At file:///home/psergey/dev/maria-5.3-subqueries-r7/ ------------------------------------------------------------ revno: 2759 revision-id: psergey@askmonty.org-20100217100527-k81b7torhmj99moy parent: psergey@askmonty.org-20100215215306-hc0levm9ag1lv1b1 committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.3-subqueries-r7 timestamp: Wed 2010-02-17 13:05:27 +0300 message: Subquery optimizations backport: - Update test results - More comments - Add Item_in_optimizer::transform() which was lost in backport === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-01-17 20:52:20 +0000 +++ b/mysql-test/r/subselect.result 2010-02-17 10:05:27 +0000 @@ -1377,7 +1377,7 @@ 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) -drop table t1, t2, t3; +drop table t0, t1, t2, t3; create table t1 (a int, b int); create table t2 (a int, b int); create table t3 (a int, b int); === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect3.result 2010-02-17 10:05:27 +0000 @@ -873,7 +873,7 @@ Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 Note 1276 Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2 Error 1054 Unknown column 'c' in 'field list' -Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from dual group by `c`) AS `(SELECT COUNT(a) FROM +Note 1003 select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `t1`.`c`) AS `(SELECT COUNT(a) FROM (SELECT COUNT(b) FROM t1) AS x GROUP BY c )` from `test`.`t1` group by `test`.`t1`.`b`) `y` DROP TABLE t1; @@ -1117,7 +1117,7 @@ explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; LooseScan +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) 1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer drop table t1, t3, t4; === modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2010-01-17 14:51:10 +0000 +++ b/mysql-test/r/subselect4.result 2010-02-17 10:05:27 +0000 @@ -13,9 +13,9 @@ WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL a 5 NULL 2 Using index; Using temporary +1 PRIMARY t1 index NULL a 5 NULL 2 Using where; Using index; Using temporary 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found # should not crash the next statement SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) @@ -77,10 +77,10 @@ EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 ref d d 5 const 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = '0')) AS `RESULT` from dual +Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = '0')) AS `RESULT` from `test`.`t1` first equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -88,10 +88,10 @@ EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 ref d d 5 const 1 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = ifnull('0',NULL))) AS `RESULT` from dual group by '0' +Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = ifnull('0',NULL))) AS `RESULT` from `test`.`t1` group by '0' second equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -99,10 +99,10 @@ EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 ref d d 5 const 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = '0')) AS `RESULT` from dual group by '0' +Note 1003 select (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = '0')) AS `RESULT` from `test`.`t1` group by '0' DROP TABLE t1,t2; # # BUG#45928 "Differing query results depending on MRR and === modified file 'sql/item.h' --- a/sql/item.h 2010-02-11 23:59:58 +0000 +++ b/sql/item.h 2010-02-17 10:05:27 +0000 @@ -2817,6 +2817,17 @@ }; +/* + Cached_item_XXX objects are not exactly caches. They do the following: + + Each Cached_item_XXX object has + - its source item + - saved value of the source item + - cmp() method that compares the saved value with the current value of the + source item, and if they were not equal saves item's value into the saved + value. +*/ + class Cached_item :public Sql_alloc { public: === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-01-17 14:55:08 +0000 +++ b/sql/item_cmpfunc.cc 2010-02-17 10:05:27 +0000 @@ -1649,6 +1649,70 @@ } +/** + Transform an Item_in_optimizer and its arguments with a callback function. + + @param transformer the transformer callback function to be applied to the + nodes of the tree of the object + @param parameter to be passed to the transformer + + @detail + Recursively transform the left and the right operand of this Item. The + Right operand is an Item_in_subselect or its subclass. To avoid the + creation of new Items, we use the fact the the left operand of the + Item_in_subselect is the same as the one of 'this', so instead of + transforming its operand, we just assign the left operand of the + Item_in_subselect to be equal to the left operand of 'this'. + The transformation is not applied further to the subquery operand + if the IN predicate. + + @returns + @retval pointer to the transformed item + @retval NULL if an error occurred +*/ + +Item *Item_in_optimizer::transform(Item_transformer transformer, uchar *argument) +{ + Item *new_item; + + DBUG_ASSERT(!current_thd->is_stmt_prepare()); + DBUG_ASSERT(arg_count == 2); + + /* Transform the left IN operand. */ + new_item= (*args)->transform(transformer, argument); + if (!new_item) + return 0; + /* + THD::change_item_tree() should be called only if the tree was + really transformed, i.e. when a new item has been created. + Otherwise we'll be allocating a lot of unnecessary memory for + change records at each execution. + */ + if ((*args) != new_item) + current_thd->change_item_tree(args, new_item); + + /* + Transform the right IN operand which should be an Item_in_subselect or a + subclass of it. The left operand of the IN must be the same as the left + operand of this Item_in_optimizer, so in this case there is no further + transformation, we only make both operands the same. + TODO: is it the way it should be? + */ + DBUG_ASSERT((args[1])->type() == Item::SUBSELECT_ITEM && + (((Item_subselect*)(args[1]))->substype() == + Item_subselect::IN_SUBS || + ((Item_subselect*)(args[1]))->substype() == + Item_subselect::ALL_SUBS || + ((Item_subselect*)(args[1]))->substype() == + Item_subselect::ANY_SUBS)); + + Item_in_subselect *in_arg= (Item_in_subselect*)args[1]; + in_arg->left_expr= args[0]; + + return (this->*transformer)(argument); +} + + longlong Item_func_eq::val_int() { DBUG_ASSERT(fixed == 1); === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-02-11 21:58:23 +0000 +++ b/sql/item_cmpfunc.h 2010-02-17 10:05:27 +0000 @@ -241,6 +241,7 @@ const char *func_name() const { return "<in_optimizer>"; } Item_cache **get_cache() { return &cache; } void keep_top_level_cache(); + Item *transform(Item_transformer transformer, uchar *arg); }; class Comp_creator === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-02-15 21:53:06 +0000 +++ b/sql/item_subselect.cc 2010-02-17 10:05:27 +0000 @@ -1312,7 +1312,7 @@ (char *)in_left_expr_name); master_unit->uncacheable|= UNCACHEABLE_DEPENDENT; - select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; + //psergey: placed then removed: select_lex->uncacheable|= UNCACHEABLE_DEPENDENT; } if (!abort_on_null && left_expr->maybe_null && !pushed_cond_guards) === modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2010-02-15 21:53:06 +0000 +++ b/sql/opt_subselect.cc 2010-02-17 10:05:27 +0000 @@ -120,7 +120,7 @@ SELECT_LEX *current= thd->lex->current_select; thd->lex->current_select= current->return_after_parsing(); char const *save_where= thd->where; - thd->where= " IN/ALL/ANY subquery"; + thd->where= "IN/ALL/ANY subquery"; bool failure= !in_subs->left_expr->fixed && in_subs->left_expr->fix_fields(thd, &in_subs->left_expr);