B4BY.588

: / usr / share / mysql-test / r /
Filename : optimizer_switch.result
back
BUG#37120 optimizer_switch allowable values not according to specification

select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='materialization=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='semijoin=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='loosescan=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='semijoin=off,materialization=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='materialization=off,semijoin=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='semijoin=off,materialization=off,loosescan=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='semijoin=off,loosescan=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
set optimizer_switch='materialization=off,loosescan=off';
select @@optimizer_switch;
@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
set optimizer_switch='default';
create table t1 (a1 char(8), a2 char(8));
create table t2 (b1 char(8), b2 char(8));
insert into t1 values ('1 - 00', '2 - 00');
insert into t1 values ('1 - 01', '2 - 01');
insert into t1 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 01', '2 - 01');
insert into t2 values ('1 - 01', '2 - 01');
insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 03', '2 - 03');
set @@optimizer_switch="semijoin=off";
prepare st1 from
"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
set @@optimizer_switch="semijoin=on,materialization=off";
execute st1;
a1	a2
1 - 01	2 - 01
1 - 02	2 - 02
set @@optimizer_switch="semijoin=off,materialization=on";
execute st1;
a1	a2
1 - 01	2 - 01
1 - 02	2 - 02
set optimizer_switch='default';
set @@optimizer_switch="materialization=off";
prepare st1 from
"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
set @@optimizer_switch="semijoin=off,materialization=on";
execute st1;
a1	a2
1 - 01	2 - 01
1 - 02	2 - 02
set @@optimizer_switch="semijoin=on,materialization=off";
execute st1;
a1	a2
1 - 01	2 - 01
1 - 02	2 - 02
set optimizer_switch='default';
drop table t1, t2;
#
# BUG#47367 Crash in Name_resolution_context::process_error
#
SET SESSION optimizer_switch = 'default,semijoin=off';
CREATE TABLE t1 (f1 INTEGER);
CREATE TABLE t2 LIKE t1;
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END|
CALL p1;
f1
ALTER TABLE t2 CHANGE COLUMN f1 my_column INT;
CALL p1;
f1
DROP PROCEDURE p1;
# Restore the original column list of table t2:
ALTER TABLE t2 CHANGE COLUMN my_column f1 INT;
SET SESSION optimizer_switch = 'semijoin=on';
# Recreate procedure so that we eliminate any caching effects
CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END|
CALL p1;
f1
ALTER TABLE t2 CHANGE COLUMN f1 my_column INT;
CALL p1;
f1
DROP PROCEDURE p1;
DROP TABLE t1, t2;
SET SESSION optimizer_switch = 'default';
#
# Bug #46744 Crash in optimize_semijoin_nests on empty view
# with limit and procedure.
#
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1 ( f1 int );
CREATE TABLE t2 ( f1 int );
insert into t2 values (5), (7);
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
create procedure p1() 
select COUNT(*) 
FROM v1 WHERE f1 IN 
(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
SET SESSION optimizer_switch = 'semijoin=on';
CALL p1();
COUNT(*)
0
SET SESSION optimizer_switch = 'semijoin=off';
CALL p1();
COUNT(*)
0
drop table t1, t2;
drop view v1;
drop procedure p1;
set SESSION optimizer_switch='default';
# End of bug#46744
#
# Bug#50489: another segfault in fix_semijoin_strategies... 
#
CREATE TABLE it (
id INT NOT NULL,
expr_key INT NOT NULL,
expr_nokey INT NOT NULL,
expr_padder INT DEFAULT NULL,
KEY expr_key(expr_key)
);
INSERT INTO it VALUES (135,218264606,218264606,100);
INSERT INTO it VALUES (201,810783319,810783319,200);
CREATE TABLE ot (
id INT NOT NULL,
expr_key INT NOT NULL,
expr_nokey INT NOT NULL,
KEY expr_key(expr_key)
);
CREATE PROCEDURE run_n_times(x int)
BEGIN
DECLARE c int;
WHILE x DO
SET x = x-1;
SELECT COUNT(expr_key) INTO c FROM ot
WHERE expr_key IN (SELECT expr_nokey FROM it)
AND ot.expr_key<100000000;
END WHILE;
END;
SET optimizer_switch="default";
call run_n_times(1);
SET optimizer_switch="firstmatch=off,materialization=off";
call run_n_times(1);
SET optimizer_switch="default";
call run_n_times(1);
DROP PROCEDURE run_n_times;
CREATE PROCEDURE run_n_times(x int)
BEGIN
DECLARE c int;
WHILE x DO
SET x = x-1;
SELECT COUNT(expr_key) INTO c FROM ot
WHERE expr_key IN (SELECT expr_nokey FROM it)
AND ot.expr_key<100000000;
END WHILE;
END;
SET optimizer_switch="firstmatch=off,materialization=off";
call run_n_times(1);
SET optimizer_switch="default";
call run_n_times(1);
DROP PROCEDURE run_n_times;
CREATE PROCEDURE run_n_times(x int)
BEGIN
DECLARE c int;
WHILE x DO
SET x = x-1;
SELECT COUNT(expr_key) INTO c FROM ot
WHERE expr_key IN (SELECT expr_nokey FROM it)
AND ot.expr_key<100000000;
END WHILE;
END;
SET optimizer_switch="semijoin=off,materialization=off";
call run_n_times(1);
SET optimizer_switch="default";
call run_n_times(1);
DROP PROCEDURE run_n_times;
DROP TABLE it, ot;
# 
# BUG#31480: Incorrect result for nested subquery when executed via semijoin
# 
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL);
CREATE TABLE t3 (e INT NOT NULL);
CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL);
INSERT INTO t1 VALUES (1,10);
INSERT INTO t1 VALUES (2,10);
INSERT INTO t1 VALUES (1,20);
INSERT INTO t1 VALUES (2,20);
INSERT INTO t1 VALUES (3,20);
INSERT INTO t1 VALUES (2,30);
INSERT INTO t1 VALUES (4,40);
INSERT INTO t2 VALUES (2,10);
INSERT INTO t2 VALUES (2,20);
INSERT INTO t2 VALUES (4,10);
INSERT INTO t2 VALUES (5,10);
INSERT INTO t2 VALUES (3,20);
INSERT INTO t2 VALUES (2,40);
INSERT INTO t3 VALUES (10);
INSERT INTO t3 VALUES (30);
INSERT INTO t3 VALUES (10);
INSERT INTO t3 VALUES (20);
INSERT INTO t4 VALUES (2,10);
INSERT INTO t4 VALUES (2,10);
INSERT INTO t4 VALUES (3,10);
INSERT INTO t4 VALUES (4,10);
INSERT INTO t4 VALUES (4,20);
INSERT INTO t4 VALUES (4,20);
# Reference to the parent query block (used tables was wrong)
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	10
2	20
3	20
2	30
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	10
2	20
3	20
2	30
# Subquery with GROUP BY and HAVING
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
GROUP BY f
HAVING ta.a=tc.f));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
GROUP BY f
HAVING ta.a=tc.f));
a	b
2	10
2	20
3	20
2	30
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
GROUP BY f
HAVING ta.a=tc.f));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
GROUP BY f
HAVING ta.a=tc.f));
a	b
2	10
2	20
3	20
2	30
# Subquery with ORDER BY and LIMIT
set @@optimizer_switch='materialization=off,semijoin=off';
# NOTE: The ordered subquery should have a LIMIT clause to make sense
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d IN (SELECT g FROM t4 as tc
WHERE ta.a=tc.f
ORDER BY tc.f));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	6	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d IN (SELECT g FROM t4 as tc
WHERE ta.a=tc.f
ORDER BY tc.f));
a	b
2	10
2	20
2	30
4	40
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d IN (SELECT g FROM t4 as tc
WHERE ta.a=tc.f
ORDER BY tc.f));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
1	SIMPLE	tc	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d IN (SELECT g FROM t4 as tc
WHERE ta.a=tc.f
ORDER BY tc.f));
a	b
2	10
2	20
2	30
4	40
# Reference to the transformed-away query block (dependency was wrong)
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE tb.d=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE tb.d=tc.e));
a	b
2	10
2	20
3	20
2	30
4	40
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE tb.d=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE tb.d=tc.e));
a	b
2	10
2	20
3	20
2	30
4	40
# Reference above the parent query block (should not be affected)
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS t
WHERE t.a NOT IN (SELECT a FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE t.b=tc.e)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
3	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS t
WHERE t.a NOT IN (SELECT a FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE t.b=tc.e)));
a	b
1	10
1	20
4	40
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS t
WHERE t.a NOT IN (SELECT a FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE t.b=tc.e)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where; Start temporary
2	DEPENDENT SUBQUERY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS t
WHERE t.a NOT IN (SELECT a FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT e FROM t3 as tc
WHERE t.b=tc.e)));
a	b
1	10
1	20
4	40
# EXISTS with reference to the parent query block
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE EXISTS (SELECT * FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE EXISTS (SELECT * FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	10
2	20
3	20
2	30
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE EXISTS (SELECT * FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE EXISTS (SELECT * FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	10
2	20
3	20
2	30
# Scalar subquery with reference to the parent query block
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	10
2	20
3	20
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d = (SELECT MIN(e) FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	10
2	20
3	20
# Combine scalar subquery with quantified comparison subquery
set @@optimizer_switch='materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE (SELECT MIN(e) FROM t3 as tc
WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where
2	DEPENDENT SUBQUERY	tb	ALL	NULL	NULL	NULL	NULL	6	Using where
4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE (SELECT MIN(e) FROM t3 as tc
WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	20
2	30
set @@optimizer_switch='materialization=off,semijoin=on';
EXPLAIN SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE (SELECT MIN(e) FROM t3 as tc
WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	tb	ALL	NULL	NULL	NULL	NULL	6	Start temporary
1	PRIMARY	ta	ALL	NULL	NULL	NULL	NULL	7	Using where; End temporary; Using join buffer (Block Nested Loop)
4	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
3	DEPENDENT SUBQUERY	tc	ALL	NULL	NULL	NULL	NULL	4	Using where
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE (SELECT MIN(e) FROM t3 as tc
WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc
WHERE ta.b=tc.e));
a	b
2	20
2	30
DROP TABLE t1, t2, t3, t4;
set @@optimizer_switch='default';
# End of BUG#31480