diff options
Diffstat (limited to 'mysql-test/r/subselect4.result')
| -rw-r--r-- | mysql-test/r/subselect4.result | 182 |
1 files changed, 109 insertions, 73 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index de9cdd729f7..1fd638fd42c 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -342,8 +342,8 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN @@ -357,14 +357,14 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; CREATE INDEX t1_IDX ON t1(EMPNUM); @@ -380,8 +380,8 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN @@ -395,14 +395,14 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; EXPLAIN SELECT EMPNAME @@ -417,8 +417,8 @@ WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN @@ -432,14 +432,14 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where -2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; SET SESSION optimizer_switch = @old_optimizer_switch; SET SESSION join_cache_level = @old_join_cache_level; @@ -562,10 +562,10 @@ 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 index condition -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 test.t1.f10 1 -3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary +1 PRIMARY t1 ref f3 f3 5 const 0 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 func 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 @@ -577,15 +577,44 @@ 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 index condition -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 10 test.t1.f10,test.t1.f10 1 -3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary +1 PRIMARY t1 ref f3 f3 5 const 0 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 10 func,func 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); f1 f3 f10 +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( +SELECT t1.f3 FROM t1 +WHERE ( t1.f10 ) IN ( SELECT max(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 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t1 +WHERE f3 = ( +SELECT t1.f3 FROM t1 +WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); +f1 f3 f10 +EXPLAIN +SELECT * FROM t1 +WHERE f3 = ( +SELECT f3 FROM t1 +WHERE ( f10, f10 ) IN ( SELECT max(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 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t1 +WHERE f3 = ( +SELECT f3 FROM t1 +WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); +f1 f3 f10 SET SESSION optimizer_switch = @old_optimizer_switch; drop table t1,t2; # @@ -599,21 +628,21 @@ 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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 MATERIALIZED 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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 MATERIALIZED 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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 MATERIALIZED 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 @@ -628,14 +657,14 @@ 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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 MATERIALIZED 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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +2 MATERIALIZED 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 @@ -785,21 +814,21 @@ 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 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 MATERIALIZED 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 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 MATERIALIZED 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 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 MATERIALIZED 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 @@ -814,14 +843,14 @@ 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 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 MATERIALIZED 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 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan +2 MATERIALIZED 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 @@ -1007,7 +1036,7 @@ WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system PRIMARY NULL NULL NULL 1 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 -2 SUBQUERY t1 index NULL f2 4 NULL 2 Using index +2 MATERIALIZED t1 index NULL f2 4 NULL 2 Using index SELECT t1.f3, MAX(t1.f2) FROM t1, t2 WHERE (t2.pk = t1.pk) AND t2.pk IN (SELECT f2 FROM t1); @@ -1075,6 +1104,10 @@ CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL); INSERT INTO t3 VALUES ('a'), ('b'), ('c'); CREATE TABLE t4 (c1 varchar(1) primary key); INSERT INTO t4 VALUES ('k'), ('d'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra @@ -1091,6 +1124,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 +SET optimizer_switch='materialization=on'; EXPLAIN SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra @@ -1098,7 +1132,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 c1 EXPLAIN @@ -1107,9 +1141,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 1 PRIMARY t4 index NULL PRIMARY 3 NULL 2 Using index; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); c1 c1 +SET optimizer_switch=@save_optimizer_switch; drop table t1, t2, t3, t4; # # LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache() @@ -1177,7 +1212,7 @@ EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 WHERE f4 <= ALL -(SELECT SQ1_t1.f4 +(SELECT max(SQ1_t1.f4) FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); id select_type table type possible_keys key key_len ref rows Extra @@ -1383,7 +1418,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 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 3 DERIVED t1 system NULL NULL NULL NULL 1 4 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL @@ -1469,72 +1504,73 @@ CREATE TABLE t2 (f1b int, f2b int not null, f3b varchar(3) not null, PRIMARY KEY INSERT INTO t2 VALUES (10,5,'d1d'); set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch = 'materialization=off'; +SET optimizer_switch='outer_join_with_cache=off'; +set @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 2 Using temporary SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); f1 f2 8 8 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'u' EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d -SET @@optimizer_switch = 'materialization=on'; +set @@optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index -2 SUBQUERY t0 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 2 Using temporary SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); f1 f2 8 8 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'u' EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d set @@optimizer_switch=@save_optimizer_switch; @@ -1619,7 +1655,7 @@ FROM t1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 drop table t1, t2, t3; # # LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table @@ -1639,10 +1675,10 @@ WHERE t2.f2 = (SELECT f2 FROM t3 WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) AND t2.f2 = t1.f1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition +1 PRIMARY t1 ref f1 f1 5 const 0 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index SELECT * FROM t1, t2 WHERE t2.f2 = (SELECT f2 FROM t3 @@ -1656,10 +1692,10 @@ WHERE t2.f2 = (SELECT f2 FROM t3 WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) AND t2.f2 = t1.f1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition +1 PRIMARY t1 ref f1 f1 5 const 0 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index SELECT * FROM t1, t2 WHERE t2.f2 = (SELECT f2 FROM t3 @@ -1687,7 +1723,7 @@ FROM t1)); 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 SUBQUERY t4 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT * FROM t2, t3 WHERE t3.f1 = ( @@ -1733,7 +1769,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 SELECT col_int_key FROM t2 WHERE (SELECT SUBQUERY2_t1.col_int_key @@ -1758,7 +1794,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT col_int_key FROM t2 WHERE (SELECT SUBQUERY2_t1.col_int_key @@ -1786,7 +1822,7 @@ EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; EXECUTE st1; f1 f2 f3 @@ -1816,7 +1852,7 @@ EXPLAIN SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; EXECUTE st3; f1 f2 f3 @@ -1848,7 +1884,7 @@ 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 SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found +3 MATERIALIZED t1 system NULL NULL NULL NULL 0 const row not found 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 @@ -1880,7 +1916,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT t1.f4 FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3 WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ; |