[Commits] Rev 3395: BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ... in file:///home/psergey/dev2/5.3-look50/

Sergey Petrunya psergey at askmonty.org
Wed Jan 25 20:05:24 EET 2012


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

------------------------------------------------------------
revno: 3395
revision-id: psergey at askmonty.org-20120125180520-zmwrarg0bq08q3n5
parent: psergey at askmonty.org-20120125143657-eavawi511n3mxf7d
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look50
timestamp: Wed 2012-01-25 22:05:20 +0400
message:
  BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
  - Disable use of join cache when we're using FirstMatch strategy, and the join
    order is such that subquery's inner tables are interleaved with outer.  Join 
    buffering code is incapable of handling such join orders.
  
  - The testcase requires use of @@debug_optimizer_prefer_join_prefix to hit the bug, 
    but I'm pushing it anyway (including the mention of the variable in .test file), 
    so that it can be found and enabled when/if we get something comparable in the 
    main tree.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-01-25 14:36:57 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-01-25 18:05:20 +0000
@@ -2509,4 +2509,31 @@
 8	4	2
 5	5	5
 DROP TABLE t1, t2;
+#
+# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+#
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+a	b	c
+e	0	j
+e	8	v
+w	0	j
+w	8	v
+a	0	j
+a	8	v
+h	0	j
+h	8	v
+x	0	j
+x	8	v
+k	0	j
+k	8	v
+g	0	j
+g	8	v
+DROP TABLE t1, t2;
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-01-25 14:36:57 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-01-25 18:05:20 +0000
@@ -2523,6 +2523,33 @@
 10	2	1
 10	2	1
 DROP TABLE t1, t2;
+#
+# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+#
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+a	b	c
+e	0	j
+e	8	v
+w	0	j
+w	8	v
+a	0	j
+a	8	v
+h	0	j
+h	8	v
+x	0	j
+x	8	v
+k	0	j
+k	8	v
+g	0	j
+g	8	v
+DROP TABLE t1, t2;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2012-01-25 14:36:57 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-01-25 18:05:20 +0000
@@ -2252,6 +2252,23 @@
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+--echo #
+# t1 should be MyISAM or InnoDB
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+
+#SET debug_optimizer_prefer_join_prefix= 'alias2,alias4,alias1,alias3';
+
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+  SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+
+DROP TABLE t1, t2;
 
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-01-25 14:33:57 +0000
+++ b/sql/opt_subselect.cc	2012-01-25 18:05:20 +0000
@@ -4079,7 +4079,8 @@
 {
   uint i;
   DBUG_ENTER("setup_semijoin_dups_elimination");
-
+  
+  join->complex_firstmatch_tables= table_map(0);
 
   POSITION *pos= join->best_positions + join->const_tables;
   for (i= join->const_tables ; i < join->top_join_tab_count; )
@@ -4165,8 +4166,13 @@
       {
         JOIN_TAB *j;
         JOIN_TAB *jump_to= tab-1;
+
+        bool complex_range= FALSE;
+        table_map tables_in_range= table_map(0);
+
         for (j= tab; j != tab + pos->n_sj_tables; j++)
         {
+          tables_in_range |= j->table->map;
           if (!j->emb_sj_nest)
           {
             /* 
@@ -4176,11 +4182,12 @@
               SELECT * FROM ot1, nt1 WHERE ot1.col IN (SELECT expr FROM it1, it2)
 
               with a join order of 
-                   
 
-              ot1 it1 nt1 nt2
+                   +----- FirstMatch range ----+
+                   |                           |
+              ot1 it1 nt1 nt2 it2 it3 ...
                    |   ^
-                   |   +-------- 'j' point here
+                   |   +-------- 'j' points here
                    +------------- SJ_OPT_FIRST_MATCH was set for this table as
                                   it's the first one that produces duplicates
               
@@ -4195,6 +4202,7 @@
               j[-1].do_firstmatch= jump_to;
 
             jump_to= j; /* Jump back to us */
+            complex_range= TRUE;
           }
           else
           {
@@ -4205,6 +4213,9 @@
         j[-1].do_firstmatch= jump_to;
         i+= pos->n_sj_tables;
         pos+= pos->n_sj_tables;
+
+        if (complex_range)
+          join->complex_firstmatch_tables|= tables_in_range;
         break;
       }
       case SJ_OPT_NONE:

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-25 14:33:57 +0000
+++ b/sql/sql_select.cc	2012-01-25 18:05:20 +0000
@@ -9153,6 +9153,9 @@
 
   if (tab->use_quick == 2)
     goto no_join_cache;
+
+  if (tab->table->map & join->complex_firstmatch_tables)
+    goto no_join_cache;
   
   /*
     Don't use join cache if we're inside a join tab range covered by LooseScan
@@ -9363,7 +9366,7 @@
   {
     tab->used_join_cache_level= join->max_allowed_join_cache_level;  
   }
-  
+
   uint idx= join->const_tables;
   for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES); 
        tab; 
@@ -9448,6 +9451,8 @@
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
   bool sorted= 1;
 
+  join->complex_firstmatch_tables= table_map(0);
+
   if (!join->select_lex->sj_nests.is_empty() &&
       setup_semijoin_dups_elimination(join, options, no_jbuf_after))
     DBUG_RETURN(TRUE); /* purecov: inspected */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-01-25 14:33:57 +0000
+++ b/sql/sql_select.h	2012-01-25 18:05:20 +0000
@@ -995,6 +995,13 @@
   
   /* We also maintain a stack of join optimization states in * join->positions[] */
 /******* Join optimization state members end *******/
+
+  /*
+    Tables within complex firstmatch ranges (i.e. those where inner tables are
+    interleaved with outer tables). Join buffering cannot be used for these.
+  */
+  table_map complex_firstmatch_tables;
+
   /*
     The cost of best complete join plan found so far during optimization,
     after optimization phase - cost of picked join order (not taking into



More information about the commits mailing list