1set @save_optimizer_switch_jcl6=@@optimizer_switch; 2set @@optimizer_switch='optimize_join_buffer_size=on'; 3set @@optimizer_switch='semijoin_with_cache=on'; 4set @@optimizer_switch='outer_join_with_cache=on'; 5set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 6set join_cache_level=6; 7show variables like 'join_cache_level'; 8Variable_name Value 9join_cache_level 6 10set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch; 11set @join_cache_level_for_subselect_sj2_test=@@join_cache_level; 12set @subselect_sj2_tmp= @@optimizer_switch; 13set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; 14set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 15SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off'); 16SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off'); 17set join_cache_level=@join_cache_level_for_subselect_sj2_test; 18drop table if exists t0, t1, t2, t3, t4, t5; 19drop view if exists v1; 20create table t0 (a int); 21insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 22create table t1 ( 23a int, 24b int 25); 26insert into t1 values (1,1),(1,1),(2,2); 27create table t2 ( 28a int, 29b int, 30key(b) 31); 32insert into t2 select a, a/2 from t0; 33insert into t2 select a+10, a+10/2 from t0; 34select * from t1; 35a b 361 1 371 1 382 2 39select * from t2; 40a b 410 0 421 1 432 1 443 2 454 2 465 3 476 3 487 4 498 4 509 5 5110 5 5211 6 5312 7 5413 8 5514 9 5615 10 5716 11 5817 12 5918 13 6019 14 61explain select * from t2 where b in (select a from t1); 62id select_type table type possible_keys key key_len ref rows Extra 631 PRIMARY t2 ALL b NULL NULL NULL 20 641 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 652 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 66select * from t2 where b in (select a from t1); 67a b 681 1 692 1 703 2 714 2 72create table t3 ( 73a int, 74b int, 75key(b), 76pk1 char(200), pk2 char(200), pk3 char(200), 77primary key(pk1, pk2, pk3) 78) engine=innodb; 79insert into t3 select a,a, a,a,a from t0; 80insert into t3 select a,a, a+100,a+100,a+100 from t0; 81explain select * from t3 where b in (select a from t1); 82id select_type table type possible_keys key key_len ref rows Extra 831 PRIMARY t3 ALL b NULL NULL NULL 20 841 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 852 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 86select * from t3 where b in (select a from t1); 87a b pk1 pk2 pk3 881 1 1 1 1 891 1 101 101 101 902 2 102 102 102 912 2 2 2 2 92set @save_max_heap_table_size= @@max_heap_table_size; 93set max_heap_table_size=16384; 94set @save_join_buffer_size = @@join_buffer_size; 95set join_buffer_size= 8192; 96drop table t3; 97create table t3 ( 98a int, 99b int, 100key(b), 101pk1 char(200), pk2 char(200), 102primary key(pk1, pk2) 103) engine=innodb; 104insert into t3 select 105A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a 106from t0 A, t0 B where B.a <5; 107explain select * from t3 where b in (select a from t0); 108id select_type table type possible_keys key key_len ref rows Extra 1091 PRIMARY t3 ALL b NULL NULL NULL # 1101 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # 1112 MATERIALIZED t0 ALL NULL NULL NULL NULL # 112select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); 113a b pk1 pk2 1140 0 0 0 1151 1 1 1 11610 10 10 10 11711 11 11 11 11812 12 12 12 11913 13 13 13 1202 2 2 2 1213 3 3 3 1224 4 4 4 1235 5 5 5 1246 6 6 6 1257 7 7 7 1268 8 8 8 1279 9 9 9 128set join_buffer_size= @save_join_buffer_size; 129set max_heap_table_size= @save_max_heap_table_size; 130explain select * from t1 where a in (select b from t2); 131id select_type table type possible_keys key key_len ref rows Extra 1321 PRIMARY t1 ALL NULL NULL NULL NULL 3 1331 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1342 MATERIALIZED t2 index b b 5 NULL 20 Using index 135select * from t1; 136a b 1371 1 1381 1 1392 2 140select * from t1 where a in (select b from t2); 141a b 1421 1 1431 1 1442 2 145drop table t1, t2, t3; 146set @save_join_buffer_size = @@join_buffer_size; 147set join_buffer_size= 8192; 148create table t1 (a int, filler1 binary(200), filler2 binary(200)); 149insert into t1 select a, 'filler123456', 'filler123456' from t0; 150insert into t1 select a+10, 'filler123456', 'filler123456' from t0; 151create table t2 as select * from t1; 152insert into t1 select a+20, 'filler123456', 'filler123456' from t0; 153insert into t1 values (2, 'duplicate ok', 'duplicate ok'); 154insert into t1 values (18, 'duplicate ok', 'duplicate ok'); 155insert into t2 values (3, 'duplicate ok', 'duplicate ok'); 156insert into t2 values (19, 'duplicate ok', 'duplicate ok'); 157explain select 158a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 159from t1 ot where a in (select a from t2 it); 160id select_type table type possible_keys key key_len ref rows Extra 1611 PRIMARY ot ALL NULL NULL NULL NULL 32 1621 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1632 MATERIALIZED it ALL NULL NULL NULL NULL 22 164select 165a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 166from t1 ot where a in (select a from t2 it); 167a mid(filler1, 1,10) Z 1680 filler1234 1 1691 filler1234 1 1702 filler1234 1 1713 filler1234 1 1724 filler1234 1 1735 filler1234 1 1746 filler1234 1 1757 filler1234 1 1768 filler1234 1 1779 filler1234 1 17810 filler1234 1 17911 filler1234 1 18012 filler1234 1 18113 filler1234 1 18214 filler1234 1 18315 filler1234 1 18416 filler1234 1 18517 filler1234 1 18618 filler1234 1 18719 filler1234 1 1882 duplicate 1 18918 duplicate 1 190explain select 191a, mid(filler1, 1,10), length(filler1)=length(filler2) 192from t2 ot where a in (select a from t1 it); 193id select_type table type possible_keys key key_len ref rows Extra 1941 PRIMARY ot ALL NULL NULL NULL NULL 22 1951 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1962 MATERIALIZED it ALL NULL NULL NULL NULL 32 197select 198a, mid(filler1, 1,10), length(filler1)=length(filler2) 199from t2 ot where a in (select a from t1 it); 200a mid(filler1, 1,10) length(filler1)=length(filler2) 2010 filler1234 1 2021 filler1234 1 2032 filler1234 1 2043 filler1234 1 2054 filler1234 1 2065 filler1234 1 2076 filler1234 1 2087 filler1234 1 2098 filler1234 1 2109 filler1234 1 21110 filler1234 1 21211 filler1234 1 21312 filler1234 1 21413 filler1234 1 21514 filler1234 1 21615 filler1234 1 21716 filler1234 1 21817 filler1234 1 21918 filler1234 1 22019 filler1234 1 2213 duplicate 1 22219 duplicate 1 223insert into t1 select a+20, 'filler123456', 'filler123456' from t0; 224insert into t1 select a+20, 'filler123456', 'filler123456' from t0; 225explain select 226a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 227from t1 ot where a in (select a from t2 it); 228id select_type table type possible_keys key key_len ref rows Extra 2291 PRIMARY ot ALL NULL NULL NULL NULL 52 2301 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2312 MATERIALIZED it ALL NULL NULL NULL NULL 22 232select 233a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 234from t1 ot where a in (select a from t2 it); 235a mid(filler1, 1,10) Z 2360 filler1234 1 2371 filler1234 1 2382 filler1234 1 2393 filler1234 1 2404 filler1234 1 2415 filler1234 1 2426 filler1234 1 2437 filler1234 1 2448 filler1234 1 2459 filler1234 1 24610 filler1234 1 24711 filler1234 1 24812 filler1234 1 24913 filler1234 1 25014 filler1234 1 25115 filler1234 1 25216 filler1234 1 25317 filler1234 1 25418 filler1234 1 25519 filler1234 1 2562 duplicate 1 25718 duplicate 1 258explain select 259a, mid(filler1, 1,10), length(filler1)=length(filler2) 260from t2 ot where a in (select a from t1 it); 261id select_type table type possible_keys key key_len ref rows Extra 2621 PRIMARY ot ALL NULL NULL NULL NULL 22 2631 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2642 MATERIALIZED it ALL NULL NULL NULL NULL 52 265select 266a, mid(filler1, 1,10), length(filler1)=length(filler2) 267from t2 ot where a in (select a from t1 it); 268a mid(filler1, 1,10) length(filler1)=length(filler2) 2690 filler1234 1 2701 filler1234 1 2712 filler1234 1 2723 filler1234 1 2734 filler1234 1 2745 filler1234 1 2756 filler1234 1 2767 filler1234 1 2778 filler1234 1 2789 filler1234 1 27910 filler1234 1 28011 filler1234 1 28112 filler1234 1 28213 filler1234 1 28314 filler1234 1 28415 filler1234 1 28516 filler1234 1 28617 filler1234 1 28718 filler1234 1 28819 filler1234 1 2893 duplicate 1 29019 duplicate 1 291drop table t1, t2; 292create table t1 (a int, b int, key(a)); 293create table t2 (a int, b int, key(a)); 294create table t3 (a int, b int, key(a)); 295insert into t1 select a,a from t0; 296insert into t2 select a,a from t0; 297insert into t3 select a,a from t0; 298t2 and t3 must be use 'ref', not 'ALL': 299explain select * 300from t0 where a in 301(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); 302id select_type table type possible_keys key key_len ref rows Extra 3031 PRIMARY t0 ALL NULL NULL NULL NULL 10 3041 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1 Using where 3052 MATERIALIZED t1 index a a 5 NULL 10 Using where; Using index 3062 MATERIALIZED t2 ref a a 5 test.t1.a 1 Using index 3072 MATERIALIZED t3 ref a a 5 test.t1.a 1 Using index 308drop table t0, t1,t2,t3; 309CREATE TABLE t1 ( 310ID int(11) NOT NULL auto_increment, 311Name char(35) NOT NULL default '', 312Country char(3) NOT NULL default '', 313Population int(11) NOT NULL default '0', 314PRIMARY KEY (ID), 315INDEX (Population), 316INDEX (Country) 317); 318CREATE TABLE t2 ( 319Code char(3) NOT NULL default '', 320Name char(52) NOT NULL default '', 321SurfaceArea float(10,2) NOT NULL default '0.00', 322Population int(11) NOT NULL default '0', 323Capital int(11) default NULL, 324PRIMARY KEY (Code), 325UNIQUE INDEX (Name), 326INDEX (Population) 327); 328CREATE TABLE t3 ( 329Country char(3) NOT NULL default '', 330Language char(30) NOT NULL default '', 331Percentage float(3,1) NOT NULL default '0.0', 332PRIMARY KEY (Country, Language), 333INDEX (Percentage) 334); 335set @bug35674_save_optimizer_switch=@@optimizer_switch; 336set optimizer_switch='materialization=off'; 337EXPLAIN 338SELECT Name FROM t2 339WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000) 340AND 341t2.Code IN (SELECT Country FROM t3 342WHERE Language='English' AND Percentage > 10 AND 343t2.Population > 100000); 344id select_type table type possible_keys key key_len ref rows Extra 3451 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Rowid-ordered scan; Start temporary 3461 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 3471 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 348set optimizer_switch=@bug35674_save_optimizer_switch; 349DROP TABLE t1,t2,t3; 350CREATE TABLE t1 ( 351Code char(3) NOT NULL DEFAULT '', 352Name char(52) NOT NULL DEFAULT '', 353Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', 354Region char(26) NOT NULL DEFAULT '', 355SurfaceArea float(10,2) NOT NULL DEFAULT '0.00', 356IndepYear smallint(6) DEFAULT NULL, 357Population int(11) NOT NULL DEFAULT '0', 358LifeExpectancy float(3,1) DEFAULT NULL, 359GNP float(10,2) DEFAULT NULL, 360GNPOld float(10,2) DEFAULT NULL, 361LocalName char(45) NOT NULL DEFAULT '', 362GovernmentForm char(45) NOT NULL DEFAULT '', 363HeadOfState char(60) DEFAULT NULL, 364Capital int(11) DEFAULT NULL, 365Code2 char(2) NOT NULL DEFAULT '', 366PRIMARY KEY (Code) 367); 368CREATE TABLE t2 ( 369ID int(11) NOT NULL AUTO_INCREMENT, 370Name char(35) NOT NULL DEFAULT '', 371CountryCode char(3) NOT NULL DEFAULT '', 372District char(20) NOT NULL DEFAULT '', 373Population int(11) NOT NULL DEFAULT '0', 374PRIMARY KEY (ID), 375KEY CountryCode (CountryCode) 376); 377Fill the table with test data 378This must not use LooseScan: 379EXPLAIN SELECT Name FROM t1 380WHERE t1.Code IN ( 381SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); 382id select_type table type possible_keys key key_len ref rows Extra 3831 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 3841 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 3852 MATERIALIZED t2 ALL CountryCode NULL NULL NULL 545 Using where 386SELECT Name FROM t1 387WHERE t1.Code IN ( 388SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); 389Name 390Austria 391Canada 392China 393Czech Republic 394drop table t1, t2; 395drop procedure if exists p1; 396drop procedure if exists p2; 397drop procedure if exists p3; 398drop procedure if exists p4; 399CREATE TABLE t1(a INT); 400CREATE TABLE t2(c INT); 401CREATE PROCEDURE p1(v1 int) 402BEGIN 403SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2); 404END 405// 406CREATE PROCEDURE p2(v1 int) 407BEGIN 408SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2); 409END 410// 411CREATE PROCEDURE p3(v1 int) 412BEGIN 413SELECT 1 414FROM 415t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, 416t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, 417t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, 418t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, 419t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, 420t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, 421t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, 422t1 t57,t1 t58,t1 t59,t1 t60 423WHERE t01.a IN (SELECT c FROM t2); 424END 425// 426CREATE PROCEDURE p4(v1 int) 427BEGIN 428SELECT 1 429FROM 430t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, 431t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, 432t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, 433t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, 434t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, 435t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, 436t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, 437t1 t57,t1 t58,t1 t59,t1 t60 438WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2); 439END 440// 441CALL p1(1); 4421 443CALL p2(1); 4441 445CALL p3(1); 4461 447CALL p4(1); 4481 449DROP TABLE t1, t2; 450DROP PROCEDURE p1; 451DROP PROCEDURE p2; 452DROP PROCEDURE p3; 453DROP PROCEDURE p4; 454create table t0 (a int); 455insert into t0 values (0),(1),(2),(3),(4); 456create table t1 (a int, b int, key(a)); 457insert into t1 select a,a from t0; 458insert into t1 select a+5,a from t0; 459create table t2 (a int, b int, primary key(a)); 460insert into t2 select * from t1; 461Table t2, unlike table t1, should be displayed as pulled out 462explain extended select * from t0 463where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and 464t1.b=t2.b); 465id select_type table type possible_keys key key_len ref rows filtered Extra 4661 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where 4671 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 4681 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2); Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 469Warnings: 470Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 471Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where `test`.`t2`.`a` = `test`.`t0`.`a` and `test`.`t1`.`a` = `test`.`t0`.`a` and `test`.`t1`.`b` = `test`.`t2`.`b` 472update t1 set a=3, b=11 where a=4; 473update t2 set b=11 where a=3; 474# Not anymore: 475# The following query gives wrong result due to Bug#49129 476select * from t0 where t0.a in 477(select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b); 478a 4790 4801 4812 4823 483drop table t0, t1, t2; 484CREATE TABLE t1 ( 485id int(11) NOT NULL, 486PRIMARY KEY (id)); 487CREATE TABLE t2 ( 488id int(11) NOT NULL, 489fid int(11) NOT NULL, 490PRIMARY KEY (id)); 491insert into t1 values(1); 492insert into t2 values(1,7503),(2,1); 493explain select count(*) 494from t1 495where fid IN (select fid from t2 where (id between 7502 and 8420) order by fid ); 496ERROR 42S22: Unknown column 'fid' in 'IN/ALL/ANY subquery' 497drop table t1, t2; 498create table t1 (a int, b int, key (a), key (b)); 499insert into t1 values (2,4),(2,4),(2,4); 500select t1.a from t1 501where 502t1.a in (select 1 from t1 where t1.a in (select 1 from t1) group by t1.a); 503a 504drop table t1; 505create table t1(a int,b int,key(a),key(b)); 506insert into t1 values (1,1),(2,2),(3,3); 507select 1 from t1 508where t1.a not in (select 1 from t1 509where t1.a in (select 1 from t1) 510group by t1.b); 5111 5121 5131 514drop table t1; 515CREATE TABLE t1 516(EMPNUM CHAR(3) NOT NULL, 517EMPNAME CHAR(20), 518GRADE DECIMAL(4), 519CITY CHAR(15)); 520CREATE TABLE t2 521(PNUM CHAR(3) NOT NULL, 522PNAME CHAR(20), 523PTYPE CHAR(6), 524BUDGET DECIMAL(9), 525CITY CHAR(15)); 526CREATE TABLE t3 527(EMPNUM CHAR(3) NOT NULL, 528PNUM CHAR(3) NOT NULL, 529HOURS DECIMAL(5)); 530INSERT INTO t1 VALUES ('E1','Alice',12,'Deale'); 531INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna'); 532INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna'); 533INSERT INTO t1 VALUES ('E4','Don',12,'Deale'); 534INSERT INTO t1 VALUES ('E5','Ed',13,'Akron'); 535INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale'); 536INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna'); 537INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa'); 538INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale'); 539INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna'); 540INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale'); 541INSERT INTO t3 VALUES ('E1','P1',40); 542INSERT INTO t3 VALUES ('E1','P2',20); 543INSERT INTO t3 VALUES ('E1','P3',80); 544INSERT INTO t3 VALUES ('E1','P4',20); 545INSERT INTO t3 VALUES ('E1','P5',12); 546INSERT INTO t3 VALUES ('E1','P6',12); 547INSERT INTO t3 VALUES ('E2','P1',40); 548INSERT INTO t3 VALUES ('E2','P2',80); 549INSERT INTO t3 VALUES ('E3','P2',20); 550INSERT INTO t3 VALUES ('E4','P2',20); 551INSERT INTO t3 VALUES ('E4','P4',40); 552INSERT INTO t3 VALUES ('E4','P5',80); 553SELECT * FROM t1; 554EMPNUM EMPNAME GRADE CITY 555E1 Alice 12 Deale 556E2 Betty 10 Vienna 557E3 Carmen 13 Vienna 558E4 Don 12 Deale 559E5 Ed 13 Akron 560CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM); 561SELECT EMPNAME 562FROM t1 563WHERE EMPNUM IN 564(SELECT EMPNUM 565FROM t3 566WHERE PNUM IN 567(SELECT PNUM 568FROM t2 569WHERE PTYPE = 'Design')); 570EMPNAME 571Alice 572Betty 573Don 574DROP INDEX t1_IDX ON t1; 575CREATE INDEX t1_IDX ON t1(EMPNUM); 576SELECT EMPNAME 577FROM t1 578WHERE EMPNUM IN 579(SELECT EMPNUM 580FROM t3 581WHERE PNUM IN 582(SELECT PNUM 583FROM t2 584WHERE PTYPE = 'Design')); 585EMPNAME 586Alice 587Betty 588Don 589DROP INDEX t1_IDX ON t1; 590SELECT EMPNAME 591FROM t1 592WHERE EMPNUM IN 593(SELECT EMPNUM 594FROM t3 595WHERE PNUM IN 596(SELECT PNUM 597FROM t2 598WHERE PTYPE = 'Design')); 599EMPNAME 600Alice 601Betty 602Don 603DROP TABLE t1, t2, t3; 604CREATE TABLE t1 (f1 INT NOT NULL); 605CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1; 606SELECT * FROM v1; 607a 608drop view v1; 609drop table t1; 610create table t0 (a int); 611insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 612create table t1(a int, b int); 613insert into t1 values (0,0),(1,1),(2,2); 614create table t2 as select * from t1; 615create table t3 (pk int, a int, primary key(pk)); 616insert into t3 select a,a from t0; 617explain 618select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); 619id select_type table type possible_keys key key_len ref rows Extra 6201 PRIMARY t1 ALL NULL NULL NULL NULL 3 6211 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) 6222 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index 623drop table t0, t1, t2, t3; 624create table t1 (a int); 625insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 626create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb; 627insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1; 628insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1; 629alter table t2 add filler1 int; 630insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; 631set @save_join_buffer_size=@@join_buffer_size; 632set join_buffer_size=1; 633select * from t2 where filler1 in ( select a from t1); 634a b c filler1 635set join_buffer_size=@save_join_buffer_size; 636drop table t1, t2; 637create table t1 (a int not null); 638drop procedure if exists p1; 639CREATE PROCEDURE p1() 640BEGIN 641DECLARE EXIT HANDLER FOR SQLEXCEPTION select a from t1; 642prepare s1 from ' 643 select a from t1 where a in ( 644 select a from t1 where a in ( 645 select a from t1 where a in ( 646 select a from t1 where a in ( 647 select a from t1 where a in ( 648 select a from t1 where a in ( 649 select a from t1 where a in ( 650 select a from t1 where a in ( 651 select a from t1 where a in ( 652 select a from t1 where a in ( 653 select a from t1 where a in ( 654 select a from t1 where a in ( 655 select a from t1 where a in ( 656 select a from t1 where a in ( 657 select a from t1 where a in ( 658 select a from t1 where a in ( 659 select a from t1 where a in ( 660 select a from t1 where a in ( 661 select a from t1 where a in ( 662 select a from t1 where a in ( 663 select a from t1 where a in ( 664 select a from t1 where a in ( 665 select a from t1 where a in ( 666 select a from t1 where a in ( 667 select a from t1 where a in ( 668 select a from t1 where a in ( 669 select a from t1 where a in ( 670 select a from t1 where a in ( 671 select a from t1 where a in ( 672 select a from t1 where a in ( 673 select a from t1 where a in ( 674 select a from t1 where a in ( 675 select a from t1 where a in ( 676 select a from t1 where a in ( 677 select a from t1 where a in ( 678 select a from t1 where a in ( 679 select a from t1 where a in ( 680 select a from t1 where a in ( 681 select a from t1 where a in ( 682 select a from t1 where a in ( 683 select a from t1 where a in ( 684 select a from t1 where a in ( 685 select a from t1 where a in ( 686 select a from t1 where a in ( 687 select a from t1 where a in ( 688 select a from t1 where a in ( 689 select a from t1 where a in ( 690 select a from t1 where a in ( 691 select a from t1 where a in ( 692 select a from t1 where a in ( 693 select a from t1 where a in ( 694 select a from t1 where a in ( 695 select a from t1 where a in ( 696 select a from t1 where a in ( 697 select a from t1 where a in ( 698 select a from t1 where a in ( 699 select a from t1 where a in ( 700 select a from t1 where a in ( 701 select a from t1 where a in ( 702 select a from t1 where a in ( 703 select a from t1 where a in ( 704 select a from t1 where a in ( select a from t1) 705 )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))'; 706execute s1; 707END; 708| 709call p1(); 710a 711drop procedure p1; 712drop table t1; 713create table t0 (a int); 714insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 715create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C; 716create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1; 717show create table t2; 718Table Create Table 719t2 CREATE TABLE `t2` ( 720 `id` int(11) NOT NULL, 721 `a` int(11) DEFAULT NULL, 722 PRIMARY KEY (`id`), 723 KEY `a` (`a`) 724) ENGINE=MyISAM DEFAULT CHARSET=latin1 725set @a=0; 726create table t3 as select * from t2 limit 0; 727insert into t3 select @a:=@a+1, t2.a from t2, t0; 728insert into t3 select @a:=@a+1, t2.a from t2, t0; 729insert into t3 select @a:=@a+1, t2.a from t2, t0; 730alter table t3 add primary key(id), add key(a); 731The following must use loose index scan over t3, key a: 732explain select count(a) from t2 where a in ( SELECT a FROM t3); 733id select_type table type possible_keys key key_len ref rows Extra 7341 PRIMARY t2 index a a 5 NULL 1000 Using index 7351 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 7362 MATERIALIZED t3 index a a 5 NULL 30000 Using index 737select count(a) from t2 where a in ( SELECT a FROM t3); 738count(a) 7391000 740drop table t0,t1,t2,t3; 741 742BUG#42740: crash in optimize_semijoin_nests 743 744create table t1 (c6 timestamp,key (c6)) engine=innodb; 745create table t2 (c2 double) engine=innodb; 746explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null) ; 747id select_type table type possible_keys key key_len ref rows Extra 7481 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 749drop table t1, t2; 750# 751# BUG#42742: crash in setup_sj_materialization, Copy_field::set 752# 753create table t3 ( c1 year) engine=innodb; 754insert into t3 values (2135),(2142); 755create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb; 756# The following must not crash, EXPLAIN should show one SJ strategy, not a mix: 757explain select 1 from t2 where 758c2 in (select 1 from t3, t2) and 759c1 in (select convert(c6,char(1)) from t2); 760id select_type table type possible_keys key key_len ref rows Extra 7611 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 7621 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 7631 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 7641 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)); Using join buffer (incremental, BNL join) 7653 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 766drop table t2, t3; 767# 768# BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 769# 770CREATE TABLE t1 ( f1 int NOT NULL , f10 int) ; 771INSERT IGNORE INTO t1 VALUES (25,0),(29,0); 772CREATE TABLE t2 ( f10 int) ENGINE=InnoDB; 773CREATE TABLE t3 ( f11 int) ; 774INSERT IGNORE INTO t3 VALUES (0); 775SELECT alias1.f10 AS field2 776FROM t2 AS alias1 777JOIN ( 778t3 AS alias2 779JOIN t1 AS alias3 780ON alias3.f10 781) ON alias3.f1 782WHERE alias2.f11 IN ( 783SELECT SQ4_alias1.f10 784FROM t1 AS SQ4_alias1 785LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 786) 787GROUP BY field2; 788field2 789drop table t1, t2, t3; 790# 791# BUG#849763: Wrong result with second execution of prepared statement with semijoin + view 792# 793CREATE TABLE t1 ( c varchar(1)) engine=innodb; 794INSERT INTO t1 VALUES ('r'); 795CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb; 796INSERT INTO t2 VALUES (1,'r','r'); 797CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1; 798PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)'; 799EXECUTE st1; 800a b c 8011 r r 802EXECUTE st1; 803a b c 8041 r r 805DROP VIEW v1; 806DROP TABLE t1, t2; 807# 808# BUG#858732: Wrong result with semijoin + loosescan + comma join 809# 810CREATE TABLE t1 (f13 int(11) NOT NULL , PRIMARY KEY (f13)) ENGINE=InnoDB; 811INSERT INTO t1 VALUES (16),(24); 812CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) ENGINE=InnoDB; 813INSERT INTO t2 VALUES (6,'y'); 814CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; 815INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); 816# The following must use LooseScan but not join buffering 817explain 818SELECT * FROM t3 819WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); 820id select_type table type possible_keys key key_len ref rows Extra 8211 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 Using index 8221 PRIMARY alias2 index f12 f12 7 NULL 1 Using index; LooseScan 8231 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; FirstMatch(alias2) 8241 PRIMARY t3 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (flat, BNL join) 825SELECT * FROM t3 826WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); 827f12 828y 829DROP TABLE t1,t2,t3; 830# 831# BUG#869012: Wrong result with semijoin + materialization + AND in WHERE 832# 833CREATE TABLE t1 (f3 varchar(1) , f4 varchar(1) ) engine=InnoDB; 834INSERT IGNORE INTO t1 VALUES ('x','x'),('x','x'); 835CREATE TABLE t2 ( f4 varchar(1) ) ; 836INSERT IGNORE INTO t2 VALUES ('g'); 837CREATE TABLE t3 (f4 varchar(1) ) Engine=InnoDB; 838INSERT IGNORE INTO t3 VALUES ('x'); 839set @tmp_869012=@@optimizer_switch; 840SET optimizer_switch='semijoin=on,materialization=on'; 841SELECT * 842FROM t1 , t2 843WHERE ( t1.f4 ) IN ( SELECT f4 FROM t3 ) 844AND t2.f4 != t1.f3 ; 845f3 f4 f4 846x x g 847x x g 848set optimizer_switch= @tmp_869012; 849DROP TABLE t1,t2,t3; 850# 851# BUG#869001: Wrong result with semijoin + materialization + firstmatch + multipart key 852# 853set @tmp869001_jcl= @@join_cache_level; 854set @tmp869001_os= @@optimizer_switch; 855SET join_cache_level=0; 856SET optimizer_switch='materialization=on,semijoin=on,firstmatch=on,loosescan=off'; 857CREATE TABLE t1 ( f2 int, f3 varchar(1), KEY (f3,f2)) engine=innodb; 858INSERT INTO t1 VALUES (8,'x'),(NULL,'x'),(8,'c'); 859CREATE TABLE t2 ( f4 varchar(1)) engine=innodb; 860INSERT INTO t2 VALUES ('x'); 861CREATE TABLE t3 ( f1 int) engine=innodb; 862INSERT INTO t3 VALUES (8),(6),(2),(9),(6); 863CREATE TABLE t4 ( f3 varchar(1)) engine=innodb; 864INSERT INTO t4 VALUES ('p'),('j'),('c'); 865SELECT * 866FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) 867WHERE ( 8 ) IN ( 868SELECT t3.f1 FROM t3 , t4 869); 870f2 f3 f4 871NULL x x 8728 x x 873DROP TABLE t1, t2, t3, t4; 874set join_cache_level= @tmp869001_jcl; 875set optimizer_switch= @tmp869001_os; 876# 877# Bug #881318: join cache + duplicate elimination + left join 878# with empty materialized derived inner table 879# 880CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB; 881CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB; 882INSERT INTO t2 VALUES ('a'); 883CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB; 884INSERT INTO t3 VALUES ('c','c'); 885CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB; 886INSERT INTO t4 VALUES ('c'), ('b'); 887CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 888EXPLAIN 889SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a 890WHERE t3.b IN (SELECT b FROM t4); 891id select_type table type possible_keys key key_len ref rows Extra 8921 PRIMARY t3 ALL NULL NULL NULL NULL 1 8931 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 8941 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t3.a 1 Using where; Using join buffer (flat, BNLH join) 8951 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) 8962 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 8973 DERIVED t1 ALL NULL NULL NULL NULL 1 898SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a 899WHERE t3.b IN (SELECT b FROM t4); 900a b b a 901c c NULL NULL 902DROP VIEW v1; 903DROP TABLE t1,t2,t3,t4; 904# 905# BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ... 906# 907CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) ); 908INSERT INTO t1 VALUES (1),(2),(3),(4); 909CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB; 910INSERT INTO t2 VALUES (1,1); 911SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b); 912a b c 9131 1 1 9142 1 1 9153 1 1 9164 1 1 917DROP TABLE t1,t2; 918# 919# BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() 920# with index_merge+index_merge_sort_union+loosescan+semijoin 921# 922CREATE TABLE t1 ( 923a INT, b VARCHAR(1), c INT, 924KEY(a), KEY(b) 925) ENGINE=InnoDB; 926INSERT INTO t1 VALUES 927(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5), 928(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9), 929(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), 930(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); 931CREATE TABLE t2 ( 932pk INT, d VARCHAR(1), e INT, 933PRIMARY KEY(pk), KEY(d,e) 934) ENGINE=InnoDB; 935INSERT INTO t2 VALUES 936(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), 937(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), 938(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), 939(15,'g',6),(16,'x',7),(17,'f',8); 940explain 941SELECT * FROM t1 WHERE b IN ( 942SELECT d FROM t2, t1 943WHERE a = d AND ( pk < 2 OR d = 'z' ) 944); 945id select_type table type possible_keys key key_len ref rows Extra 9461 PRIMARY t1 ALL b NULL NULL NULL 19 9471 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 9482 MATERIALIZED t2 index PRIMARY,d d 9 NULL 17 Using where; Using index 9492 MATERIALIZED t1 ref a a 5 test.t2.d 1 Using where; Using index 950SELECT * FROM t1 WHERE b IN ( 951SELECT d FROM t2, t1 952WHERE a = d AND ( pk < 2 OR d = 'z' ) 953); 954a b c 955Warnings: 956Warning 1292 Truncated incorrect INTEGER value: 'x' 957DROP TABLE t1, t2; 958# 959# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view 960# 961CREATE TABLE t1 ( 962a VARCHAR(1), 963b VARCHAR(1) NOT NULL, 964KEY(a) 965) ENGINE=InnoDB; 966INSERT INTO t1 VALUES 967('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), 968('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), 969('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), 970('q','q'),('w','w'),('d','d'),('e','e'); 971CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 972# This query returned 6 rows instead of 19 973SELECT * FROM v1 974WHERE ( a, a ) IN ( 975SELECT alias2.b, alias2.a 976FROM t1 AS alias1, t1 AS alias2 977WHERE alias2.b = alias1.a 978AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) 979); 980a b 981b b 982c c 983d d 984d d 985d d 986e e 987g g 988j j 989m m 990m m 991p p 992q q 993r r 994s s 995t t 996v v 997w w 998x x 999y y 1000# Another testcase, without the VIEW: 1001CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB; 1002INSERT INTO t2 SELECT * FROM t1; 1003INSERT INTO t2 SELECT * FROM t1; 1004EXPLAIN 1005SELECT * FROM t2 1006WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 1007WHERE 1008alias2.b = alias1.a AND 1009(alias1.b >= alias1.a OR alias2.b = 'z')); 1010id select_type table type possible_keys key key_len ref rows Extra 10111 PRIMARY t2 ALL a NULL NULL NULL 38 10121 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 10132 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 10142 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1015SELECT * FROM t2 1016WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 1017WHERE 1018alias2.b = alias1.a AND 1019(alias1.b >= alias1.a OR alias2.b = 'z')); 1020a b 1021b b 1022b b 1023c c 1024c c 1025d d 1026d d 1027d d 1028d d 1029d d 1030d d 1031e e 1032e e 1033g g 1034g g 1035j j 1036j j 1037m m 1038m m 1039m m 1040m m 1041p p 1042p p 1043q q 1044q q 1045r r 1046r r 1047s s 1048s s 1049t t 1050t t 1051v v 1052v v 1053w w 1054w w 1055x x 1056x x 1057y y 1058y y 1059DROP VIEW v1; 1060DROP TABLE t1, t2; 1061# 1062# BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR 1063# (this is a regression caused by the fix for BUG#951937) 1064CREATE TABLE t1 ( a INT, b INT, c INT, d INT ); 1065INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8); 1066SELECT * FROM t1 1067WHERE a = d AND ( b = 50 AND b = d OR a = c ); 1068a b c d 1069DROP TABLE t1; 1070# 1071# BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery 1072# 1073set @tmp_951283=@@optimizer_prune_level; 1074SET optimizer_prune_level=0; 1075CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; 1076INSERT INTO t1 VALUES 1077(10),(11),(12),(13),(14),(15),(16), 1078(17),(18),(19),(20),(21),(22),(23); 1079CREATE TABLE t2 ( 1080b INT PRIMARY KEY, 1081c VARCHAR(1), 1082d VARCHAR(1), 1083KEY(c) 1084) ENGINE=InnoDB; 1085INSERT INTO t2 VALUES 1086(1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'), 1087(5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'), 1088(9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), 1089(13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), 1090(17,'q','q'),(18,'w','w'),(19,'d','d'); 1091EXPLAIN 1092SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1093WHERE alias3.d IN ( 1094SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1095WHERE alias5.b = alias4.b 1096AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) 1097); 1098id select_type table type possible_keys key key_len ref rows Extra 10991 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where 11001 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index 11011 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3) 11021 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) 11031 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (incremental, BNL join) 1104SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1105WHERE alias3.d IN ( 1106SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1107WHERE alias5.b = alias4.b 1108AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) 1109); 1110COUNT(*) 11113724 1112EXPLAIN 1113SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1114WHERE alias3.d IN ( 1115SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1116WHERE alias5.b = alias4.b 1117AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) 1118); 1119id select_type table type possible_keys key key_len ref rows Extra 11201 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 Using where 11211 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 Using index 11221 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 Using where; FirstMatch(alias3) 11231 PRIMARY alias2 ALL NULL NULL NULL NULL 14 Using join buffer (flat, BNL join) 11241 PRIMARY alias1 ALL NULL NULL NULL NULL 14 Using join buffer (incremental, BNL join) 1125SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1126WHERE alias3.d IN ( 1127SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1128WHERE alias5.b = alias4.b 1129AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) 1130); 1131COUNT(*) 11323724 1133set optimizer_prune_level=@tmp_951283; 1134DROP TABLE t1,t2; 1135# 1136# Bug mdev-5135: crash on semijoin with nested outer joins 1137# 1138CREATE TABLE t1 (i1 int) ENGINE=MyISAM; 1139INSERT INTO t1 VALUES (1),(2); 1140CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM; 1141CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM; 1142INSERT INTO t3 VALUES (3,'x'),(4,'y'); 1143SELECT * FROM t1 WHERE ( 1, 1 ) IN ( 1144SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( 1145t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) 1146) ON ( t3a.c = t3b.c ) 1147); 1148i1 1149DROP TABLE t1,t2,t3; 1150# 1151# MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin 1152# 1153CREATE TABLE t1 (a INT) engine=innodb; 1154INSERT INTO t1 VALUES (8),(9); 1155CREATE TABLE t2 (b INT) engine=innodb; 1156INSERT INTO t2 VALUES (2),(3); 1157CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb; 1158INSERT INTO t2 VALUES (4),(5); 1159explain 1160SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); 1161id select_type table type possible_keys key key_len ref rows Extra 11621 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 11633 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL No matching min/max row 1164SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); 1165a 1166DROP TABLE t1,t2,t3; 1167DROP TABLE IF EXISTS t1,t2,t3,t4; 1168# 1169# MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin 1170# 1171CREATE TABLE t1 ( t1_pk1 varchar(3), t1_pk2 varchar(52), PRIMARY KEY (t1_pk1,t1_pk2)) ENGINE=InnoDB; 1172INSERT INTO t1 VALUES ('CHN','Chinese'),('USA','English'); 1173CREATE TABLE t2 ( t2_i int(11), t2_c varchar(52)) ENGINE=InnoDB; 1174INSERT INTO t2 VALUES (86,'English'); 1175CREATE TABLE t3 ( t3_i int(11), t3_c varchar(35)) ENGINE=InnoDB; 1176INSERT INTO t3 VALUES (3989,'Abilene'),(3873,'Akron'); 1177create table t4 like t1; 1178insert into t4 select * from t1; 1179SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); 1180t1_pk1 t1_pk2 t3_i t3_c 1181explain 1182SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); 1183id select_type table type possible_keys key key_len ref rows Extra 11841 PRIMARY t1 ref PRIMARY PRIMARY 5 const 1 Using where; Using index 11851 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary; Using join buffer (flat, BNL join) 11861 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) 11871 PRIMARY t4 hash_index NULL #hash#$hj:PRIMARY 54:59 test.t3.t3_c 2 Using where; Using index; End temporary; Using join buffer (incremental, BNLH join) 1188DROP TABLE t1,t2,t3,t4; 1189# 1190# MDEV-6263: Wrong result when using IN subquery with order by 1191# 1192CREATE TABLE t1 ( 1193id int(11) NOT NULL, 1194nombre varchar(255) NOT NULL, 1195PRIMARY KEY (id) 1196) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1197INSERT INTO t1 (id, nombre) VALUES 1198(1, 'row 1'),(2, 'row 2'),(3, 'row 3'), 1199(4, 'row 4'),(5, 'row 5'),(6, 'row 6'); 1200CREATE TABLE t2 ( 1201id_algo int(11) NOT NULL, 1202id_agente int(11) NOT NULL, 1203PRIMARY KEY (id_algo,id_agente), 1204KEY another_data (id_agente) 1205) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1206INSERT INTO t2 (id_algo, id_agente) VALUES 1207(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1); 1208SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC; 1209id nombre 12101 row 1 12112 row 2 12123 row 3 1213SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); 1214id nombre 12151 row 1 12162 row 2 12173 row 3 1218DROP TABLE t1, t2; 1219# 1220# MDEV-7474: Semi-Join's DuplicateWeedout strategy skipped for some values of optimizer_search_depth 1221# 1222CREATE TABLE t1 ( 1223t1id BIGINT(20) NOT NULL, 1224code VARCHAR(20), 1225PRIMARY KEY (t1id) 1226) COLLATE='utf8mb4_bin' ENGINE=InnoDB; 1227CREATE TABLE t2 ( 1228t2id BIGINT(20) NOT NULL, 1229t1idref BIGINT(20) NOT NULL, 1230code VARCHAR(20), 1231PRIMARY KEY (t2id), 1232INDEX FK_T2_T1Id (t1idref), 1233CONSTRAINT FK_T2_T1Id FOREIGN KEY (t1idref) REFERENCES t1 (t1id) 1234) COLLATE='utf8mb4_bin' ENGINE=InnoDB; 1235CREATE TABLE t3 ( 1236t3idref BIGINT(20) NOT NULL, 1237t2idref BIGINT(20) NOT NULL, 1238sequencenumber INT(10) NOT NULL, 1239PRIMARY KEY (t3idref, t2idref), 1240INDEX FK_T3_T2Id (t2idref), 1241CONSTRAINT FK_T3_T2Id FOREIGN KEY (t2idref) REFERENCES t2 (t2id) 1242) COLLATE='utf8mb4_bin' ENGINE=InnoDB; 1243INSERT INTO t1 (t1id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030), 1244(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042), 1245(100043),(100044),(100045),(100046),(100047); 1246INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; 1247INSERT IGNORE INTO t1 VALUES (200001, 'a'); 1248INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); 1249INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); 1250set @tmp7474= @@optimizer_search_depth; 1251SET SESSION optimizer_search_depth = 1; 1252SELECT SQL_NO_CACHE 1253T2_0_.t1idref, 1254T2_0_.t2id 1255FROM 1256t2 T2_0_ 1257WHERE 1258T2_0_.t1idref IN ( 1259SELECT 1260T1_1_.t1id 1261FROM 1262t3 T3_0_ 1263INNER JOIN 1264t2 T2_1_ 1265ON T3_0_.t2idref=T2_1_.t2id 1266INNER JOIN 1267t1 T1_1_ 1268ON T2_1_.t1idref=T1_1_.t1id 1269WHERE 1270T3_0_.t3idref= 1 1271); 1272t1idref t2id 1273200001 200011 1274200001 200012 1275200001 200013 1276explain SELECT SQL_NO_CACHE 1277T2_0_.t1idref, 1278T2_0_.t2id 1279FROM 1280t2 T2_0_ 1281WHERE 1282T2_0_.t1idref IN ( 1283SELECT 1284T1_1_.t1id 1285FROM 1286t3 T3_0_ 1287INNER JOIN 1288t2 T2_1_ 1289ON T3_0_.t2idref=T2_1_.t2id 1290INNER JOIN 1291t1 T1_1_ 1292ON T2_1_.t1idref=T1_1_.t1id 1293WHERE 1294T3_0_.t3idref= 1 1295); 1296id select_type table type possible_keys key key_len ref rows Extra 12971 PRIMARY T3_0_ ref PRIMARY,FK_T3_T2Id PRIMARY 8 const 3 Using index; Start temporary 12981 PRIMARY T2_1_ eq_ref PRIMARY,FK_T2_T1Id PRIMARY 8 test.T3_0_.t2idref 1 Using join buffer (flat, BKA join); Key-ordered scan 12991 PRIMARY T1_1_ eq_ref PRIMARY PRIMARY 8 test.T2_1_.t1idref 1 Using index 13001 PRIMARY T2_0_ ref FK_T2_T1Id FK_T2_T1Id 8 test.T2_1_.t1idref 1 Using index; End temporary 1301drop table t3,t2,t1; 1302set optimizer_search_depth=@tmp7474; 1303# 1304# 1305# 1306CREATE TABLE t1 ( 1307id int(16) NOT NULL AUTO_INCREMENT, 1308PRIMARY KEY (id) 1309) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1310CREATE TABLE t2 ( 1311id int(16) NOT NULL AUTO_INCREMENT, 1312t3_id int(16) NOT NULL DEFAULT '0', 1313t1_id int(16) NOT NULL DEFAULT '0', 1314PRIMARY KEY (id), 1315KEY t3_idx (t3_id), 1316KEY t1_idx (t1_id) 1317) ENGINE=MyISAM DEFAULT CHARSET=utf8; 1318CREATE TABLE t3 ( 1319id int(16) NOT NULL AUTO_INCREMENT, 1320PRIMARY KEY (id) 1321) ENGINE=MyISAM DEFAULT CHARSET=utf8; 1322INSERT INTO t3 VALUES (1); 1323INSERT INTO t2 VALUES (1, 1, 1); 1324INSERT INTO t2 VALUES (2, 1, 2); 1325INSERT INTO t2 VALUES (3, 1, 2); 1326INSERT INTO t2 VALUES (4, 1, 1); 1327INSERT INTO t1 VALUES (1); 1328INSERT INTO t1 VALUES (2); 1329SELECT * FROM t1 WHERE t1.id IN ( 1330SELECT t2.t1_id FROM t3 JOIN t2 ON t3.id = t2.t3_id WHERE t3.id = 1 1331); 1332id 13331 13342 1335drop table t1,t2,t3; 1336# 1337# MDEV-11108: Assertion `uniq_tuple_length_arg <= table->file->max_key_length()' failed in SJ_TMP_TABLE::create_sj_weedout_tmp_table 1338# 1339CREATE TABLE t1 (a INT) ENGINE=InnoDB; 1340CREATE TABLE t2 (pk BLOB, b INT, PRIMARY KEY(pk(1000))) ENGINE=InnoDB; 1341CREATE TABLE t3 (c INT) ENGINE=InnoDB; 1342CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; 1343INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1344SELECT * FROM t1, t2 1345WHERE a IN ( SELECT b FROM t2 LEFT JOIN v3 ON ( c = b ) ) ; 1346a pk b 1347DROP TABLE t1,t2,t3; 1348DROP VIEW v3; 1349# This must be the last in the file: 1350set optimizer_switch=@subselect_sj2_tmp; 1351# 1352# Bug #898073: potential incremental join cache for semijoin 1353# 1354CREATE TABLE t1 (a int, b varchar(1), KEY (b,a)); 1355INSERT INTO t1 VALUES (0,'x'), (5,'r'); 1356CREATE TABLE t2 (a int) ENGINE=InnoDB; 1357INSERT INTO t2 VALUES (8); 1358CREATE TABLE t3 (b varchar(1), c varchar(1)) ENGINE=InnoDB; 1359INSERT INTO t3 VALUES ('x','x'); 1360CREATE TABLE t4 (a int NOT NULL, b varchar(1)) ENGINE=InnoDB; 1361INSERT INTO t4 VALUES (20,'r'), (10,'x'); 1362set @tmp_optimizer_switch=@@optimizer_switch; 1363SET SESSION optimizer_switch='semijoin_with_cache=on'; 1364SET SESSION join_cache_level=2; 1365EXPLAIN 1366SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b 1367WHERE c IN (SELECT t4.b FROM t4 JOIN t2); 1368id select_type table type possible_keys key key_len ref rows Extra 13691 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where 13701 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 13711 PRIMARY t1 ref b b 4 test.t3.b 1 Using index 13722 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 13732 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 1374SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b 1375WHERE c IN (SELECT t4.b FROM t4 JOIN t2); 1376b c 1377x x 1378set optimizer_switch=@tmp_optimizer_switch; 1379set join_cache_level=default; 1380DROP TABLE t1,t2,t3,t4; 1381# 1382# Bug #899696: potential incremental join cache for semijoin 1383# 1384CREATE TABLE t1 (pk int PRIMARY KEY, a int); 1385INSERT INTO t1 VALUES (1, 6), (2, 8); 1386CREATE TABLE t2 (b int) ENGINE=InnoDB; 1387INSERT INTO t2 VALUES (8); 1388CREATE TABLE t3 (pk int PRIMARY KEY, a int); 1389INSERT INTO t3 VALUES (1, 6), (2, 8); 1390CREATE TABLE t4 (b int) ENGINE=InnoDB; 1391INSERT INTO t4 VALUES (2); 1392set @tmp_optimizer_switch=@@optimizer_switch; 1393SET optimizer_switch = 'semijoin_with_cache=on'; 1394SET join_cache_level = 2; 1395EXPLAIN 1396SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); 1397id select_type table type possible_keys key key_len ref rows Extra 13981 PRIMARY t2 ALL NULL NULL NULL NULL 1 13991 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 14001 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 14012 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 Using where 14022 MATERIALIZED t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 1403SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); 1404pk a b 14051 6 8 14062 8 8 1407set optimizer_switch=@tmp_optimizer_switch; 1408set join_cache_level=default; 1409DROP TABLE t1,t2,t3,t4; 1410# 1411# Bug #899962: materialized subquery with join_cache_level=3 1412# 1413CREATE TABLE t1 (a varchar(1), b varchar(1)) ENGINE=InnoDB; 1414INSERT INTO t1 VALUES ('v','v'); 1415CREATE TABLE t2 (a varchar(1), b varchar(1)) ENGINE=InnoDB; 1416INSERT INTO t2 VALUES ('v','v'); 1417set @tmp_optimizer_switch=@@optimizer_switch; 1418SET optimizer_switch = 'semijoin_with_cache=on'; 1419SET join_cache_level = 3; 1420EXPLAIN 1421SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); 1422id select_type table type possible_keys key key_len ref rows Extra 14231 PRIMARY t1 ALL NULL NULL NULL NULL 1 14241 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 14252 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 1426SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); 1427a b 1428v v 1429EXPLAIN 1430SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); 1431id select_type table type possible_keys key key_len ref rows Extra 14321 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where 14331 PRIMARY <subquery2> hash_ALL distinct_key #hash#distinct_key 4 test.t1.b 1 Using join buffer (flat, BNLH join) 14342 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using temporary 1435SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); 1436a b 1437v v 1438set optimizer_switch=@tmp_optimizer_switch; 1439set join_cache_level=default; 1440DROP TABLE t1,t2; 1441set @@optimizer_switch=@save_optimizer_switch_jcl6; 1442set @optimizer_switch_for_subselect_sj2_test=NULL; 1443set @join_cache_level_subselect_sj2_test=NULL; 1444