From 35f0faf4c7cd5d0b6270855f274027f85c5aca72 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 16 Sep 2010 16:49:20 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation - Corrected a wrong result that was recorded by the MySQL fix for BUG#39069. - Removed Item_func_isnull::cached_value and all the logic around this custom-made caching of the NULL result because MWL#89 optimizes subqueries before the outer query is being executed, and this cache cannot be made easily to work for all kinds of Items (specifically Item_sum_sum, but others too). --- mysql-test/r/subselect3.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/r/subselect3.result') diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 665bb9a2bde..cfc18413ee0 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -843,8 +843,8 @@ x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22) # 2nd and 3rd columns should be same for x == 11 only SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12) -1 0 1 -2 0 1 +1 0 0 +2 0 0 11 1 1 DROP TABLE t1; # both columns should be same -- cgit v1.2.1 From 8ec5e13f1f0d56afe42e5ded02baeab7a6a60261 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 30 Sep 2010 18:32:44 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation Phase 3: Implementation of re-optimization of subqueries with injected predicates and cost comparison between Materialization and IN->EXISTS strategies. The commit contains the following known problems: - The implementation of EXPLAIN has not been re-engineered to reflect the changes in subquery optimization. EXPLAIN for subqueries is called during the execute phase, which results in different code paths during JOIN::optimize and thus in differing EXPLAIN messages for constant/system tables. - There are some valgrind warnings that need investigation - Several EXPLAINs with minor differences need to be reconsidered after fixing the EXPLAIN problem above. This patch also adds one extra optimizer_switch: 'in_to_exists' for complete manual control of the subquery execution strategies. --- mysql-test/r/subselect3.result | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'mysql-test/r/subselect3.result') diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index cfc18413ee0..39ae0bbb4de 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -840,7 +840,12 @@ x ROW(11, 12) = (SELECT MAX(x), 22) ROW(11, 12) IN (SELECT MAX(x), 22) 1 0 0 2 0 0 11 0 0 -# 2nd and 3rd columns should be same for x == 11 only +# 2nd and 3rd columns should be same +EXPLAIN SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1; x ROW(11, 12) = (SELECT MAX(x), 12) ROW(11, 12) IN (SELECT MAX(x), 12) 1 0 0 -- cgit v1.2.1