bk commit into 6.0 tree (sergefp:1.2628) BUG#35550

[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-05-01 07:53:36+04:00, sergefp@stripped +7 -0
  BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
  The bug was a bunch of interrelated issues caused by failure to do the below:
  - Let Item_in_subselect store the clause (WHERE/ON) where it is located, so 
    we can walk that clause and remove the subselect predicate from there if we
    convert it to a semi-join.
  - Let pull_out_semijoin_tables() use statement's MEM_ROOT when operating on 
    TABLE_LIST structures, as the changes it makes should remain there for 
    subsequent statement re-executions.
  - Let make_join_statistics() do constant table processing for tables that are 
    within a semi-join nest (but not within an outer join nest).

  mysql-test/r/subselect_sj.result@stripped, 2008-05-01 07:53:29+04:00, sergefp@stripped +13
-8
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    - Added a testcase where the subquery table is a constant table that has a 
      record
    - EXPLAIN EXTENDED changes: update test results (caused by preceding subquery 
      bugfixes, this .result file lagged behind because the test was disabled).

  mysql-test/r/subselect_sj2.result@stripped, 2008-05-01 07:53:29+04:00, sergefp@stripped +14
-0
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    - Testcase

  mysql-test/t/subselect_sj.test@stripped, 2008-05-01 07:53:29+04:00, sergefp@stripped +3 -0
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    - Added a testcase where the subquery table is a constant table that has a 
      record

  mysql-test/t/subselect_sj2.test@stripped, 2008-05-01 07:53:29+04:00, sergefp@stripped +19
-0
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    - Testcase

  sql/item_subselect.h@stripped, 2008-05-01 07:53:30+04:00, sergefp@stripped +10 -2
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    - Let Item_in_subselect store the clause (WHERE/ON) where it is located.

  sql/sql_class.h@stripped, 2008-05-01 07:53:30+04:00, sergefp@stripped +1 -6
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    - Updated comments.

  sql/sql_select.cc@stripped, 2008-05-01 07:53:30+04:00, sergefp@stripped +34 -12
    BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
    The bug was a bunch of interrelated issues caused by failure to do the below:
    - Let Item_in_subselect store the clause (WHERE/ON) where it is located, so 
      we can walk that clause and remove the subselect predicate from there if we
      convert it to a semi-join.
    - Let pull_out_semijoin_tables() use statement's MEM_ROOT when operating on 
      TABLE_LIST structures, as the changes it makes should remain there for 
      subsequent statement re-executions.
    - Let make_join_statistics() do constant table processing for tables that are 
      within a semi-join nest (but not within an outer join nest).

diff -Nrup a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
--- a/mysql-test/r/subselect_sj.result	2007-10-12 17:46:17 +04:00
+++ b/mysql-test/r/subselect_sj.result	2008-05-01 07:53:29 +04:00
@@ -22,10 +22,15 @@ a	b
 A confluent case of dependency
 explain select * from t1 where a in (select a from t10 where pk=12);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t10	const	PRIMARY	PRIMARY	4	const	1	Using where
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const
tables
 select * from t1 where a in (select a from t10 where pk=12);
 a	b
+explain select * from t1 where a in (select a from t10 where pk=9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t10	const	PRIMARY	PRIMARY	4	const	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+select * from t1 where a in (select a from t10 where pk=9);
+a	b
 An empty table inside
 explain select * from t1 where a in (select a from t11);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -60,7 +65,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	
 1	PRIMARY	t12	eq_ref	PRIMARY	PRIMARY	4	test.t10.a	1	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10` join
`test`.`t12`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and
(`test`.`t12`.`pk` = `test`.`t10`.`a`))
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join
`test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and
(`test`.`t12`.`pk` = `test`.`t10`.`a`))
 subqueries within outer joins go into ON expr.
 explAin extended
 select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.A in (select pk from t10));
@@ -70,7 +75,7 @@ id	select_type	tABle	type	possiBle_keys	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.B.A	1	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS
`A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1`
left join ((`test`.`t10`) join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A`
= `test`.`t1`.`A`) And 1 And (`test`.`B`.`A` = `test`.`t10`.`pk`))) where 1
+Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS
`A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1`
left join (`test`.`t10` join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` =
`test`.`t1`.`A`) And 1 And (`test`.`B`.`A` = `test`.`t10`.`pk`))) where 1
 t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
 explAin extended
 select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
@@ -79,7 +84,7 @@ id	select_type	tABle	type	possiBle_keys	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t2.A	1	100.00	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS
`A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2`)
on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`t2`.`A` = `test`.`t10`.`pk`)))
where 1
+Note	1003	select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS
`A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join (`test`.`t10` join `test`.`t2`)
on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`t2`.`A` = `test`.`t10`.`pk`)))
where 1
 we shouldn't flatten if we're going to get a join of > MAX_TABLES.
 explain select * from 
 t1 s00, t1 s01,  t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
@@ -188,9 +193,9 @@ a	b	a	b
 insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
 explain extended select * from t1 where a in (select pk from t10 where pk<3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	103	100.00	Using where
-1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Using index
+1	PRIMARY	t10	range	PRIMARY	PRIMARY	4	NULL	4	100.00	Using where; Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	103	100.00	Using where; Using join buffer
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10`) join
`test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join
`test`.`t1` where ((`test`.`t1`.`a` = `test`.`t10`.`pk`) and (`test`.`t10`.`pk` < 3))
 drop table t0, t1;
 drop table t10, t11, t12;
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-29 03:00:23 +04:00
+++ b/mysql-test/r/subselect_sj2.result	2008-05-01 07:53:29 +04:00
@@ -551,3 +551,17 @@ SELECT * FROM v1;
 a
 drop view v1;
 drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,2);
+create table t2 as select * from t1;
+create table t3 (pk int, a int, primary key(pk));
+insert into t3 select a,a from t0;
+explain 
+select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using index
+drop table t0, t1, t2, t3;
diff -Nrup a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
--- a/mysql-test/t/subselect_sj.test	2007-06-30 23:11:58 +04:00
+++ b/mysql-test/t/subselect_sj.test	2008-05-01 07:53:29 +04:00
@@ -31,6 +31,9 @@ select * from t1 where a in (select pk f
 explain select * from t1 where a in (select a from t10 where pk=12);
 select * from t1 where a in (select a from t10 where pk=12);
 
+explain select * from t1 where a in (select a from t10 where pk=9);
+select * from t1 where a in (select a from t10 where pk=9);
+
 --echo An empty table inside
 explain select * from t1 where a in (select a from t11);
 select * from t1 where a in (select a from t11);
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-29 03:00:23 +04:00
+++ b/mysql-test/t/subselect_sj2.test	2008-05-01 07:53:29 +04:00
@@ -712,3 +712,22 @@ SELECT * FROM v1;
 drop view v1;
 drop table t1;
 
+
+#
+# BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,2);
+create table t2 as select * from t1;
+
+create table t3 (pk int, a int, primary key(pk));
+insert into t3 select a,a from t0;
+
+explain 
+select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
+
+drop table t0, t1, t2, t3;
+
diff -Nrup a/sql/item_subselect.h b/sql/item_subselect.h
--- a/sql/item_subselect.h	2008-03-12 11:25:22 +03:00
+++ b/sql/item_subselect.h	2008-05-01 07:53:30 +04:00
@@ -281,14 +281,22 @@ protected:
   Item_in_optimizer *optimizer;
   bool was_null;
   bool abort_on_null;
-
 public:
   /* Used to trigger on/off conditions that were pushed down to subselect */
   bool *pushed_cond_guards;
   
   /* Priority of this predicate in the convert-to-semi-join-nest process. */
   int sj_convert_priority;
-
+  /*
+    Used by subquery optimizations to keep track about in which clause this
+    subquery predicate is located: 
+      (TABLE_LIST*) 1   - the predicate is an AND-part of the WHERE
+      join nest pointer - the predicate is an AND-part of ON expression
+                          of a join nest   
+      NULL              - for all other locations
+    See also THD::emb_on_expr_nest.
+  */
+  TABLE_LIST *emb_on_expr_nest;
   /* 
     Location of the subquery predicate. It is either
      - pointer to join nest if the subquery predicate is in the ON expression
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h	2008-04-29 03:00:24 +04:00
+++ b/sql/sql_class.h	2008-05-01 07:53:30 +04:00
@@ -1339,12 +1339,7 @@ public:
   union 
   { 
     /*
-      Used by subquery optimizations to inform subquery->fix_fields() calls 
-      where the subquery predicates are located.
-        (TABLE_LIST*) 1   - the predicate is an AND-part of the WHERE
-        join nest pointer - the predicate is an AND-part of ON expression
-                            of a join nest   
-        NULL              - for all other locations
+      Used by subquery optimizations, see Item_in_subselect::emb_on_expr_nest.
     */
     TABLE_LIST *emb_on_expr_nest;
   } thd_marker;
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-04-29 03:00:24 +04:00
+++ b/sql/sql_select.cc	2008-05-01 07:53:30 +04:00
@@ -233,8 +233,9 @@ void select_describe(JOIN *join, bool ne
 static Item *remove_additional_cond(Item* conds);
 static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
 static bool test_if_ref(Item_field *left_item,Item *right_item);
-static bool replace_where_subcondition(JOIN *join, Item *old_cond, 
-                                       Item *new_cond, bool fix_fields);
+static bool replace_where_subcondition(JOIN *join, TABLE_LIST *emb_nest, 
+                                       Item *old_cond, Item *new_cond,
+                                       bool do_fix_fields);
 
 /*
   This is used to mark equalities that were made from i-th IN-equality.
@@ -588,6 +589,7 @@ JOIN::prepare(Item ***rref_pointer_array
                                                         &in_subs->left_expr);
           thd->lex->current_select= current;
           thd->where= save_where;
+          in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
           if (failure)
             DBUG_RETURN(-1);
           /*
@@ -3398,7 +3400,8 @@ bool JOIN::flatten_subqueries()
        tables + ((*in_subq)->sj_convert_priority % MAX_TABLES) < MAX_TABLES;
        in_subq++)
   {
-    if (replace_where_subcondition(this, *in_subq, new Item_int(1), FALSE))
+    if (replace_where_subcondition(this, (*in_subq)->emb_on_expr_nest,
+                                   *in_subq, new Item_int(1), FALSE))
       DBUG_RETURN(TRUE);
   }
  
@@ -3429,7 +3432,8 @@ bool JOIN::flatten_subqueries()
 
     Item *substitute= (*in_subq)->substitution;
     bool do_fix_fields= !(*in_subq)->substitution->fixed;
-    if (replace_where_subcondition(this, *in_subq, substitute, do_fix_fields))
+    if (replace_where_subcondition(this, (*in_subq)->emb_on_expr_nest, 
+                                   *in_subq, substitute, do_fix_fields))
       DBUG_RETURN(TRUE);
 
     //if ((*in_subq)->fix_fields(thd, (*in_subq)->ref_ptr))
@@ -3654,6 +3658,8 @@ int pull_out_semijoin_tables(JOIN *join)
       List<TABLE_LIST> *upper_join_list= (sj_nest->embedding != NULL)?
                                           
(&sj_nest->embedding->nested_join->join_list): 
                                            (&join->select_lex->top_join_list);
+      Query_arena *arena, backup;
+      arena= join->thd->activate_stmt_arena_if_needed(&backup);
       while ((tbl= child_li++))
       {
         if (tbl->table)
@@ -3690,6 +3696,9 @@ int pull_out_semijoin_tables(JOIN *join)
         while (sj_nest != li++);
         li.remove();
       }
+
+      if (arena)
+        join->thd->restore_active_arena(arena, &backup);
     }
   }
   DBUG_RETURN(0);
@@ -4022,9 +4031,16 @@ make_join_statistics(JOIN *join, TABLE_L
 	    keyuse++;
 	  } while (keyuse->table == table && keyuse->key == key);
 
+          TABLE_LIST *embedding= table->pos_in_table_list->embedding;
+          /*
+            TODO (low priority): currently we ignore the const tables that
+            are within a semi-join nest which is within an outer join nest.
+            The effect of this is that we don't do const substitution for
+            such tables.
+          */
 	  if (eq_part.is_prefix(table->key_info[key].key_parts) &&
               !table->fulltext_searched && 
-              !table->pos_in_table_list->embedding)
+              (!embedding || (embedding->sj_on_expr &&
!embedding->embedding)))
 	  {
             if ((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY))
                  == HA_NOSAME)
@@ -14986,20 +15002,25 @@ static bool test_if_ref(Item_field *left
    @return <code>true</code> if there was an error,
<code>false</code> if
    successful.
 */
-static bool replace_where_subcondition(JOIN *join, Item *old_cond, 
-                                       Item *new_cond, bool do_fix_fields)
+static bool replace_where_subcondition(JOIN *join, TABLE_LIST *emb_nest, 
+                                       Item *old_cond, Item *new_cond,
+                                       bool do_fix_fields)
 {
-  if (join->conds == old_cond) {
-    join->conds= new_cond;
+  Item **expr= (emb_nest == (TABLE_LIST*)1)? &join->conds :
&emb_nest->on_expr;
+  if (*expr == old_cond)
+  {
+    *expr= new_cond;
     if (do_fix_fields)
-      new_cond->fix_fields(join->thd, &join->conds);
+      new_cond->fix_fields(join->thd, expr);
     return FALSE;
   }
   
-  if (join->conds->type() == Item::COND_ITEM) {
-    List_iterator<Item> li(*((Item_cond*)join->conds)->argument_list());
+  if ((*expr)->type() == Item::COND_ITEM) 
+  {
+    List_iterator<Item> li(*((Item_cond*)(*expr))->argument_list());
     Item *item;
     while ((item= li++))
+    {
       if (item == old_cond) 
       {
         li.replace(new_cond);
@@ -15007,6 +15028,7 @@ static bool replace_where_subcondition(J
           new_cond->fix_fields(join->thd, li.ref());
         return FALSE;
       }
+    }
   }
 
   return TRUE;