From: Date: May 1 2008 5:53am Subject: bk commit into 6.0 tree (sergefp:1.2628) BUG#35550 List-Archive: http://lists.mysql.com/commits/46254 X-Bug: 35550 Message-Id: <20080501035346.ABEF42783BA@pslp.localdomain> 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 *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 true if there was an error, false 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 li(*((Item_cond*)join->conds)->argument_list()); + if ((*expr)->type() == Item::COND_ITEM) + { + List_iterator 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;