1 2BUG#37120 optimizer_switch allowable values not according to specification 3 4select @@optimizer_switch; 5@@optimizer_switch 6index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 7set optimizer_switch='default'; 8set optimizer_switch='materialization=off'; 9select @@optimizer_switch; 10@@optimizer_switch 11index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 12set optimizer_switch='default'; 13set optimizer_switch='semijoin=off'; 14select @@optimizer_switch; 15@@optimizer_switch 16index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 17set optimizer_switch='default'; 18set optimizer_switch='loosescan=off'; 19select @@optimizer_switch; 20@@optimizer_switch 21index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 22set optimizer_switch='default'; 23set optimizer_switch='semijoin=off,materialization=off'; 24select @@optimizer_switch; 25@@optimizer_switch 26index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 27set optimizer_switch='default'; 28set optimizer_switch='materialization=off,semijoin=off'; 29select @@optimizer_switch; 30@@optimizer_switch 31index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 32set optimizer_switch='default'; 33set optimizer_switch='semijoin=off,materialization=off,loosescan=off'; 34select @@optimizer_switch; 35@@optimizer_switch 36index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 37set optimizer_switch='default'; 38set optimizer_switch='semijoin=off,loosescan=off'; 39select @@optimizer_switch; 40@@optimizer_switch 41index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 42set optimizer_switch='default'; 43set optimizer_switch='materialization=off,loosescan=off'; 44select @@optimizer_switch; 45@@optimizer_switch 46index_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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on,favor_range_scan=off 47set optimizer_switch='default'; 48create table t1 (a1 char(8), a2 char(8)); 49create table t2 (b1 char(8), b2 char(8)); 50insert into t1 values ('1 - 00', '2 - 00'); 51insert into t1 values ('1 - 01', '2 - 01'); 52insert into t1 values ('1 - 02', '2 - 02'); 53insert into t2 values ('1 - 01', '2 - 01'); 54insert into t2 values ('1 - 01', '2 - 01'); 55insert into t2 values ('1 - 02', '2 - 02'); 56insert into t2 values ('1 - 02', '2 - 02'); 57insert into t2 values ('1 - 03', '2 - 03'); 58set @@optimizer_switch="semijoin=off"; 59prepare st1 from 60"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)"; 61set @@optimizer_switch="semijoin=on,materialization=off"; 62execute st1; 63a1 a2 641 - 01 2 - 01 651 - 02 2 - 02 66set @@optimizer_switch="semijoin=off,materialization=on"; 67execute st1; 68a1 a2 691 - 01 2 - 01 701 - 02 2 - 02 71set optimizer_switch='default'; 72set @@optimizer_switch="materialization=off"; 73prepare st1 from 74"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)"; 75set @@optimizer_switch="semijoin=off,materialization=on"; 76execute st1; 77a1 a2 781 - 01 2 - 01 791 - 02 2 - 02 80set @@optimizer_switch="semijoin=on,materialization=off"; 81execute st1; 82a1 a2 831 - 01 2 - 01 841 - 02 2 - 02 85set optimizer_switch='default'; 86drop table t1, t2; 87# 88# BUG#47367 Crash in Name_resolution_context::process_error 89# 90SET SESSION optimizer_switch = 'default,semijoin=off'; 91CREATE TABLE t1 (f1 INTEGER); 92CREATE TABLE t2 LIKE t1; 93CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| 94CALL p1; 95f1 96ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; 97CALL p1; 98f1 99DROP PROCEDURE p1; 100# Restore the original column list of table t2: 101ALTER TABLE t2 CHANGE COLUMN my_column f1 INT; 102SET SESSION optimizer_switch = 'semijoin=on'; 103# Recreate procedure so that we eliminate any caching effects 104CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t1 WHERE f1 IN (SELECT f1 FROM t2); END| 105CALL p1; 106f1 107ALTER TABLE t2 CHANGE COLUMN f1 my_column INT; 108CALL p1; 109f1 110DROP PROCEDURE p1; 111DROP TABLE t1, t2; 112SET SESSION optimizer_switch = 'default'; 113# 114# Bug #46744 Crash in optimize_semijoin_nests on empty view 115# with limit and procedure. 116# 117DROP TABLE IF EXISTS t1, t2; 118DROP VIEW IF EXISTS v1; 119DROP PROCEDURE IF EXISTS p1; 120CREATE TABLE t1 ( f1 int ); 121CREATE TABLE t2 ( f1 int ); 122insert into t2 values (5), (7); 123CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2; 124create procedure p1() 125select COUNT(*) 126FROM v1 WHERE f1 IN 127(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1)); 128SET SESSION optimizer_switch = 'semijoin=on'; 129CALL p1(); 130COUNT(*) 1310 132SET SESSION optimizer_switch = 'semijoin=off'; 133CALL p1(); 134COUNT(*) 1350 136drop table t1, t2; 137drop view v1; 138drop procedure p1; 139set SESSION optimizer_switch='default'; 140# End of bug#46744 141# 142# Bug#50489: another segfault in fix_semijoin_strategies... 143# 144CREATE TABLE it ( 145id INT NOT NULL, 146expr_key INT NOT NULL, 147expr_nokey INT NOT NULL, 148expr_padder INT DEFAULT NULL, 149KEY expr_key(expr_key) 150); 151INSERT INTO it VALUES (135,218264606,218264606,100); 152INSERT INTO it VALUES (201,810783319,810783319,200); 153CREATE TABLE ot ( 154id INT NOT NULL, 155expr_key INT NOT NULL, 156expr_nokey INT NOT NULL, 157KEY expr_key(expr_key) 158); 159CREATE PROCEDURE run_n_times(x int) 160BEGIN 161DECLARE c int; 162WHILE x DO 163SET x = x-1; 164SELECT COUNT(expr_key) INTO c FROM ot 165WHERE expr_key IN (SELECT expr_nokey FROM it) 166AND ot.expr_key<100000000; 167END WHILE; 168END; 169SET optimizer_switch="default"; 170call run_n_times(1); 171SET optimizer_switch="firstmatch=off,materialization=off"; 172call run_n_times(1); 173SET optimizer_switch="default"; 174call run_n_times(1); 175DROP PROCEDURE run_n_times; 176CREATE PROCEDURE run_n_times(x int) 177BEGIN 178DECLARE c int; 179WHILE x DO 180SET x = x-1; 181SELECT COUNT(expr_key) INTO c FROM ot 182WHERE expr_key IN (SELECT expr_nokey FROM it) 183AND ot.expr_key<100000000; 184END WHILE; 185END; 186SET optimizer_switch="firstmatch=off,materialization=off"; 187call run_n_times(1); 188SET optimizer_switch="default"; 189call run_n_times(1); 190DROP PROCEDURE run_n_times; 191CREATE PROCEDURE run_n_times(x int) 192BEGIN 193DECLARE c int; 194WHILE x DO 195SET x = x-1; 196SELECT COUNT(expr_key) INTO c FROM ot 197WHERE expr_key IN (SELECT expr_nokey FROM it) 198AND ot.expr_key<100000000; 199END WHILE; 200END; 201SET optimizer_switch="semijoin=off,materialization=off"; 202call run_n_times(1); 203SET optimizer_switch="default"; 204call run_n_times(1); 205DROP PROCEDURE run_n_times; 206DROP TABLE it, ot; 207# 208# BUG#31480: Incorrect result for nested subquery when executed via semijoin 209# 210CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); 211CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL); 212CREATE TABLE t3 (e INT NOT NULL); 213CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL); 214INSERT INTO t1 VALUES (1,10); 215INSERT INTO t1 VALUES (2,10); 216INSERT INTO t1 VALUES (1,20); 217INSERT INTO t1 VALUES (2,20); 218INSERT INTO t1 VALUES (3,20); 219INSERT INTO t1 VALUES (2,30); 220INSERT INTO t1 VALUES (4,40); 221INSERT INTO t2 VALUES (2,10); 222INSERT INTO t2 VALUES (2,20); 223INSERT INTO t2 VALUES (4,10); 224INSERT INTO t2 VALUES (5,10); 225INSERT INTO t2 VALUES (3,20); 226INSERT INTO t2 VALUES (2,40); 227INSERT INTO t3 VALUES (10); 228INSERT INTO t3 VALUES (30); 229INSERT INTO t3 VALUES (10); 230INSERT INTO t3 VALUES (20); 231INSERT INTO t4 VALUES (2,10); 232INSERT INTO t4 VALUES (2,10); 233INSERT INTO t4 VALUES (3,10); 234INSERT INTO t4 VALUES (4,10); 235INSERT INTO t4 VALUES (4,20); 236INSERT INTO t4 VALUES (4,20); 237# Reference to the parent query block (used tables was wrong) 238set @@optimizer_switch='materialization=off,semijoin=off'; 239analyze table t1; 240Table Op Msg_type Msg_text 241test.t1 analyze status OK 242analyze table t2; 243Table Op Msg_type Msg_text 244test.t2 analyze status OK 245analyze table t3; 246Table Op Msg_type Msg_text 247test.t3 analyze status OK 248analyze table t4; 249Table Op Msg_type Msg_text 250test.t4 analyze status OK 251EXPLAIN SELECT * FROM t1 AS ta 252WHERE ta.a IN (SELECT c FROM t2 AS tb 253WHERE tb.d >= SOME(SELECT e FROM t3 as tc 254WHERE ta.b=tc.e)); 255id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2561 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 2572 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 2583 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 259Warnings: 260Note 1276 Field or reference 'test.ta.b' of SELECT #3 was resolved in SELECT #1 261Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where (<nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` `tc` where ((`test`.`ta`.`b` = `test`.`tc`.`e`) and (<cache>(`test`.`tb`.`d`) >= `test`.`tc`.`e`))))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 262SELECT * FROM t1 AS ta 263WHERE ta.a IN (SELECT c FROM t2 AS tb 264WHERE tb.d >= SOME(SELECT e FROM t3 as tc 265WHERE ta.b=tc.e)); 266a b 2672 10 2682 20 2693 20 2702 30 271set @@optimizer_switch='materialization=off,semijoin=on'; 272EXPLAIN SELECT * FROM t1 AS ta 273WHERE ta.a IN (SELECT c FROM t2 AS tb 274WHERE tb.d >= SOME(SELECT e FROM t3 as tc 275WHERE ta.b=tc.e)); 276id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2771 PRIMARY tb NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 2781 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 2793 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 280Warnings: 281Note 1276 Field or reference 'test.ta.b' of SELECT #3 was resolved in SELECT #1 282Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`ta`.`a` = `test`.`tb`.`c`) and <nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` `tc` where ((`test`.`ta`.`b` = `test`.`tc`.`e`) and (<cache>(`test`.`tb`.`d`) >= `test`.`tc`.`e`)))))) 283SELECT * FROM t1 AS ta 284WHERE ta.a IN (SELECT c FROM t2 AS tb 285WHERE tb.d >= SOME(SELECT e FROM t3 as tc 286WHERE ta.b=tc.e)); 287a b 2882 10 2892 20 2903 20 2912 30 292# Subquery with GROUP BY and HAVING 293set @@optimizer_switch='materialization=off,semijoin=off'; 294EXPLAIN SELECT * FROM t1 AS ta 295WHERE ta.a IN (SELECT c FROM t2 AS tb 296WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc 297GROUP BY f 298HAVING ta.a=tc.f)); 299id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3001 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 3012 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 3023 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 303Warnings: 304Note 1276 Field or reference 'ta.a' of SELECT #3 was resolved in SELECT #1 305Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where (<nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t4` `tc` group by `test`.`tc`.`f` having ((`test`.`ta`.`a` = `test`.`tc`.`f`) and (<cache>(`test`.`tb`.`d`) >= <ref_null_helper>(sum(`test`.`tc`.`g`))))))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 306SELECT * FROM t1 AS ta 307WHERE ta.a IN (SELECT c FROM t2 AS tb 308WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc 309GROUP BY f 310HAVING ta.a=tc.f)); 311a b 3122 10 3132 20 3143 20 3152 30 316set @@optimizer_switch='materialization=off,semijoin=on'; 317EXPLAIN SELECT * FROM t1 AS ta 318WHERE ta.a IN (SELECT c FROM t2 AS tb 319WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc 320GROUP BY f 321HAVING ta.a=tc.f)); 322id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3231 PRIMARY tb NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 3241 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 3253 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 326Warnings: 327Note 1276 Field or reference 'ta.a' of SELECT #3 was resolved in SELECT #1 328Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`ta`.`a` = `test`.`tb`.`c`) and <nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t4` `tc` group by `test`.`tc`.`f` having ((`test`.`ta`.`a` = `test`.`tc`.`f`) and (<cache>(`test`.`tb`.`d`) >= <ref_null_helper>(sum(`test`.`tc`.`g`)))))))) 329SELECT * FROM t1 AS ta 330WHERE ta.a IN (SELECT c FROM t2 AS tb 331WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc 332GROUP BY f 333HAVING ta.a=tc.f)); 334a b 3352 10 3362 20 3373 20 3382 30 339# Subquery with ORDER BY and LIMIT 340set @@optimizer_switch='materialization=off,semijoin=off'; 341# NOTE: The ordered subquery should have a LIMIT clause to make sense 342EXPLAIN SELECT * FROM t1 AS ta 343WHERE ta.a IN (SELECT c FROM t2 AS tb 344WHERE tb.d IN (SELECT g FROM t4 as tc 345WHERE ta.a=tc.f 346ORDER BY tc.f)); 347id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3481 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 3492 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 3503 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 6 16.67 Using where 351Warnings: 352Note 1276 Field or reference 'test.ta.a' of SELECT #3 was resolved in SELECT #1 353Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where (<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t4` `tc` where ((`test`.`ta`.`a` = `test`.`tc`.`f`) and (<cache>(`test`.`tb`.`d`) = `test`.`tc`.`g`)))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 354SELECT * FROM t1 AS ta 355WHERE ta.a IN (SELECT c FROM t2 AS tb 356WHERE tb.d IN (SELECT g FROM t4 as tc 357WHERE ta.a=tc.f 358ORDER BY tc.f)); 359a b 3602 10 3612 20 3622 30 3634 40 364set @@optimizer_switch='materialization=off,semijoin=on'; 365EXPLAIN SELECT * FROM t1 AS ta 366WHERE ta.a IN (SELECT c FROM t2 AS tb 367WHERE tb.d IN (SELECT g FROM t4 as tc 368WHERE ta.a=tc.f 369ORDER BY tc.f)); 370id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3711 SIMPLE tb NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 3721 SIMPLE tc NULL ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (Block Nested Loop) 3731 SIMPLE ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 374Warnings: 375Note 1276 Field or reference 'test.ta.a' of SELECT #3 was resolved in SELECT #1 376Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t4` `tc` join `test`.`t2` `tb`) where ((`test`.`tc`.`g` = `test`.`tb`.`d`) and (`test`.`tc`.`f` = `test`.`tb`.`c`) and (`test`.`ta`.`a` = `test`.`tb`.`c`)) 377SELECT * FROM t1 AS ta 378WHERE ta.a IN (SELECT c FROM t2 AS tb 379WHERE tb.d IN (SELECT g FROM t4 as tc 380WHERE ta.a=tc.f 381ORDER BY tc.f)); 382a b 3832 10 3842 20 3852 30 3864 40 387# Reference to the transformed-away query block (dependency was wrong) 388set @@optimizer_switch='materialization=off,semijoin=off'; 389EXPLAIN SELECT * FROM t1 AS ta 390WHERE ta.a IN (SELECT c FROM t2 AS tb 391WHERE tb.d >= SOME(SELECT e FROM t3 as tc 392WHERE tb.d=tc.e)); 393id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3941 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 3952 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 3963 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 397Warnings: 398Note 1276 Field or reference 'test.tb.d' of SELECT #3 was resolved in SELECT #2 399Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where (<nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` `tc` where ((`test`.`tb`.`d` = `test`.`tc`.`e`) and (<cache>(`test`.`tb`.`d`) >= `test`.`tc`.`e`))))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 400SELECT * FROM t1 AS ta 401WHERE ta.a IN (SELECT c FROM t2 AS tb 402WHERE tb.d >= SOME(SELECT e FROM t3 as tc 403WHERE tb.d=tc.e)); 404a b 4052 10 4062 20 4073 20 4082 30 4094 40 410set @@optimizer_switch='materialization=off,semijoin=on'; 411EXPLAIN SELECT * FROM t1 AS ta 412WHERE ta.a IN (SELECT c FROM t2 AS tb 413WHERE tb.d >= SOME(SELECT e FROM t3 as tc 414WHERE tb.d=tc.e)); 415id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4161 PRIMARY tb NULL ALL NULL NULL NULL NULL 6 100.00 Using where; Start temporary 4171 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 4183 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 419Warnings: 420Note 1276 Field or reference 'test.tb.d' of SELECT #3 was resolved in SELECT #2 421Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`ta`.`a` = `test`.`tb`.`c`) and <nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#3 */ select 1 from `test`.`t3` `tc` where ((`test`.`tb`.`d` = `test`.`tc`.`e`) and (<cache>(`test`.`tb`.`d`) >= `test`.`tc`.`e`)))))) 422SELECT * FROM t1 AS ta 423WHERE ta.a IN (SELECT c FROM t2 AS tb 424WHERE tb.d >= SOME(SELECT e FROM t3 as tc 425WHERE tb.d=tc.e)); 426a b 4272 10 4282 20 4293 20 4302 30 4314 40 432# Reference above the parent query block (should not be affected) 433set @@optimizer_switch='materialization=off,semijoin=off'; 434EXPLAIN SELECT * FROM t1 AS t 435WHERE t.a NOT IN (SELECT a FROM t1 AS ta 436WHERE ta.a IN (SELECT c FROM t2 AS tb 437WHERE tb.d >= SOME(SELECT e FROM t3 as tc 438WHERE t.b=tc.e))); 439id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4401 PRIMARY t NULL ALL NULL NULL NULL NULL 7 100.00 Using where 4412 DEPENDENT SUBQUERY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where 4423 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 4434 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 444Warnings: 445Note 1276 Field or reference 'test.t.b' of SELECT #4 was resolved in SELECT #1 446Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t1` `t` where (not(<in_optimizer>(`test`.`t`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` `ta` where (<in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#3 */ select 1 from `test`.`t2` `tb` where (<nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#4 */ select 1 from `test`.`t3` `tc` where ((`test`.`t`.`b` = `test`.`tc`.`e`) and (<cache>(`test`.`tb`.`d`) >= `test`.`tc`.`e`))))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) and (<cache>(`test`.`t`.`a`) = `test`.`ta`.`a`)))))) 447SELECT * FROM t1 AS t 448WHERE t.a NOT IN (SELECT a FROM t1 AS ta 449WHERE ta.a IN (SELECT c FROM t2 AS tb 450WHERE tb.d >= SOME(SELECT e FROM t3 as tc 451WHERE t.b=tc.e))); 452a b 4531 10 4541 20 4554 40 456set @@optimizer_switch='materialization=off,semijoin=on'; 457EXPLAIN SELECT * FROM t1 AS t 458WHERE t.a NOT IN (SELECT a FROM t1 AS ta 459WHERE ta.a IN (SELECT c FROM t2 AS tb 460WHERE tb.d >= SOME(SELECT e FROM t3 as tc 461WHERE t.b=tc.e))); 462id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4631 PRIMARY t NULL ALL NULL NULL NULL NULL 7 100.00 Using where 4642 DEPENDENT SUBQUERY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where 4652 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where; FirstMatch(ta); Using join buffer (Block Nested Loop) 4664 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 467Warnings: 468Note 1276 Field or reference 'test.t.b' of SELECT #4 was resolved in SELECT #1 469Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t1` `t` where (not(<in_optimizer>(`test`.`t`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`tb`.`c` = `test`.`ta`.`a`) and (<cache>(`test`.`t`.`a`) = `test`.`ta`.`a`) and <nop>(<in_optimizer>(`test`.`tb`.`d`,<exists>(/* select#4 */ select 1 from `test`.`t3` `tc` where ((`test`.`t`.`b` = `test`.`tc`.`e`) and (<cache>(`test`.`tb`.`d`) >= `test`.`tc`.`e`)))))))))) 470SELECT * FROM t1 AS t 471WHERE t.a NOT IN (SELECT a FROM t1 AS ta 472WHERE ta.a IN (SELECT c FROM t2 AS tb 473WHERE tb.d >= SOME(SELECT e FROM t3 as tc 474WHERE t.b=tc.e))); 475a b 4761 10 4771 20 4784 40 479# EXISTS with reference to the parent query block 480set @@optimizer_switch='materialization=off,semijoin=off'; 481EXPLAIN SELECT * FROM t1 AS ta 482WHERE ta.a IN (SELECT c FROM t2 AS tb 483WHERE EXISTS (SELECT * FROM t3 as tc 484WHERE ta.b=tc.e)); 485id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4861 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 4872 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 4883 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 489Warnings: 490Note 1276 Field or reference 'test.ta.b' of SELECT #3 was resolved in SELECT #1 491Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where (exists(/* select#3 */ select 1 from `test`.`t3` `tc` where (`test`.`ta`.`b` = `test`.`tc`.`e`)) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 492SELECT * FROM t1 AS ta 493WHERE ta.a IN (SELECT c FROM t2 AS tb 494WHERE EXISTS (SELECT * FROM t3 as tc 495WHERE ta.b=tc.e)); 496a b 4972 10 4982 20 4993 20 5002 30 501set @@optimizer_switch='materialization=off,semijoin=on'; 502EXPLAIN SELECT * FROM t1 AS ta 503WHERE ta.a IN (SELECT c FROM t2 AS tb 504WHERE EXISTS (SELECT * FROM t3 as tc 505WHERE ta.b=tc.e)); 506id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5071 PRIMARY tb NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 5081 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 5093 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 510Warnings: 511Note 1276 Field or reference 'test.ta.b' of SELECT #3 was resolved in SELECT #1 512Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`ta`.`a` = `test`.`tb`.`c`) and exists(/* select#3 */ select 1 from `test`.`t3` `tc` where (`test`.`ta`.`b` = `test`.`tc`.`e`))) 513SELECT * FROM t1 AS ta 514WHERE ta.a IN (SELECT c FROM t2 AS tb 515WHERE EXISTS (SELECT * FROM t3 as tc 516WHERE ta.b=tc.e)); 517a b 5182 10 5192 20 5203 20 5212 30 522# Scalar subquery with reference to the parent query block 523set @@optimizer_switch='materialization=off,semijoin=off'; 524EXPLAIN SELECT * FROM t1 AS ta 525WHERE ta.a IN (SELECT c FROM t2 AS tb 526WHERE tb.d = (SELECT MIN(e) FROM t3 as tc 527WHERE ta.b=tc.e)); 528id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5291 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 5302 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 5313 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 532Warnings: 533Note 1276 Field or reference 'test.ta.b' of SELECT #3 was resolved in SELECT #1 534Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where ((`test`.`tb`.`d` = (/* select#3 */ select min(`test`.`tc`.`e`) from `test`.`t3` `tc` where (`test`.`ta`.`b` = `test`.`tc`.`e`))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 535SELECT * FROM t1 AS ta 536WHERE ta.a IN (SELECT c FROM t2 AS tb 537WHERE tb.d = (SELECT MIN(e) FROM t3 as tc 538WHERE ta.b=tc.e)); 539a b 5402 10 5412 20 5423 20 543set @@optimizer_switch='materialization=off,semijoin=on'; 544EXPLAIN SELECT * FROM t1 AS ta 545WHERE ta.a IN (SELECT c FROM t2 AS tb 546WHERE tb.d = (SELECT MIN(e) FROM t3 as tc 547WHERE ta.b=tc.e)); 548id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5491 PRIMARY tb NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 5501 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 5513 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 552Warnings: 553Note 1276 Field or reference 'test.ta.b' of SELECT #3 was resolved in SELECT #1 554Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`ta`.`a` = `test`.`tb`.`c`) and (`test`.`tb`.`d` = (/* select#3 */ select min(`test`.`tc`.`e`) from `test`.`t3` `tc` where (`test`.`ta`.`b` = `test`.`tc`.`e`)))) 555SELECT * FROM t1 AS ta 556WHERE ta.a IN (SELECT c FROM t2 AS tb 557WHERE tb.d = (SELECT MIN(e) FROM t3 as tc 558WHERE ta.b=tc.e)); 559a b 5602 10 5612 20 5623 20 563# Combine scalar subquery with quantified comparison subquery 564set @@optimizer_switch='materialization=off,semijoin=off'; 565EXPLAIN SELECT * FROM t1 AS ta 566WHERE ta.a IN (SELECT c FROM t2 AS tb 567WHERE (SELECT MIN(e) FROM t3 as tc 568WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc 569WHERE ta.b=tc.e)); 570id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5711 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 100.00 Using where 5722 DEPENDENT SUBQUERY tb NULL ALL NULL NULL NULL NULL 6 16.67 Using where 5734 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 5743 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 575Warnings: 576Note 1276 Field or reference 'test.ta.b' of SELECT #4 was resolved in SELECT #1 577Note 1276 Field or reference 'test.tb.d' of SELECT #3 was resolved in SELECT #2 578Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` where <in_optimizer>(`test`.`ta`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `tb` where (<nop>(<in_optimizer>((/* select#3 */ select min(`test`.`tc`.`e`) from `test`.`t3` `tc` where (`test`.`tb`.`d` = `test`.`tc`.`e`)),<exists>(/* select#4 */ select 1 from `test`.`t3` `tc` where ((`test`.`ta`.`b` = `test`.`tc`.`e`) and <if>(outer_field_is_not_null, (<cache>((/* select#3 */ select min(`test`.`tc`.`e`) from `test`.`t3` `tc` where (`test`.`tb`.`d` = `test`.`tc`.`e`))) < `test`.`tc`.`e`), true))))) and (<cache>(`test`.`ta`.`a`) = `test`.`tb`.`c`)))) 579SELECT * FROM t1 AS ta 580WHERE ta.a IN (SELECT c FROM t2 AS tb 581WHERE (SELECT MIN(e) FROM t3 as tc 582WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc 583WHERE ta.b=tc.e)); 584a b 5852 20 5862 30 587set @@optimizer_switch='materialization=off,semijoin=on'; 588EXPLAIN SELECT * FROM t1 AS ta 589WHERE ta.a IN (SELECT c FROM t2 AS tb 590WHERE (SELECT MIN(e) FROM t3 as tc 591WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc 592WHERE ta.b=tc.e)); 593id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5941 PRIMARY tb NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary 5951 PRIMARY ta NULL ALL NULL NULL NULL NULL 7 14.29 Using where; End temporary; Using join buffer (Block Nested Loop) 5964 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 5973 DEPENDENT SUBQUERY tc NULL ALL NULL NULL NULL NULL 4 25.00 Using where 598Warnings: 599Note 1276 Field or reference 'test.ta.b' of SELECT #4 was resolved in SELECT #1 600Note 1276 Field or reference 'test.tb.d' of SELECT #3 was resolved in SELECT #2 601Note 1003 /* select#1 */ select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b` from `test`.`t1` `ta` semi join (`test`.`t2` `tb`) where ((`test`.`ta`.`a` = `test`.`tb`.`c`) and <nop>(<in_optimizer>((/* select#3 */ select min(`test`.`tc`.`e`) from `test`.`t3` `tc` where (`test`.`tb`.`d` = `test`.`tc`.`e`)),<exists>(/* select#4 */ select 1 from `test`.`t3` `tc` where ((`test`.`ta`.`b` = `test`.`tc`.`e`) and <if>(outer_field_is_not_null, (<cache>((/* select#3 */ select min(`test`.`tc`.`e`) from `test`.`t3` `tc` where (`test`.`tb`.`d` = `test`.`tc`.`e`))) < `test`.`tc`.`e`), true)))))) 602SELECT * FROM t1 AS ta 603WHERE ta.a IN (SELECT c FROM t2 AS tb 604WHERE (SELECT MIN(e) FROM t3 as tc 605WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc 606WHERE ta.b=tc.e)); 607a b 6082 20 6092 30 610DROP TABLE t1, t2, t3, t4; 611set @@optimizer_switch='default'; 612# End of BUG#31480 613