bk commit into 5.0 tree (kaa:1.2478) BUG#27643

[Alexey Kopytov]

Below is the list of changes that have just been committed into a local
5.0 repository of kaa. When kaa does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-05-30 19:27:00+04:00, kaa@stripped +6 -0
  Fix for bug #27643 "query failed : 1114 (The table '' is full)
  
  Problem:
  
  HASH indexes on VARCHAR columns with binary collations did not ignore trailing spaces
from strings before comparisons. This could result in duplicate records being
successfully inserted into a MEMORY table with unique key constraints.
  
  As a direct consequence of the above, internal MEMORY tables used for GROUP BY
calculation in testcases for bug #27643 contained duplicate rows which resulted in
duplicate key errors when converting those temporary tables to MyISAM. Additionally, that
error was incorrectly converted to the 'table is full' error.
  
  Solution:
  
  - ignore trailing spaces in VARCHAR fields with binary collations when calculating
hashes.
  - return a proper error from create_myisam_from_heap() when conversion fails.

  mysql-test/r/heap_hash.result@stripped, 2007-05-30 19:26:57+04:00, kaa@stripped +23 -0
    Added a testcase for bug #27643.

  mysql-test/t/heap_hash.test@stripped, 2007-05-30 19:26:57+04:00, kaa@stripped +32 -1
    Added a testcase for bug #27643.

  sql/sql_select.cc@stripped, 2007-05-30 19:26:57+04:00, kaa@stripped +1 -1
    Return an appropriate error instead of 'table is full' when conversion from MEMORY to
MyISAM fails.

  strings/ctype-bin.c@stripped, 2007-05-30 19:26:57+04:00, kaa@stripped +24 -1
    Added my_hash_sort_8bit_bin() which ignores trailing spaces when calculating hashes,
and is now used for VARCHAR columns instead of my_hash_sort_bin().

  strings/ctype-mb.c@stripped, 2007-05-30 19:26:57+04:00, kaa@stripped +7 -0
    Ignore trailing spaces when calculating a string hash in my_hash_sort_mb_bin().

  strings/ctype-ucs2.c@stripped, 2007-05-30 19:26:57+04:00, kaa@stripped +4 -1
    Ignore trailing spaces when calculating a string hash in my_hash_sort_ucs2_bin().

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kaa
# Host:	polly.local
# Root:	/home/kaa/src/maint/bug27643/my50-bug27643

--- 1.515/sql/sql_select.cc	2007-04-30 07:14:31 +04:00
+++ 1.516/sql/sql_select.cc	2007-05-30 19:26:57 +04:00
@@ -10112,7 +10112,7 @@ bool create_myisam_from_heap(THD *thd, T
 
  err:
   DBUG_PRINT("error",("Got error: %d",write_err));
-  table->file->print_error(error,MYF(0));	// Give table is full error
+  table->file->print_error(write_err, MYF(0));
   (void) table->file->ha_rnd_end();
   (void) new_table.file->close();
  err1:

--- 1.74/strings/ctype-bin.c	2007-01-22 15:10:42 +03:00
+++ 1.75/strings/ctype-bin.c	2007-05-30 19:26:57 +04:00
@@ -271,6 +271,29 @@ static int my_wc_mb_bin(CHARSET_INFO *cs
 }
 
 
+void my_hash_sort_8bit_bin(CHARSET_INFO *cs __attribute__((unused)),
+		      const uchar *key, uint len,ulong *nr1, ulong *nr2)
+{
+  const uchar *pos = key;
+  
+  key+= len;
+  
+  /*
+     Remove trailing spaces. We have to do this to be able to compare
+    'A ' and 'A' as identical
+  */
+  while (key > pos && key[-1] == ' ')
+    key--;
+
+  for (; pos < (uchar*) key ; pos++)
+  {
+    nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * 
+	     ((uint)*pos)) + (nr1[0] << 8);
+    nr2[0]+=3;
+  }
+}
+
+
 void my_hash_sort_bin(CHARSET_INFO *cs __attribute__((unused)),
 		      const uchar *key, uint len,ulong *nr1, ulong *nr2)
 {
@@ -471,7 +494,7 @@ MY_COLLATION_HANDLER my_collation_8bit_b
     my_wildcmp_bin,
     my_strcasecmp_bin,
     my_instr_bin,
-    my_hash_sort_bin,
+    my_hash_sort_8bit_bin,
     my_propagate_simple
 };
 

--- 1.65/strings/ctype-ucs2.c	2007-02-22 17:59:55 +03:00
+++ 1.66/strings/ctype-ucs2.c	2007-05-30 19:26:57 +04:00
@@ -1484,7 +1484,10 @@ void my_hash_sort_ucs2_bin(CHARSET_INFO 
   const uchar *pos = key;
   
   key+= len;
-  
+
+  while (key > pos+1 && key[-1] == ' ' && key[-2] == '\0')
+    key-= 2;
+
   for (; pos < (uchar*) key ; pos++)
   {
     nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * 

--- 1.20/mysql-test/r/heap_hash.result	2006-09-07 22:06:33 +04:00
+++ 1.21/mysql-test/r/heap_hash.result	2007-05-30 19:26:57 +04:00
@@ -366,3 +366,26 @@ explain select a from t1 where a in (1,3
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	4	Using where
 drop table t1;
+End of 4.1 tests
+CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
+col2 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
+UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES('A', 'A');
+INSERT INTO t1 VALUES('A ', 'A ');
+ERROR 23000: Duplicate entry 'A -A ' for key 1
+DROP TABLE t1;
+CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 
+col2 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 
+UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES('A', 'A');
+INSERT INTO t1 VALUES('A ', 'A ');
+ERROR 23000: Duplicate entry 'A -A ' for key 1
+DROP TABLE t1;
+CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, 
+col2 VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, 
+UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES('A', 'A'), ('B', 'B'), ('C', 'C');
+INSERT INTO t1 VALUES('A ', 'A ');
+ERROR 23000: Duplicate entry '' for key 1
+DROP TABLE t1;
+End of 5.0 tests

--- 1.11/mysql-test/t/heap_hash.test	2005-07-28 17:12:34 +04:00
+++ 1.12/mysql-test/t/heap_hash.test	2007-05-30 19:26:57 +04:00
@@ -260,4 +260,35 @@ select a from t1 where a in (1,3);
 explain select a from t1 where a in (1,3);
 drop table t1;
 
-# End of 4.1 tests
+--echo End of 4.1 tests
+
+#
+# Bug #27643: query failed : 1114 (The table '' is full)
+#
+# Check that HASH indexes disregard trailing spaces when comparing 
+# strings with binary collations
+
+CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
+                col2 VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
+                UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES('A', 'A');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES('A ', 'A ');
+DROP TABLE t1;
+CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 
+                col2 VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, 
+                UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES('A', 'A');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES('A ', 'A ');
+DROP TABLE t1;
+
+CREATE TABLE t1(col1 VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, 
+                col2 VARCHAR(32) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL, 
+                UNIQUE KEY key1 USING HASH (col1, col2)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES('A', 'A'), ('B', 'B'), ('C', 'C');
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES('A ', 'A ');
+DROP TABLE t1;
+
+--echo End of 5.0 tests

--- 1.55/strings/ctype-mb.c	2007-01-22 15:10:42 +03:00
+++ 1.56/strings/ctype-mb.c	2007-05-30 19:26:57 +04:00
@@ -467,6 +467,13 @@ static void my_hash_sort_mb_bin(CHARSET_
   
   key+= len;
   
+  /*
+     Remove trailing spaces. We have to do this to be able to compare
+    'A ' and 'A' as identical
+  */
+  while (key > pos && key[-1] == ' ')
+    key--;
+  
   for (; pos < (uchar*) key ; pos++)
   {
     nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) *