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
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 (`test`.`t2`.`a`,<exists>((<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 ""; }
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);
