From da5214831d3d0f0880b4068a06ce062ca703293f Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 17 May 2012 13:46:05 +0300 Subject: Fix for bug lp:944706, task MDEV-193 The patch enables back constant subquery execution during query optimization after it was disabled during the development of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION). The main idea is that constant subqueries are allowed to be executed during optimization if their execution is not expensive. The approach is as follows: - Constant subqueries are recursively optimized in the beginning of JOIN::optimize of the outer query. This is done by the new method JOIN::optimize_constant_subqueries(). This is done so that the cost of executing these queries can be estimated. - Optimization of the outer query proceeds normally. During this phase the optimizer may request execution of non-expensive constant subqueries. Each place where the optimizer may potentially execute an expensive expression is guarded with the predicate Item::is_expensive(). - The implementation of Item_subselect::is_expensive has been extended to use the number of examined rows (estimated by the optimizer) as a way to determine whether the subquery is expensive or not. - The new system variable "expensive_subquery_limit" controls how many examined rows are considered to be not expensive. The default is 100. In addition, multiple changes were needed to make this solution work in the light of the changes made by MWL#89. These changes were needed to fix various crashes and wrong results, and legacy bugs discovered during development. --- mysql-test/r/subselect4.result | 35 +++++++++++++++++++---------------- 1 file changed, 19 insertions(+), 16 deletions(-) (limited to 'mysql-test/r/subselect4.result') diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 82360f9fe9f..c17ffe75282 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -562,7 +562,7 @@ WHERE f3 = ( SELECT t1.f3 FROM t1 WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref f3 f3 5 const 0 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY eq_ref distinct_key distinct_key 4 func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 @@ -577,7 +577,7 @@ WHERE f3 = ( SELECT f3 FROM t1 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref f3 f3 5 const 0 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 2 SUBQUERY eq_ref distinct_key distinct_key 8 func,func 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 @@ -1220,6 +1220,13 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary 3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE +(SELECT f2 FROM t2 +WHERE f4 <= ALL +(SELECT max(SQ1_t1.f4) +FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 +GROUP BY SQ1_t1.f4)); +ERROR 21000: Subquery returns more than 1 row drop table t1, t2, t3; # # BUG#52317: Assertion failing in Field_varstring::store() @@ -1250,8 +1257,7 @@ FROM t2 JOIN t1 ON t1.f3 WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), @@ -1267,8 +1273,7 @@ FROM t2 JOIN t1 ON t1.f3 WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), @@ -1277,7 +1282,7 @@ FROM t2 JOIN t1 ON t1.f3 WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; COUNT(t2.f3) f9 -0 2 +0 NULL EXPLAIN SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 @@ -1285,8 +1290,7 @@ FROM t2 JOIN t1 WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), @@ -1302,8 +1306,7 @@ FROM t2 JOIN t1 WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), @@ -1325,7 +1328,7 @@ EXPLAIN SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f1 FROM t1) AS field1 FROM t2 GROUP BY field1; field1 NULL @@ -1333,7 +1336,7 @@ EXPLAIN SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -2 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f1 FROM t1) AS field1 FROM t2 ORDER BY field1; field1 NULL @@ -1631,7 +1634,7 @@ SET SESSION optimizer_switch='in_to_exists=on,materialization=off'; EXPLAIN SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); @@ -1852,8 +1855,8 @@ GROUP BY 1, 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 Using temporary; Using filesort 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 MATERIALIZED t1 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table PREPARE st1 FROM " SELECT t2.f1, (SELECT f2 FROM t1 WHERE (7) IN (SELECT f1 FROM t1)) FROM t2 JOIN t3 ON t3.f4 = t2.f4 -- cgit v1.2.1 From 941018f8d10240e9ec457ea6ab984844c343f6b6 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 30 May 2012 00:18:53 +0300 Subject: Patch for mdev-287: CHEAP SQ: A query with subquery in SELECT list, EXISTS, inner joins takes hundreds times longer Analysis: The fix for lp:944706 introduces early subquery optimization. While a subquery is being optimized some of its predicates may be removed. In the test case, the EXISTS subquery is constant, and is evaluated to TRUE. As a result the whole OR is TRUE, and thus the correlated condition "b = alias1.b" is optimized away. The subquery becomes non-correlated. The subquery cache is designed to work only for correlated subqueries. If constant subquery optimization is disallowed, then the constant subquery is not evaluated, the subquery remains correlated, and its execution is cached. As a result execution is fast. However, when the constant subquery was optimized away, it was neither cached by the subquery cache, nor it was cached by the internal subquery caching. The latter was due to the fact that the subquery still appeared as correlated to the subselect_XYZ_engine::exec methods, and they re-executed the subquery on each call to Item_subselect::exec. Solution: The solution is to update the correlated status of the subquery after it has been optimized. This status consists of: - st_select_lex::is_correlated - Item_subselect::is_correlated - SELECT_LEX::uncacheable - SELECT_LEX_UNIT::uncacheable The status is updated by st_select_lex::update_correlated_cache(), and its caller st_select_lex::optimize_unflattened_subqueries. The solution relies on the fact that the optimizer already called st_select_lex::update_used_tables() for each subquery. This allows to efficiently update the correlated status of each subquery without walking the whole subquery tree. Notice that his patch is an improvement over MySQL 5.6 and older, where subqueries are not pre-optimized, and the above analysis is not possible. --- mysql-test/r/subselect4.result | 120 ++++++++++++++++++++--------------------- 1 file changed, 60 insertions(+), 60 deletions(-) (limited to 'mysql-test/r/subselect4.result') diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index c17ffe75282..e9284e427ef 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -226,10 +226,10 @@ NULL 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 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual first equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -237,10 +237,10 @@ NULL 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 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL second equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; RESULT @@ -248,10 +248,10 @@ NULL 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 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select <>((select 1 from `test`.`t2` where 0)) AS `RESULT` from dual group by NULL +Note 1003 select (select 1 from `test`.`t2` where 0) AS `RESULT` from dual group by NULL DROP TABLE t1,t2; # # BUG#45928 "Differing query results depending on MRR and @@ -649,7 +649,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; not_in NULL @@ -671,7 +671,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; not_in NULL @@ -679,7 +679,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; not_in 1 @@ -687,7 +687,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; not_in NULL @@ -695,7 +695,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; not_in NULL @@ -703,7 +703,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; not_in NULL @@ -719,28 +719,28 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; not_in NULL @@ -748,21 +748,21 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; not_in NULL @@ -770,7 +770,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; not_in 1 @@ -778,7 +778,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; not_in NULL @@ -786,7 +786,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; not_in NULL @@ -794,7 +794,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; not_in NULL @@ -835,7 +835,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -857,7 +857,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -865,7 +865,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; not_in 1 @@ -873,7 +873,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; not_in NULL @@ -881,7 +881,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -889,7 +889,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -905,28 +905,28 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -934,21 +934,21 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); f1 f2 EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -956,7 +956,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; not_in 1 @@ -964,7 +964,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; not_in NULL @@ -972,7 +972,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -980,7 +980,7 @@ EXPLAIN SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; not_in NULL @@ -1240,7 +1240,7 @@ EXPLAIN SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 index_subquery k k 5 const 2 Using index +2 SUBQUERY t2 index_subquery k k 5 const 2 Using index DROP TABLE t2; DROP TABLE t1; # @@ -1258,8 +1258,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1274,8 +1274,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT COUNT(f3) FROM t1 WHERE t2.f1) AS f9 FROM t2 JOIN t1 ON t1.f3 @@ -1291,8 +1291,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) GROUP BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 FROM t2 JOIN t1 @@ -1307,8 +1307,8 @@ WHERE ('v') IN (SELECT f4 FROM t2) ORDER BY f9; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT COUNT(t2.f3), (SELECT t2.f1 FROM t1 limit 1) AS f9 FROM t2 JOIN t1 @@ -1390,7 +1390,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1400,7 +1400,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1411,7 +1411,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1431,7 +1431,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1443,7 +1443,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1453,7 +1453,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used 4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1464,7 +1464,7 @@ EXPLAIN SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1474,7 +1474,7 @@ EXPLAIN SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1484,7 +1484,7 @@ EXPLAIN SELECT ( 5 ) IN ( SELECT * FROM v2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY ALL NULL NULL NULL NULL 2 Using where 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -1635,8 +1635,8 @@ EXPLAIN SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index +2 SUBQUERY t3 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); f4 drop table t1,t2,t3; -- cgit v1.2.1