bk commit into 6.0 tree (sergefp:1.2627) BUG#33743

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
6.0 repository of sergefp.  When sergefp 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, 2008-04-26 06:09:22+04:00, sergefp@stripped +5 -0
  BUG#33743 "nested subqueries, unique index, wrong result"
  Fix a lot of problems found by the NIST test:
  - sj_table_is_included(): Make the check if an sj-outer table is functionally 
    dependent on other sj-outer tables actually work: don't rely on 
    join_tab->ref.depend_map, it can be wrong because of equality propagation,
    do check tab->ref.items array instead.
  - setup_semijoin_dups_elimination(): 
    = Don't use InsideOut strategy if inner tables are interleaved with 
      outer (it is unusable)
    = When setting up FirstMatch strategy for a case with multiple inner 
      tables, set tab->do_firstmatch only for the last inner table.
  - get_bound_sj_equalities(): fix typo
  - best_access_path(): Don't consider InsideOut strategy when all subquery's
    outer references are bound.
    already bound.

  mysql-test/r/subselect.result@stripped, 2008-04-26 06:09:13+04:00, sergefp@stripped +1 -1
    BUG#33743 "nested subqueries, unique index, wrong result"
    - Update test results

  mysql-test/r/subselect_no_mat.result@stripped, 2008-04-26 06:09:13+04:00, sergefp@stripped
+1 -1
    BUG#33743 "nested subqueries, unique index, wrong result"
    - Update test results

  mysql-test/r/subselect_sj2.result@stripped, 2008-04-26 06:09:13+04:00, sergefp@stripped +89
-0
    BUG#33743 "nested subqueries, unique index, wrong result"
    - Testcase

  mysql-test/t/subselect_sj2.test@stripped, 2008-04-26 06:09:13+04:00, sergefp@stripped +88
-0
    BUG#33743 "nested subqueries, unique index, wrong result"
    - Testcase

  sql/sql_select.cc@stripped, 2008-04-26 06:09:13+04:00, sergefp@stripped +29 -13
    BUG#33743 "nested subqueries, unique index, wrong result"
    Fix a lot of problems found by the NIST test:
    - sj_table_is_included(): Make the check if an sj-outer table is functionally 
      dependent on other sj-outer tables actually work: don't rely on 
      join_tab->ref.depend_map, it can be wrong because of equality propagation,
      do check tab->ref.items array instead.
    - setup_semijoin_dups_elimination(): 
      = Don't use InsideOut strategy if inner tables are interleaved with 
        outer (it is unusable)
      = When setting up FirstMatch strategy for a case with multiple inner 
        tables, set tab->do_firstmatch only for the last inner table.
    - get_bound_sj_equalities(): fix typo
    - best_access_path(): Don't consider InsideOut strategy when all subquery's
      outer references are bound.
      already bound.

diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result	2008-04-23 17:13:08 +04:00
+++ b/mysql-test/r/subselect.result	2008-04-26 06:09:13 +04:00
@@ -1353,7 +1353,7 @@ a
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index; Start temporary
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index
 1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using where; Using index; End temporary; Using
join buffer
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join
`test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` =
`test`.`t1`.`b`))
diff -Nrup a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
--- a/mysql-test/r/subselect_no_mat.result	2008-04-23 17:13:08 +04:00
+++ b/mysql-test/r/subselect_no_mat.result	2008-04-26 06:09:13 +04:00
@@ -1357,7 +1357,7 @@ a
 explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index; Start temporary
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index
 1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using where; Using index; End temporary; Using
join buffer
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join
`test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` =
`test`.`t1`.`b`))
diff -Nrup a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
--- a/mysql-test/r/subselect_sj2.result	2008-04-24 02:01:04 +04:00
+++ b/mysql-test/r/subselect_sj2.result	2008-04-26 06:09:13 +04:00
@@ -455,3 +455,92 @@ group by  t1.b);
 1
 1
 drop table t1;
+CREATE TABLE t1
+(EMPNUM   CHAR(3) NOT NULL,
+EMPNAME  CHAR(20),
+GRADE    DECIMAL(4),
+CITY     CHAR(15));
+CREATE TABLE t2
+(PNUM     CHAR(3) NOT NULL,
+PNAME    CHAR(20),
+PTYPE    CHAR(6),
+BUDGET   DECIMAL(9),
+CITY     CHAR(15));
+CREATE TABLE t3
+(EMPNUM   CHAR(3) NOT NULL,
+PNUM     CHAR(3) NOT NULL,
+HOURS    DECIMAL(5));
+INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
+INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
+INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
+INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
+INSERT INTO t3 VALUES  ('E1','P1',40);
+INSERT INTO t3 VALUES  ('E1','P2',20);
+INSERT INTO t3 VALUES  ('E1','P3',80);
+INSERT INTO t3 VALUES  ('E1','P4',20);
+INSERT INTO t3 VALUES  ('E1','P5',12);
+INSERT INTO t3 VALUES  ('E1','P6',12);
+INSERT INTO t3 VALUES  ('E2','P1',40);
+INSERT INTO t3 VALUES  ('E2','P2',80);
+INSERT INTO t3 VALUES  ('E3','P2',20);
+INSERT INTO t3 VALUES  ('E4','P2',20);
+INSERT INTO t3 VALUES  ('E4','P4',40);
+INSERT INTO t3 VALUES  ('E4','P5',80);
+SELECT * FROM t1;
+EMPNUM	EMPNAME	GRADE	CITY
+E1	Alice	12	Deale
+E2	Betty	10	Vienna
+E3	Carmen	13	Vienna
+E4	Don	12	Deale
+E5	Ed	13	Akron
+CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
+SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+(SELECT EMPNUM
+FROM t3
+WHERE PNUM IN
+(SELECT PNUM
+FROM t2
+WHERE PTYPE = 'Design'));
+EMPNAME
+Alice
+Betty
+Don
+DROP INDEX t1_IDX ON t1;
+CREATE INDEX t1_IDX ON t1(EMPNUM);
+SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+(SELECT EMPNUM
+FROM t3
+WHERE PNUM IN
+(SELECT PNUM
+FROM t2
+WHERE PTYPE = 'Design'));
+EMPNAME
+Alice
+Betty
+Don
+DROP INDEX t1_IDX ON t1;
+SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+(SELECT EMPNUM
+FROM t3
+WHERE PNUM IN
+(SELECT PNUM
+FROM t2
+WHERE PTYPE = 'Design'));
+EMPNAME
+Alice
+Betty
+Don
+DROP TABLE t1, t2, t3;
diff -Nrup a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
--- a/mysql-test/t/subselect_sj2.test	2008-04-24 02:01:04 +04:00
+++ b/mysql-test/t/subselect_sj2.test	2008-04-26 06:09:13 +04:00
@@ -612,3 +612,91 @@ where t1.a not in (select 1 from t1 
                   group by  t1.b);
 drop table t1;
 
+#
+# BUG#33743 "nested subqueries, unique index, wrong result"
+#
+CREATE TABLE t1
+ (EMPNUM   CHAR(3) NOT NULL,
+  EMPNAME  CHAR(20),
+  GRADE    DECIMAL(4),
+  CITY     CHAR(15));
+
+CREATE TABLE t2
+ (PNUM     CHAR(3) NOT NULL,
+  PNAME    CHAR(20),
+  PTYPE    CHAR(6),
+  BUDGET   DECIMAL(9),
+  CITY     CHAR(15));
+
+CREATE TABLE t3
+ (EMPNUM   CHAR(3) NOT NULL,
+  PNUM     CHAR(3) NOT NULL,
+  HOURS    DECIMAL(5));
+
+INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
+INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
+INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
+
+INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
+
+INSERT INTO t3 VALUES  ('E1','P1',40);
+INSERT INTO t3 VALUES  ('E1','P2',20);
+INSERT INTO t3 VALUES  ('E1','P3',80);
+INSERT INTO t3 VALUES  ('E1','P4',20);
+INSERT INTO t3 VALUES  ('E1','P5',12);
+INSERT INTO t3 VALUES  ('E1','P6',12);
+INSERT INTO t3 VALUES  ('E2','P1',40);
+INSERT INTO t3 VALUES  ('E2','P2',80);
+INSERT INTO t3 VALUES  ('E3','P2',20);
+INSERT INTO t3 VALUES  ('E4','P2',20);
+INSERT INTO t3 VALUES  ('E4','P4',40);
+INSERT INTO t3 VALUES  ('E4','P5',80);
+
+
+SELECT * FROM t1;
+CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
+--sorted_result
+SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+   (SELECT EMPNUM
+    FROM t3
+    WHERE PNUM IN
+       (SELECT PNUM
+        FROM t2
+        WHERE PTYPE = 'Design'));
+
+DROP INDEX t1_IDX ON t1;
+CREATE INDEX t1_IDX ON t1(EMPNUM);
+--sorted_result
+SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+   (SELECT EMPNUM
+    FROM t3
+    WHERE PNUM IN
+       (SELECT PNUM
+        FROM t2
+        WHERE PTYPE = 'Design'));
+
+DROP INDEX t1_IDX ON t1;
+--sorted_result
+SELECT EMPNAME
+FROM t1
+WHERE EMPNUM IN
+   (SELECT EMPNUM
+    FROM t3
+    WHERE PNUM IN
+       (SELECT PNUM
+        FROM t2
+        WHERE PTYPE = 'Design'));
+
+DROP TABLE t1, t2, t3;
+
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-04-25 03:59:32 +04:00
+++ b/sql/sql_select.cc	2008-04-26 06:09:13 +04:00
@@ -916,12 +916,17 @@ static bool sj_table_is_included(JOIN *j
   TABLE_LIST *embedding= join_tab->table->pos_in_table_list->embedding;
   if (join_tab->type == JT_EQ_REF)
   {
-    Table_map_iterator it(join_tab->ref.depend_map & ~PSEUDO_TABLE_BITS);
+    table_map depends_on= 0;
     uint idx;
+    
+    for (uint kp= 0; kp < join_tab->ref.key_parts; kp++)
+      depends_on |= join_tab->ref.items[kp]->used_tables();
+
+    Table_map_iterator it(depends_on & ~PSEUDO_TABLE_BITS);
     while ((idx= it.next_bit())!=Table_map_iterator::BITMAP_END)
     {
-      JOIN_TAB *ref_tab= join->join_tab + idx;
-      if (embedding == ref_tab->table->pos_in_table_list->embedding)
+      JOIN_TAB *ref_tab= join->map2table[idx];
+      if (embedding != ref_tab->table->pos_in_table_list->embedding)
         return TRUE;
     }
     /* Ok, functionally dependent */
@@ -1182,6 +1187,12 @@ int setup_semijoin_dups_elimination(JOIN
         dealing_with_jbuf= FALSE;
         dups_ranges[++cur_range].strategy= 0;
       }
+      else
+      {
+        /* We don't support interleaving for InsideOut*/
+        if (!tab->emb_sj_nest)
+          emb_insideout_nest= NULL;
+      }
     }
   }
 
@@ -1268,11 +1279,11 @@ int setup_semijoin_dups_elimination(JOIN
     /* Create the FirstMatch tail */
     for (; tab < join->join_tab + dups_ranges[j].end_idx; tab++)
     {
-      if (tab->emb_sj_nest)
-        tab->do_firstmatch= jump_to; 
-      else
+      if (!tab->emb_sj_nest)
         jump_to= tab;
     }
+    if (tab - 1 != jump_to)
+      tab[-1].do_firstmatch= jump_to;
   }
   DBUG_RETURN(FALSE);
 }
@@ -5308,7 +5319,7 @@ ulonglong get_bound_sj_equalities(TABLE_
     */
     if (!(item->used_tables() & remaining_tables))
     {
-      res |= 1ULL < i;
+      res |= 1ULL << i;
     }
   }
   return res;
@@ -5376,14 +5387,19 @@ best_access_path(JOIN      *join,
         3. We're not within a semi-join range (i.e. all semi-joins either have
            all or none of their tables in join_table_map), except
            s->emb_sj_nest (which we've just entered).
-        3. All correlation references from this sj-nest are bound
+        4. All non-IN-equality correlation references from this sj-nest are 
+           bound
+        5. But some of the IN-equalities aren't (so this can't be handled by 
+           FirstMatch strategy)
     */
-    if (s->emb_sj_nest &&                                                 //
(1)
+    if (s->emb_sj_nest &&                                               // (1)
         s->emb_sj_nest->sj_in_exprs < 64 && 
-        ((remaining_tables & s->emb_sj_nest->sj_inner_tables) ==           //
(2)
-         s->emb_sj_nest->sj_inner_tables) &&                              
// (2)
-        join->cur_emb_sj_nests == s->emb_sj_nest->sj_inner_tables &&    
  // (3)
-        !(remaining_tables & s->emb_sj_nest->nested_join->sj_corr_tables))
// (4)
+        ((remaining_tables & s->emb_sj_nest->sj_inner_tables) ==        // (2)
+         s->emb_sj_nest->sj_inner_tables) &&                            //
(2)
+        join->cur_emb_sj_nests == s->emb_sj_nest->sj_inner_tables &&   
// (3)
+        !(remaining_tables & 
+          s->emb_sj_nest->nested_join->sj_corr_tables) &&              
// (4)
+        remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on)  //
(5)
     {
       /* This table is an InsideOut scan candidate */
       bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest,