[Commits] Rev 3400: Fixed LP bug #922971. in file:///home/igor/maria/maria-5.3-bug922971/

Igor Babaev igor at askmonty.org
Sat Jan 28 11:12:45 EET 2012


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

------------------------------------------------------------
revno: 3400
revision-id: igor at askmonty.org-20120128091245-o90z25rnh8lzruj7
parent: igor at askmonty.org-20120128032308-kqzgdkmycm3c4yyt
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug922971
timestamp: Sat 2012-01-28 01:12:45 -0800
message:
  Fixed LP bug #922971.
  Applied the fix for bug #12546542 from the mysql-5.6 code line:
  JOIN_CACHE::join_records forgot to reset JOIN_TAB::first_unmatched
  in some cases.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2012-01-22 04:58:23 +0000
+++ b/mysql-test/r/join_cache.result	2012-01-28 09:12:45 +0000
@@ -5486,4 +5486,52 @@
 set join_cache_level = default;
 set optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3;
+#
+# Bug #12546542: missing row with semijoin=off + join cache
+# (LP bug #922971)
+#
+CREATE TABLE t1 (a varchar(1024));
+INSERT INTO t1 VALUES ('v'), ('we');
+CREATE TABLE t2 (
+a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int
+);
+INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6);
+CREATE TABLE t3 (b int, c int);
+INSERT INTO t3 VALUES (4,4);
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off';
+set optimizer_switch='materialization=off';
+set join_cache_level=0;
+EXPLAIN
+SELECT * FROM t1
+WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
+WHERE t2.c < 10 OR t3.c > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT * FROM t1
+WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
+WHERE t2.c < 10 OR t3.c > 1);
+a
+v
+we
+set join_cache_level=2;
+EXPLAIN
+SELECT * FROM t1
+WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
+WHERE t2.c < 10 OR t3.c > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t1
+WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
+WHERE t2.c < 10 OR t3.c > 1);
+a
+v
+we
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2012-01-22 04:58:23 +0000
+++ b/mysql-test/t/join_cache.test	2012-01-28 09:12:45 +0000
@@ -3505,5 +3505,47 @@
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #12546542: missing row with semijoin=off + join cache
+--echo # (LP bug #922971)
+--echo #
+
+CREATE TABLE t1 (a varchar(1024));
+INSERT INTO t1 VALUES ('v'), ('we');
+CREATE TABLE t2 (
+  a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int
+);
+INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6);
+CREATE TABLE t3 (b int, c int);
+INSERT INTO t3 VALUES (4,4);
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off';
+set optimizer_switch='materialization=off';
+
+set join_cache_level=0;
+EXPLAIN
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
+                WHERE t2.c < 10 OR t3.c > 1);
+
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
+                WHERE t2.c < 10 OR t3.c > 1);
+
+set join_cache_level=2;
+EXPLAIN
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
+                WHERE t2.c < 10 OR t3.c > 1);
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
+                WHERE t2.c < 10 OR t3.c > 1);
+
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2011-11-25 19:54:36 +0000
+++ b/sql/sql_join_cache.cc	2012-01-28 09:12:45 +0000
@@ -2109,6 +2109,18 @@
     if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
       goto finish;
   }
+ 
+  if (skip_last)
+  {
+    DBUG_ASSERT(!is_key_access());
+    /*
+       Restore the last record from the join buffer to generate
+       all extentions for it.
+    */
+    get_record();		               
+  }
+
+finish:
   if (outer_join_first_inner)
   {
     /* 
@@ -2119,18 +2131,6 @@
     for (tab= join_tab->first_inner; tab <= join_tab->last_inner; tab++)
       tab->first_unmatched= 0;
   } 
- 
-  if (skip_last)
-  {
-    DBUG_ASSERT(!is_key_access());
-    /*
-       Restore the last record from the join buffer to generate
-       all extentions for it.
-    */
-    get_record();		               
-  }
-
-finish:
   restore_last_record();
   reset(TRUE);
   DBUG_PRINT("exit", ("rc: %d", rc));



More information about the commits mailing list