[Commits] Rev 2896: Fixed LP bug #707827. in file:///home/igor/maria/maria-5.3-bug707827/

Igor Babaev igor at askmonty.org
Fri Jan 28 07:23:03 EET 2011


At file:///home/igor/maria/maria-5.3-bug707827/

------------------------------------------------------------
revno: 2896
revision-id: igor at askmonty.org-20110128052302-09617460yxj1kck7
parent: igor at askmonty.org-20110127044523-9kvx1t3x3166hjyr
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug707827
timestamp: Thu 2011-01-27 21:23:02 -0800
message:
  Fixed LP bug #707827.
  This bug could manifest itself when hash join over a varchar column
  with NULL values in some rows was used. It happened because the
  function key_buf_cmp erroneously returned FALSE when one of the joined
  key fields was null while the second was not.
  Also fixed two other bugs in the functions key_hashnr and key_buf_cmp 
  that could possibly lead to wrong results for some queries that
  used hash join over several columns with nulls.
  Also reverted the latest addition of the test case for bug #45092. It
  had been already backported earlier.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2011-01-15 20:42:32 +0000
+++ b/mysql-test/r/join_cache.result	2011-01-28 05:23:02 +0000
@@ -6221,21 +6221,34 @@
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2;
 #
-# Backported testcase for: Bug #45092: join buffer contains two blob columns one of which is
-#   used in the key employed to access the joined table
+# Bug #707827: hash join on varchar column with NULLs 
 #
-CREATE TABLE t1 (c1 int, c2 int, key (c2));
-INSERT INTO t1 VALUES (1,1);
-INSERT INTO t1 VALUES (2,2);
-CREATE TABLE t2 (c1 text, c2 text);
-INSERT INTO t2 VALUES('tt', 'uu');
-INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
-ANALYZE TABLE t1,t2;
-set join_cache_level=6;
-SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
-WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
-c1	c2	c1	c2	LENGTH(t2.c1)	LENGTH(t2.c2)
-2	2	tt	uu	2	2
-set join_cache_level=default;
+CREATE TABLE t1 (v varchar(1));
+INSERT INTO t1 VALUES ('o'), ('u');
+CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ;
+INSERT INTO t2 VALUES
+(8,NULL), (10,'b'), (5,'k'), (4,NULL),
+(1,NULL), (11,'u'), (7,NULL), (2,'d');
+SET SESSION join_buffer_size = 255;
+SET SESSION join_cache_level = 4;
+EXPLAIN
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.v	2	Using join buffer (flat, BNLH join)
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+a
+11
+SET SESSION join_cache_level = 1;
+EXPLAIN
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.v	2	
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+a
+11
+SET SESSION join_cache_level = DEFAULT;
+SET SESSION join_buffer_size = DEFAULT;
 DROP TABLE t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2011-01-14 10:07:50 +0000
+++ b/mysql-test/t/join_cache.test	2011-01-28 05:23:02 +0000
@@ -2847,28 +2847,31 @@
 DROP TABLE t1,t2;
 
 --echo #
---echo # Backported testcase for: Bug #45092: join buffer contains two blob columns one of which is
---echo #   used in the key employed to access the joined table
+--echo # Bug #707827: hash join on varchar column with NULLs 
 --echo #
 
-CREATE TABLE t1 (c1 int, c2 int, key (c2));
-INSERT INTO t1 VALUES (1,1);
-INSERT INTO t1 VALUES (2,2);
-
-CREATE TABLE t2 (c1 text, c2 text);
-INSERT INTO t2 VALUES('tt', 'uu');
-INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
-
---disable_result_log
-ANALYZE TABLE t1,t2;
---enable_result_log
-
-set join_cache_level=6;
-
-SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
-  WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
-
-set join_cache_level=default;
+CREATE TABLE t1 (v varchar(1));
+INSERT INTO t1 VALUES ('o'), ('u');
+
+CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ;
+INSERT INTO t2 VALUES
+  (8,NULL), (10,'b'), (5,'k'), (4,NULL),
+  (1,NULL), (11,'u'), (7,NULL), (2,'d');
+
+SET SESSION join_buffer_size = 255;
+
+SET SESSION join_cache_level = 4;
+EXPLAIN
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+
+SET SESSION join_cache_level = 1;
+EXPLAIN
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
+
+SET SESSION join_cache_level = DEFAULT;
+SET SESSION join_buffer_size = DEFAULT;
 
 DROP TABLE t1,t2;
 

=== modified file 'sql/key.cc'
--- a/sql/key.cc	2011-01-05 23:03:30 +0000
+++ b/sql/key.cc	2011-01-28 05:23:02 +0000
@@ -663,8 +663,6 @@
         switch (key_part->type) {
         case HA_KEYTYPE_VARTEXT1:
         case HA_KEYTYPE_VARBINARY1:
-          key++;
-          break;
         case HA_KEYTYPE_VARTEXT2:
         case HA_KEYTYPE_VARBINARY2:
           key+= 2;
@@ -769,8 +767,6 @@
         switch (key_part->type) {
         case HA_KEYTYPE_VARTEXT1:
         case HA_KEYTYPE_VARBINARY1:
-          key1++; key2++;
-          break;
         case HA_KEYTYPE_VARTEXT2:
         case HA_KEYTYPE_VARBINARY2:
           key1+= 2; key2+= 2;
@@ -778,10 +774,10 @@
         default:
           ;
         }
-    continue;
+        continue;
       }
       if (*pos1 != *pos2)
-        return FALSE;
+        return TRUE;
       pos1++; pos2++;
     }
 



More information about the commits mailing list