[Commits] Rev 3389: BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ... in file:///home/psergey/dev2/5.3-look48/

Sergey Petrunya psergey at askmonty.org
Thu Jan 19 21:44:45 EET 2012


At file:///home/psergey/dev2/5.3-look48/

------------------------------------------------------------
revno: 3389
revision-id: psergey at askmonty.org-20120119194443-rxtmqwzmg4bpe88u
parent: wlad at montyprogram.com-20120119124659-8rkf6gab6px5zper
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look48
timestamp: Thu 2012-01-19 23:44:43 +0400
message:
  BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
  - setup_semijoin_dups_elimination() would incorrectly set join_tab->do_firstmatch 
    when the join order had outer tables interleaved with inner.
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2011-12-24 16:55:10 +0000
+++ b/mysql-test/r/subselect_sj2.result	2012-01-19 19:44:43 +0000
@@ -889,5 +889,19 @@
 c	c	NULL	NULL
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
+#
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1);
+SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2011-12-24 16:55:10 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2012-01-19 19:44:43 +0000
@@ -903,6 +903,20 @@
 c	c	NULL	NULL
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
+#
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1);
+SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #
@@ -924,9 +938,9 @@
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	FirstMatch(t3)
 1	PRIMARY	t1	ref	b	b	4	test.t3.b	1	Using index
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
 SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 b	c
@@ -952,12 +966,13 @@
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t4)
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t1)
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 pk	a	b
 1	6	8
+2	8	8
 set optimizer_switch=@tmp_optimizer_switch;
 set join_cache_level=default;
 DROP TABLE t1,t2,t3,t4;

=== modified file 'mysql-test/r/subselect_sj2_mat.result'
--- a/mysql-test/r/subselect_sj2_mat.result	2011-12-24 16:55:10 +0000
+++ b/mysql-test/r/subselect_sj2_mat.result	2012-01-19 19:44:43 +0000
@@ -891,6 +891,20 @@
 c	c	NULL	NULL
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
+#
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1);
+SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test	2011-12-15 22:26:59 +0000
+++ b/mysql-test/t/subselect_sj2.test	2012-01-19 19:44:43 +0000
@@ -1076,5 +1076,19 @@
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
+--echo #
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+# t2 needs to be InnoDB
+CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1);
+
+SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
+
+DROP TABLE t1,t2;
+
 --echo # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-01-18 13:52:38 +0000
+++ b/sql/opt_subselect.cc	2012-01-19 19:44:43 +0000
@@ -4162,16 +4162,39 @@
       }
       case SJ_OPT_FIRST_MATCH:
       {
-        JOIN_TAB *j, *jump_to= tab-1;
+        JOIN_TAB *j;
+        JOIN_TAB *jump_to= tab-1;
         for (j= tab; j != tab + pos->n_sj_tables; j++)
         {
-          /*
-            NOTE: this loop probably doesn't do the right thing for the case 
-            where FirstMatch's duplicate-generating range is interleaved with
-            "unrelated" tables (as specified in WL#3750, section 2.2).
-          */
           if (!j->emb_sj_nest)
-            jump_to= tab;
+          {
+            /* 
+              Got a table that's not within any semi-join nest. This is a case
+              like this:
+
+              SELECT * FROM ot1, nt1 WHERE ot1.col IN (SELECT expr FROM it1, it2)
+
+              with a join order of 
+                   
+
+              ot1 it1 nt1 nt2
+                   |   ^
+                   |   +-------- 'j' point here
+                   +------------- SJ_OPT_FIRST_MATCH was set for this table as
+                                  it's the first one that produces duplicates
+              
+            */
+            DBUG_ASSERT(j != tab);  /* table ntX must have an itX before it */
+
+            /* 
+              If the table right before us is an inner table (like it1 in the
+              picture), it should be set to jump back to previous outer-table
+            */
+            if (j[-1].emb_sj_nest)
+              j[-1].do_firstmatch= jump_to;
+
+            jump_to= j; /* Jump back to us */
+          }
           else
           {
             j->first_sj_inner_tab= tab;



More information about the commits mailing list