diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect_sj2.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2_jcl6.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj2_mat.result | 821 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 1769 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj2.test | 20 |
5 files changed, 60 insertions, 2590 deletions
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 6b75933fb98..24d75ced352 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -802,4 +802,24 @@ WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1 f12 y DROP TABLE t1,t2,t3; +# +# BUG#869012: Wrong result with semijoin + materialization + AND in WHERE +# +CREATE TABLE t1 (f3 varchar(1) , f4 varchar(1) ) engine=InnoDB; +INSERT IGNORE INTO t1 VALUES ('x','x'),('x','x'); +CREATE TABLE t2 ( f4 varchar(1) ) ; +INSERT IGNORE INTO t2 VALUES ('g'); +CREATE TABLE t3 (f4 varchar(1) ) Engine=InnoDB; +INSERT IGNORE INTO t3 VALUES ('x'); +set @tmp_869012=@@optimizer_switch; +SET optimizer_switch='semijoin=on,materialization=on'; +SELECT * +FROM t1 , t2 +WHERE ( t1.f4 ) IN ( SELECT f4 FROM t3 ) +AND t2.f4 != t1.f3 ; +f3 f4 f4 +x x g +x x g +set optimizer_switch= @tmp_869012; +# This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 88fefc8ac07..1804ab05af0 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -813,6 +813,26 @@ WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1 f12 y DROP TABLE t1,t2,t3; +# +# BUG#869012: Wrong result with semijoin + materialization + AND in WHERE +# +CREATE TABLE t1 (f3 varchar(1) , f4 varchar(1) ) engine=InnoDB; +INSERT IGNORE INTO t1 VALUES ('x','x'),('x','x'); +CREATE TABLE t2 ( f4 varchar(1) ) ; +INSERT IGNORE INTO t2 VALUES ('g'); +CREATE TABLE t3 (f4 varchar(1) ) Engine=InnoDB; +INSERT IGNORE INTO t3 VALUES ('x'); +set @tmp_869012=@@optimizer_switch; +SET optimizer_switch='semijoin=on,materialization=on'; +SELECT * +FROM t1 , t2 +WHERE ( t1.f4 ) IN ( SELECT f4 FROM t3 ) +AND t2.f4 != t1.f3 ; +f3 f4 f4 +x x g +x x g +set optimizer_switch= @tmp_869012; +# This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result deleted file mode 100644 index c18797a345e..00000000000 --- a/mysql-test/r/subselect_sj2_mat.result +++ /dev/null @@ -1,821 +0,0 @@ -set optimizer_switch='materialization=on'; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -set @subselect_sj2_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -drop table if exists t0, t1, t2, t3; -drop view if exists v1; -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 ( -a int, -b int -); -insert into t1 values (1,1),(1,1),(2,2); -create table t2 ( -a int, -b int, -key(b) -); -insert into t2 select a, a/2 from t0; -insert into t2 select a+10, a+10/2 from t0; -select * from t1; -a b -1 1 -1 1 -2 2 -select * from t2; -a b -0 0 -1 1 -2 1 -3 2 -4 2 -5 3 -6 3 -7 4 -8 4 -9 5 -10 5 -11 6 -12 7 -13 8 -14 9 -15 10 -16 11 -17 12 -18 13 -19 14 -explain select * from t2 where b in (select a from t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where -select * from t2 where b in (select a from t1); -a b -1 1 -2 1 -3 2 -4 2 -create table t3 ( -a int, -b int, -key(b), -pk1 char(200), pk2 char(200), pk3 char(200), -primary key(pk1, pk2, pk3) -) engine=innodb; -insert into t3 select a,a, a,a,a from t0; -insert into t3 select a,a, a+100,a+100,a+100 from t0; -explain select * from t3 where b in (select a from t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 20 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 -select * from t3 where b in (select a from t1); -a b pk1 pk2 pk3 -1 1 1 1 1 -1 1 101 101 101 -2 2 102 102 102 -2 2 2 2 2 -set @save_max_heap_table_size= @@max_heap_table_size; -set max_heap_table_size=16384; -set @save_join_buffer_size = @@join_buffer_size; -set join_buffer_size= 8000; -drop table t3; -create table t3 ( -a int, -b int, -key(b), -pk1 char(200), pk2 char(200), -primary key(pk1, pk2) -) engine=innodb; -insert into t3 select -A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a -from t0 A, t0 B where B.a <5; -explain select * from t3 where b in (select a from t0); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where -set @save_ecp= @@engine_condition_pushdown; -set engine_condition_pushdown=0; -select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); -a b pk1 pk2 -0 0 0 0 -1 1 1 1 -2 2 2 2 -3 3 3 3 -4 4 4 4 -5 5 5 5 -6 6 6 6 -7 7 7 7 -8 8 8 8 -9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 -set engine_condition_pushdown=@save_ecp; -set join_buffer_size= @save_join_buffer_size; -set max_heap_table_size= @save_max_heap_table_size; -explain select * from t1 where a in (select b from t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 2 Using index; FirstMatch(t1) -select * from t1; -a b -1 1 -1 1 -2 2 -select * from t1 where a in (select b from t2); -a b -1 1 -1 1 -2 2 -drop table t1, t2, t3; -set @save_join_buffer_size = @@join_buffer_size; -set join_buffer_size= 8000; -create table t1 (a int, filler1 binary(200), filler2 binary(200)); -insert into t1 select a, 'filler123456', 'filler123456' from t0; -insert into t1 select a+10, 'filler123456', 'filler123456' from t0; -create table t2 as select * from t1; -insert into t1 select a+20, 'filler123456', 'filler123456' from t0; -insert into t1 values (2, 'duplicate ok', 'duplicate ok'); -insert into t1 values (18, 'duplicate ok', 'duplicate ok'); -insert into t2 values (3, 'duplicate ok', 'duplicate ok'); -insert into t2 values (19, 'duplicate ok', 'duplicate ok'); -explain select -a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z -from t1 ot where a in (select a from t2 it); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY it ALL NULL NULL NULL NULL 22 -select -a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z -from t1 ot where a in (select a from t2 it); -a mid(filler1, 1,10) Z -0 filler1234 1 -1 filler1234 1 -2 filler1234 1 -3 filler1234 1 -4 filler1234 1 -5 filler1234 1 -6 filler1234 1 -7 filler1234 1 -8 filler1234 1 -9 filler1234 1 -10 filler1234 1 -11 filler1234 1 -12 filler1234 1 -13 filler1234 1 -14 filler1234 1 -15 filler1234 1 -16 filler1234 1 -17 filler1234 1 -18 filler1234 1 -19 filler1234 1 -2 duplicate 1 -18 duplicate 1 -explain select -a, mid(filler1, 1,10), length(filler1)=length(filler2) -from t2 ot where a in (select a from t1 it); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 32 -select -a, mid(filler1, 1,10), length(filler1)=length(filler2) -from t2 ot where a in (select a from t1 it); -a mid(filler1, 1,10) length(filler1)=length(filler2) -0 filler1234 1 -1 filler1234 1 -2 filler1234 1 -3 filler1234 1 -4 filler1234 1 -5 filler1234 1 -6 filler1234 1 -7 filler1234 1 -8 filler1234 1 -9 filler1234 1 -10 filler1234 1 -11 filler1234 1 -12 filler1234 1 -13 filler1234 1 -14 filler1234 1 -15 filler1234 1 -16 filler1234 1 -17 filler1234 1 -18 filler1234 1 -19 filler1234 1 -3 duplicate 1 -19 duplicate 1 -insert into t1 select a+20, 'filler123456', 'filler123456' from t0; -insert into t1 select a+20, 'filler123456', 'filler123456' from t0; -explain select -a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z -from t1 ot where a in (select a from t2 it); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY it ALL NULL NULL NULL NULL 22 -select -a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z -from t1 ot where a in (select a from t2 it); -a mid(filler1, 1,10) Z -0 filler1234 1 -1 filler1234 1 -2 filler1234 1 -3 filler1234 1 -4 filler1234 1 -5 filler1234 1 -6 filler1234 1 -7 filler1234 1 -8 filler1234 1 -9 filler1234 1 -10 filler1234 1 -11 filler1234 1 -12 filler1234 1 -13 filler1234 1 -14 filler1234 1 -15 filler1234 1 -16 filler1234 1 -17 filler1234 1 -18 filler1234 1 -19 filler1234 1 -2 duplicate 1 -18 duplicate 1 -explain select -a, mid(filler1, 1,10), length(filler1)=length(filler2) -from t2 ot where a in (select a from t1 it); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ot ALL NULL NULL NULL NULL 22 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY it ALL NULL NULL NULL NULL 52 -select -a, mid(filler1, 1,10), length(filler1)=length(filler2) -from t2 ot where a in (select a from t1 it); -a mid(filler1, 1,10) length(filler1)=length(filler2) -0 filler1234 1 -1 filler1234 1 -2 filler1234 1 -3 filler1234 1 -4 filler1234 1 -5 filler1234 1 -6 filler1234 1 -7 filler1234 1 -8 filler1234 1 -9 filler1234 1 -10 filler1234 1 -11 filler1234 1 -12 filler1234 1 -13 filler1234 1 -14 filler1234 1 -15 filler1234 1 -16 filler1234 1 -17 filler1234 1 -18 filler1234 1 -19 filler1234 1 -3 duplicate 1 -19 duplicate 1 -drop table t1, t2; -create table t1 (a int, b int, key(a)); -create table t2 (a int, b int, key(a)); -create table t3 (a int, b int, key(a)); -insert into t1 select a,a from t0; -insert into t2 select a,a from t0; -insert into t3 select a,a from t0; -t2 and t3 must be use 'ref', not 'ALL': -explain select * -from t0 where a in -(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 -2 SUBQUERY t1 index a a 5 NULL 10 Using where; Using index -2 SUBQUERY t2 ref a a 5 test.t1.a 1 Using index -2 SUBQUERY t3 ref a a 5 test.t1.a 1 Using index -drop table t0, t1,t2,t3; -CREATE TABLE t1 ( -ID int(11) NOT NULL auto_increment, -Name char(35) NOT NULL default '', -Country char(3) NOT NULL default '', -Population int(11) NOT NULL default '0', -PRIMARY KEY (ID), -INDEX (Population), -INDEX (Country) -); -CREATE TABLE t2 ( -Code char(3) NOT NULL default '', -Name char(52) NOT NULL default '', -SurfaceArea float(10,2) NOT NULL default '0.00', -Population int(11) NOT NULL default '0', -Capital int(11) default NULL, -PRIMARY KEY (Code), -UNIQUE INDEX (Name), -INDEX (Population) -); -CREATE TABLE t3 ( -Country char(3) NOT NULL default '', -Language char(30) NOT NULL default '', -Percentage float(3,1) NOT NULL default '0.0', -PRIMARY KEY (Country, Language), -INDEX (Percentage) -); -set @bug35674_save_optimizer_switch=@@optimizer_switch; -set optimizer_switch='materialization=off'; -EXPLAIN -SELECT Name FROM t2 -WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000) -AND -t2.Code IN (SELECT Country FROM t3 -WHERE Language='English' AND Percentage > 10 AND -t2.Population > 100000); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Rowid-ordered scan; Start temporary -1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where -1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; End temporary -set optimizer_switch=@bug35674_save_optimizer_switch; -DROP TABLE t1,t2,t3; -CREATE TABLE t1 ( -Code char(3) NOT NULL DEFAULT '', -Name char(52) NOT NULL DEFAULT '', -Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', -Region char(26) NOT NULL DEFAULT '', -SurfaceArea float(10,2) NOT NULL DEFAULT '0.00', -IndepYear smallint(6) DEFAULT NULL, -Population int(11) NOT NULL DEFAULT '0', -LifeExpectancy float(3,1) DEFAULT NULL, -GNP float(10,2) DEFAULT NULL, -GNPOld float(10,2) DEFAULT NULL, -LocalName char(45) NOT NULL DEFAULT '', -GovernmentForm char(45) NOT NULL DEFAULT '', -HeadOfState char(60) DEFAULT NULL, -Capital int(11) DEFAULT NULL, -Code2 char(2) NOT NULL DEFAULT '', -PRIMARY KEY (Code) -); -CREATE TABLE t2 ( -ID int(11) NOT NULL AUTO_INCREMENT, -Name char(35) NOT NULL DEFAULT '', -CountryCode char(3) NOT NULL DEFAULT '', -District char(20) NOT NULL DEFAULT '', -Population int(11) NOT NULL DEFAULT '0', -PRIMARY KEY (ID), -KEY CountryCode (CountryCode) -); -Fill the table with test data -This must not use LooseScan: -EXPLAIN SELECT Name FROM t1 -WHERE t1.Code IN ( -SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t2 ALL CountryCode NULL NULL NULL 545 Using where -SELECT Name FROM t1 -WHERE t1.Code IN ( -SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); -Name -Austria -Canada -China -Czech Republic -drop table t1, t2; -drop procedure if exists p1; -drop procedure if exists p2; -drop procedure if exists p3; -drop procedure if exists p4; -CREATE TABLE t1(a INT); -CREATE TABLE t2(c INT); -CREATE PROCEDURE p1(v1 int) -BEGIN -SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2); -END -// -CREATE PROCEDURE p2(v1 int) -BEGIN -SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2); -END -// -CREATE PROCEDURE p3(v1 int) -BEGIN -SELECT 1 -FROM -t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, -t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, -t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, -t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, -t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, -t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, -t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, -t1 t57,t1 t58,t1 t59,t1 t60 -WHERE t01.a IN (SELECT c FROM t2); -END -// -CREATE PROCEDURE p4(v1 int) -BEGIN -SELECT 1 -FROM -t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, -t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, -t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, -t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, -t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, -t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, -t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, -t1 t57,t1 t58,t1 t59,t1 t60 -WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2); -END -// -CALL p1(1); -1 -CALL p2(1); -1 -CALL p3(1); -1 -CALL p4(1); -1 -DROP TABLE t1, t2; -DROP PROCEDURE p1; -DROP PROCEDURE p2; -DROP PROCEDURE p3; -DROP PROCEDURE p4; -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4); -create table t1 (a int, b int, key(a)); -insert into t1 select a,a from t0; -insert into t1 select a+5,a from t0; -create table t2 (a int, b int, primary key(a)); -insert into t2 select * from t1; -Table t2, unlike table t1, should be displayed as pulled out -explain extended select * from t0 -where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and -t1.b=t2.b); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using where; FirstMatch(t2) -Warnings: -Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t0` where ((`test`.`t1`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`a` = `test`.`t0`.`a`) and (`test`.`t1`.`a` = `test`.`t0`.`a`)) -update t1 set a=3, b=11 where a=4; -update t2 set b=11 where a=3; -select * from t0 where t0.a in -(select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b); -a -0 -1 -2 -3 -drop table t0, t1, t2; -CREATE TABLE t1 ( -id int(11) NOT NULL, -PRIMARY KEY (id)); -CREATE TABLE t2 ( -id int(11) NOT NULL, -fid int(11) NOT NULL, -PRIMARY KEY (id)); -insert into t1 values(1); -insert into t2 values(1,7503),(2,1); -explain select count(*) -from t1 -where fid IN (select fid from t2 where (id between 7502 and 8420) order by fid ); -ERROR 42S22: Unknown column 'fid' in 'IN/ALL/ANY subquery' -drop table t1, t2; -create table t1 (a int, b int, key (a), key (b)); -insert into t1 values (2,4),(2,4),(2,4); -select t1.a from t1 -where -t1.a in (select 1 from t1 where t1.a in (select 1 from t1) group by t1.a); -a -drop table t1; -create table t1(a int,b int,key(a),key(b)); -insert into t1 values (1,1),(2,2),(3,3); -select 1 from t1 -where t1.a not in (select 1 from t1 -where t1.a in (select 1 from t1) -group by t1.b); -1 -1 -1 -drop table t1; -CREATE TABLE t1 -(EMPNUM CHAR(3) NOT NULL, -EMPNAME CHAR(20), -GRADE DECIMAL(4), -CITY CHAR(15)); -CREATE TABLE t2 -(PNUM CHAR(3) NOT NULL, -PNAME CHAR(20), -PTYPE CHAR(6), -BUDGET DECIMAL(9), -CITY CHAR(15)); -CREATE TABLE t3 -(EMPNUM CHAR(3) NOT NULL, -PNUM CHAR(3) NOT NULL, -HOURS DECIMAL(5)); -INSERT INTO t1 VALUES ('E1','Alice',12,'Deale'); -INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna'); -INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna'); -INSERT INTO t1 VALUES ('E4','Don',12,'Deale'); -INSERT INTO t1 VALUES ('E5','Ed',13,'Akron'); -INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale'); -INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna'); -INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa'); -INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale'); -INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna'); -INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale'); -INSERT INTO t3 VALUES ('E1','P1',40); -INSERT INTO t3 VALUES ('E1','P2',20); -INSERT INTO t3 VALUES ('E1','P3',80); -INSERT INTO t3 VALUES ('E1','P4',20); -INSERT INTO t3 VALUES ('E1','P5',12); -INSERT INTO t3 VALUES ('E1','P6',12); -INSERT INTO t3 VALUES ('E2','P1',40); -INSERT INTO t3 VALUES ('E2','P2',80); -INSERT INTO t3 VALUES ('E3','P2',20); -INSERT INTO t3 VALUES ('E4','P2',20); -INSERT INTO t3 VALUES ('E4','P4',40); -INSERT INTO t3 VALUES ('E4','P5',80); -SELECT * FROM t1; -EMPNUM EMPNAME GRADE CITY -E1 Alice 12 Deale -E2 Betty 10 Vienna -E3 Carmen 13 Vienna -E4 Don 12 Deale -E5 Ed 13 Akron -CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM); -SELECT EMPNAME -FROM t1 -WHERE EMPNUM IN -(SELECT EMPNUM -FROM t3 -WHERE PNUM IN -(SELECT PNUM -FROM t2 -WHERE PTYPE = 'Design')); -EMPNAME -Alice -Betty -Don -DROP INDEX t1_IDX ON t1; -CREATE INDEX t1_IDX ON t1(EMPNUM); -SELECT EMPNAME -FROM t1 -WHERE EMPNUM IN -(SELECT EMPNUM -FROM t3 -WHERE PNUM IN -(SELECT PNUM -FROM t2 -WHERE PTYPE = 'Design')); -EMPNAME -Alice -Betty -Don -DROP INDEX t1_IDX ON t1; -SELECT EMPNAME -FROM t1 -WHERE EMPNUM IN -(SELECT EMPNUM -FROM t3 -WHERE PNUM IN -(SELECT PNUM -FROM t2 -WHERE PTYPE = 'Design')); -EMPNAME -Alice -Betty -Don -DROP TABLE t1, t2, t3; -CREATE TABLE t1 (f1 INT NOT NULL); -CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1; -SELECT * FROM v1; -a -drop view v1; -drop table t1; -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1(a int, b int); -insert into t1 values (0,0),(1,1),(2,2); -create table t2 as select * from t1; -create table t3 (pk int, a int, primary key(pk)); -insert into t3 select a,a from t0; -explain -select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where -2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 10 Using index -drop table t0, t1, t2, t3; -create table t1 (a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb; -insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1; -insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1; -alter table t2 add filler1 int; -insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; -set @save_join_buffer_size=@@join_buffer_size; -set join_buffer_size=1; -select * from t2 where filler1 in ( select a from t1); -a b c filler1 -set join_buffer_size=default; -drop table t1, t2; -create table t1 (a int not null); -drop procedure if exists p1; -CREATE PROCEDURE p1() -BEGIN -DECLARE EXIT HANDLER FOR SQLEXCEPTION select a from t1; -prepare s1 from ' - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( - select a from t1 where a in ( select a from t1) - )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))'; -execute s1; -END; -| -call p1(); -a -drop procedure p1; -drop table t1; -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C; -create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1; -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `id` int(11) NOT NULL DEFAULT '0', - `a` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `a` (`a`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -set @a=0; -create table t3 as select * from t2 limit 0; -insert into t3 select @a:=@a+1, t2.a from t2, t0; -insert into t3 select @a:=@a+1, t2.a from t2, t0; -insert into t3 select @a:=@a+1, t2.a from t2, t0; -alter table t3 add primary key(id), add key(a); -The following must use loose index scan over t3, key a: -explain select count(a) from t2 where a in ( SELECT a FROM t3); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t3 index a a 5 NULL 30000 Using index -select count(a) from t2 where a in ( SELECT a FROM t3); -count(a) -1000 -drop table t0,t1,t2,t3; - -BUG#42740: crash in optimize_semijoin_nests - -create table t1 (c6 timestamp,key (c6)) engine=innodb; -create table t2 (c2 double) engine=innodb; -explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null) ; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -drop table t1, t2; -# -# BUG#42742: crash in setup_sj_materialization, Copy_field::set -# -create table t3 ( c1 year) engine=innodb; -insert into t3 values (2135),(2142); -create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb; -# The following must not crash, EXPLAIN should show one SJ strategy, not a mix: -explain select 1 from t2 where -c2 in (select 1 from t3, t2) and -c1 in (select convert(c6,char(1)) from t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) -drop table t2, t3; -# -# BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 -# -CREATE TABLE t1 ( f1 int NOT NULL , f10 int) ; -INSERT IGNORE INTO t1 VALUES (25,0),(29,0); -CREATE TABLE t2 ( f10 int) ENGINE=InnoDB; -CREATE TABLE t3 ( f11 int) ; -INSERT IGNORE INTO t3 VALUES (0); -SELECT alias1.f10 AS field2 -FROM t2 AS alias1 -JOIN ( -t3 AS alias2 -JOIN t1 AS alias3 -ON alias3.f10 -) ON alias3.f1 -WHERE alias2.f11 IN ( -SELECT SQ4_alias1.f10 -FROM t1 AS SQ4_alias1 -LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 -) -GROUP BY field2; -field2 -drop table t1, t2, t3; -# -# BUG#849763: Wrong result with second execution of prepared statement with semijoin + view -# -CREATE TABLE t1 ( c varchar(1)) engine=innodb; -INSERT INTO t1 VALUES ('r'); -CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb; -INSERT INTO t2 VALUES (1,'r','r'); -CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1; -PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)'; -EXECUTE st1; -a b c -1 r r -EXECUTE st1; -a b c -1 r r -DROP VIEW v1; -DROP TABLE t1, t2; -# -# BUG#858732: Wrong result with semijoin + loosescan + comma join -# -CREATE TABLE t1 (f13 int(11) NOT NULL , PRIMARY KEY (f13)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (16),(24); -CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) ENGINE=InnoDB; -INSERT INTO t2 VALUES (6,'y'); -CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; -INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); -# The following must use LooseScan but not join buffering -explain -SELECT * FROM t3 -WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 Using index -1 PRIMARY alias2 index f12 f12 7 NULL 1 Using index; LooseScan -1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; FirstMatch(alias2) -1 PRIMARY t3 ALL NULL NULL NULL NULL 7 Using where; Using join buffer (flat, BNL join) -SELECT * FROM t3 -WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); -f12 -y -DROP TABLE t1,t2,t3; -set optimizer_switch=@subselect_sj2_tmp; -set optimizer_switch=default; -select @@optimizer_switch like '%materialization=on%'; -@@optimizer_switch like '%materialization=on%' -0 diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result deleted file mode 100644 index bbc5b88bc80..00000000000 --- a/mysql-test/r/subselect_sj_mat.result +++ /dev/null @@ -1,1769 +0,0 @@ -set @subselect_sj_mat_tmp= @@optimizer_switch; -set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on'); -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -set @optimizer_switch_local_default= @@optimizer_switch; -drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; -drop table if exists columns; -drop table if exists t1_16, t2_16, t3_16; -drop view if exists v1, v2, v1m, v2m; -create table t1 (a1 char(8), a2 char(8)); -create table t2 (b1 char(8), b2 char(8)); -create table t3 (c1 char(8), c2 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'); -insert into t3 values ('1 - 01', '2 - 01'); -insert into t3 values ('1 - 02', '2 - 02'); -insert into t3 values ('1 - 03', '2 - 03'); -insert into t3 values ('1 - 04', '2 - 04'); -create table t1i (a1 char(8), a2 char(8)); -create table t2i (b1 char(8), b2 char(8)); -create table t3i (c1 char(8), c2 char(8)); -create index it1i1 on t1i (a1); -create index it1i2 on t1i (a2); -create index it1i3 on t1i (a1, a2); -create index it2i1 on t2i (b1); -create index it2i2 on t2i (b2); -create index it2i3 on t2i (b1, b2); -create index it3i1 on t3i (c1); -create index it3i2 on t3i (c2); -create index it3i3 on t3i (c1, c2); -insert into t1i select * from t1; -insert into t2i select * from t2; -insert into t3i select * from t3; -set @@optimizer_switch='materialization=on,in_to_exists=off,firstmatch=off'; -/****************************************************************************** -* Simple tests. -******************************************************************************/ -# non-indexed nullable fields -explain extended -select * from t1 where a1 in (select b1 from t2 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0')) -select * from t1 where a1 in (select b1 from t2 where b1 > '0'); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`<subquery2>`.`b1` = `test`.`t1`.`a1`) -select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) -select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) -select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 40.00 Using where; Using index; LooseScan -1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) -select * from t1i where a1 in (select b1 from t2i where b1 > '0'); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key # NULL # 3 100.00 # -1 PRIMARY t1i ref it1i1,it1i3 # 9 # 1 100.00 # -2 SUBQUERY t2i range it2i1,it2i3 # 9 # 3 100.00 # -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `<subquery2>`.`b1`) -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 40.00 Using where; Using index; LooseScan -1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # -1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # -2 SUBQUERY t2i range it2i1,it2i3 # # # 3 100.00 # -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`)) -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # -1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # -2 SUBQUERY t2i range it2i1,it2i3 # # # 3 100.00 # -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`<subquery2>`.`min(b2)` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`)) -select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 -2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`max(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) -select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; -execute st1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 -2 SUBQUERY t2i range NULL it2i3 9 NULL 3 Using index for group-by -execute st1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 -2 SUBQUERY t2i range NULL it2i3 9 NULL 3 Using index for group-by -prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)"; -execute st2; -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -execute st2; -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 -2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) -select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1); -ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch=@optimizer_switch_local_default; -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=@optimizer_switch_local_default; -set @@optimizer_switch='materialization=off,in_to_exists=on'; -execute st1; -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -set @@optimizer_switch=@optimizer_switch_local_default; -set @@optimizer_switch='semijoin=off'; -execute st1; -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -set @@optimizer_switch=@optimizer_switch_local_default; -set @@optimizer_switch='materialization=off,in_to_exists=on'; -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=@optimizer_switch_local_default; -set @@optimizer_switch='semijoin=off'; -execute st1; -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -set @@optimizer_switch=@optimizer_switch_local_default; -set @@optimizer_switch='materialization=off,in_to_exists=on'; -execute st1; -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -set @@optimizer_switch=@save_optimizer_switch; -explain extended -select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) -select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00 -2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`)) -select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -/****************************************************************************** -* Views, UNIONs, several levels of nesting. -******************************************************************************/ -# materialize the result of subquery over temp-table view -create algorithm=merge view v1 as -select b1, c2 from t2, t3 where b2 > c2; -create algorithm=merge view v2 as -select b1, c2 from t2, t3 group by b2, c2; -Warnings: -Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) -create algorithm=temptable view v1m as -select b1, c2 from t2, t3 where b2 > c2; -create algorithm=temptable view v2m as -select b1, c2 from t2, t3 group by b2, c2; -select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null); -b1 c2 -1 - 02 2 - 01 -1 - 02 2 - 01 -1 - 03 2 - 01 -1 - 03 2 - 02 -select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null); -b1 c2 -1 - 02 2 - 01 -1 - 02 2 - 01 -1 - 03 2 - 01 -1 - 03 2 - 02 -select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null); -b1 c2 -1 - 02 2 - 01 -1 - 02 2 - 01 -1 - 03 2 - 01 -1 - 03 2 - 02 -select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null); -b1 c2 -1 - 02 2 - 01 -1 - 02 2 - 01 -1 - 03 2 - 01 -1 - 03 2 - 02 -drop view v1, v2, v1m, v2m; -explain extended -select * from t1 -where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where -3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -3 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t2`.`b1` > '0') and (`test`.`t3`.`c2` > '0')) -select * from t1 -where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1i -where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and -(a1, a2) in (select c1, c2 from t3i -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # -1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # -1 PRIMARY <subquery3> eq_ref distinct_key # # # 1 100.00 # -2 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 # -3 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 # -3 SUBQUERY t3i index it3i1,it3i2,it3i3 # # # 4 75.00 # -Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0')) -select * from t1i -where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and -(a1, a2) in (select c1, c2 from t3i -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 where c2 LIKE '%02') or -b2 in (select c2 from t3 where c2 LIKE '%03')) and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where -5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -5 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) -4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3`.`c2` > '0')) -select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 where c2 LIKE '%02') or -b2 in (select c2 from t3 where c2 LIKE '%03')) and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -a1 a2 -1 - 02 2 - 02 -explain extended -select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 t3a where c1 = a1) or -b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and -(a1, a2) in (select c1, c2 from t3 t3c -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Start temporary -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -5 SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00 Using where -5 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) -4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where -3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3c`.`c2` > '0')) -select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 t3a where c1 = a1) or -b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and -(a1, a2) in (select c1, c2 from t3 t3c -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -(select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 where c2 LIKE '%02') or -b2 in (select c2 from t3 where c2 LIKE '%03') -group by b1, b2) and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) -UNION -(select * from t1i -where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and -(a1, a2) in (select c1, c2 from t3i -where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key # # # 5 0.00 # -1 PRIMARY t3 ALL NULL # # # 4 100.00 # -1 PRIMARY t1 ALL NULL # # # 3 100.00 # -1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # -2 SUBQUERY t2 ALL NULL # # # 5 100.00 # -4 SUBQUERY t3 ALL NULL # # # 4 100.00 # -3 SUBQUERY t3 ALL NULL # # # 4 100.00 # -7 UNION t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # -7 UNION <subquery8> eq_ref distinct_key # # # 1 100.00 # -7 UNION <subquery9> eq_ref distinct_key # # # 1 100.00 # -8 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 # -9 SUBQUERY t2i index it2i1,it2i2,it2i3 # # # 5 100.00 # -9 SUBQUERY t3i index it3i1,it3i2,it3i3 # # # 4 75.00 # -NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # -Warnings: -Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t1` where ((`test`.`t3`.`c2` = `<subquery2>`.`b2`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`) and (`test`.`t2i`.`b2` = `<subquery2>`.`b2`) and (`test`.`t3`.`c1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t2i`.`b1` = `<subquery2>`.`b1`) and (`<subquery2>`.`b2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))) -(select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 where c2 LIKE '%02') or -b2 in (select c2 from t3 where c2 LIKE '%03') -group by b1, b2) and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))) -UNION -(select * from t1i -where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and -(a1, a2) in (select c1, c2 from t3i -where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); -a1 a2 -1 - 02 2 - 02 -1 - 01 2 - 01 -explain extended -select * from t1 -where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -4 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) and (`test`.`t3`.`c2` > '0')) -select * from t1 -where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and -(a1, a2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); -a1 a2 -1 - 01 2 - 01 -1 - 02 2 - 02 -explain extended -select * from t1, t3 -where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and -(c1, c2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and -a1 = c1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 18 func,func 1 100.00 -4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -4 SUBQUERY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))) and (`test`.`t3`.`c2` > '0')) -select * from t1, t3 -where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and -(c1, c2) in (select c1, c2 from t3 -where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and -a1 = c1; -a1 a2 c1 c2 -1 - 01 2 - 01 1 - 01 2 - 01 -1 - 02 2 - 02 1 - 02 2 - 02 -/****************************************************************************** -* Negative tests, where materialization should not be applied. -******************************************************************************/ -# UNION in a subquery -explain extended -select * from t3 -where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where -NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL -Warnings: -Note 1003 select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <expr_cache><`test`.`t3`.`c1`>(<in_optimizer>(`test`.`t3`.`c1`,<exists>(select `test`.`t1`.`a1` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1`)) union select `test`.`t2`.`b1` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`))))) -select * from t3 -where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9'); -c1 c2 -1 - 01 2 - 01 -1 - 02 2 - 02 -1 - 03 2 - 03 -explain extended -select * from t1 -where (a1, a2) in (select b1, b2 from t2 -where b2 in (select c2 from t3 t3a where c1 = a1) or -b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and -(a1, a2) in (select c1, c2 from t3 t3c -where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where -3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 -Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))))) -explain extended -select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) -select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); -a1 a2 -1 - 01 2 - 01 -explain extended -select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having (((<cache>(`test`.`t1`.`a1`) = '1 - 01') or isnull('1 - 01')) and ((<cache>(`test`.`t1`.`a2`) = '2 - 01') or isnull('2 - 01')) and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))) -select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual); -a1 a2 -1 - 01 2 - 01 -/****************************************************************************** -* Subqueries in other uncovered clauses. -******************************************************************************/ -/* SELECT clause */ -select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1; -((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL -0 -0 -0 -/* GROUP BY clause */ -create table columns (col int key); -insert into columns values (1), (2); -explain extended -select * from t1 group by (select col from columns limit 1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (select `test`.`columns`.`col` from `test`.`columns` limit 1) -select * from t1 group by (select col from columns limit 1); -a1 a2 -1 - 00 2 - 00 -explain extended -select * from t1 group by (a1 in (select col from columns)); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort -2 DEPENDENT SUBQUERY columns unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index; Using where; Full scan on NULL key -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))) -select * from t1 group by (a1 in (select col from columns)); -a1 a2 -1 - 00 2 - 00 -/* ORDER BY clause */ -explain extended -select * from t1 order by (select col from columns limit 1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -2 SUBQUERY columns index NULL PRIMARY 4 NULL 2 100.00 Using index -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (select `test`.`columns`.`col` from `test`.`columns` limit 1) -select * from t1 order by (select col from columns limit 1); -a1 a2 -1 - 00 2 - 00 -1 - 01 2 - 01 -1 - 02 2 - 02 -/****************************************************************************** -* Column types/sizes that affect materialization. -******************************************************************************/ -/* -Test that BLOBs are not materialized (except when arguments of some functions). -*/ -# force materialization to be always considered -set @prefix_len = 6; -set @blob_len = 16; -set @suffix_len = @blob_len - @prefix_len; -create table t1_16 (a1 blob(16), a2 blob(16)); -create table t2_16 (b1 blob(16), b2 blob(16)); -create table t3_16 (c1 blob(16), c2 blob(16)); -insert into t1_16 values -(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); -insert into t1_16 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t1_16 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_16 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t2_16 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_16 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_16 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t3_16 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t3_16 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_16 values -(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); -explain extended select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select b1 from t2_16 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select b1 from t2_16 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_16 -where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_16 -where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 20 func 1 100.00 -2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where -Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` > '0') and (`test`.`t1_16`.`a1` = substr(`test`.`t2_16`.`b1`,1,16))) -select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select group_concat(b1) from t2_16 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where -2 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <expr_cache><`test`.`t1_16`.`a1`>(<in_optimizer>(`test`.`t1_16`.`a1`,<exists>(select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) = <ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ',')))))) -select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select group_concat(b1) from t2_16 group by b2); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -set @@group_concat_max_len = 256; -explain extended select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select group_concat(b1) from t2_16 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_16.a1 1 100.00 Using where -2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_16 -where a1 in (select group_concat(b1) from t2_16 group by b2); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended -select * from t1 -where concat(a1,'x') IN -(select left(a1,8) from t1_16 -where (a1, a2) IN -(select t2_16.b1, t2_16.b2 from t2_16, t2 -where t2.b2 = substring(t2_16.b2,1,6) and -t2.b1 IN (select c1 from t3 where c2 > '0'))); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Start temporary -1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2_16` join `test`.`t2` join `test`.`t1_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2`.`b1` = `test`.`t3`.`c1`) and (`test`.`t2`.`b2` = substr(`test`.`t1_16`.`a2`,1,6)) and (`test`.`t3`.`c2` > '0') and (concat(`test`.`t1`.`a1`,'x') = left(`test`.`t1_16`.`a1`,8))) -drop table t1_16, t2_16, t3_16; -set @blob_len = 512; -set @suffix_len = @blob_len - @prefix_len; -create table t1_512 (a1 blob(512), a2 blob(512)); -create table t2_512 (b1 blob(512), b2 blob(512)); -create table t3_512 (c1 blob(512), c2 blob(512)); -insert into t1_512 values -(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); -insert into t1_512 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t1_512 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_512 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t2_512 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_512 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_512 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t3_512 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t3_512 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_512 values -(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); -explain extended select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select b1 from t2_512 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select b1 from t2_512 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_512 -where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_512 -where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 517 func 1 100.00 -2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where -Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` > '0') and (`test`.`t1_512`.`a1` = substr(`test`.`t2_512`.`b1`,1,512))) -select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select group_concat(b1) from t2_512 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where -2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select group_concat(b1) from t2_512 group by b2); -left(a1,7) left(a2,7) -set @@group_concat_max_len = 256; -explain extended select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select group_concat(b1) from t2_512 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where -2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_512 -where a1 in (select group_concat(b1) from t2_512 group by b2); -left(a1,7) left(a2,7) -drop table t1_512, t2_512, t3_512; -set @blob_len = 1024; -set @suffix_len = @blob_len - @prefix_len; -create table t1_1024 (a1 blob(1024), a2 blob(1024)); -create table t2_1024 (b1 blob(1024), b2 blob(1024)); -create table t3_1024 (c1 blob(1024), c2 blob(1024)); -insert into t1_1024 values -(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); -insert into t1_1024 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t1_1024 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_1024 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t2_1024 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_1024 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_1024 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t3_1024 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t3_1024 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_1024 values -(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); -explain extended select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select b1 from t2_1024 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select b1 from t2_1024 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_1024 -where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_1024 -where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Start temporary -1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` > '0') and (`test`.`t1_1024`.`a1` = substr(`test`.`t2_1024`.`b1`,1,1024))) -select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select group_concat(b1) from t2_1024 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where -2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select group_concat(b1) from t2_1024 group by b2); -left(a1,7) left(a2,7) -set @@group_concat_max_len = 256; -explain extended select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select group_concat(b1) from t2_1024 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where -2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_1024 -where a1 in (select group_concat(b1) from t2_1024 group by b2); -left(a1,7) left(a2,7) -drop table t1_1024, t2_1024, t3_1024; -set @blob_len = 1025; -set @suffix_len = @blob_len - @prefix_len; -create table t1_1025 (a1 blob(1025), a2 blob(1025)); -create table t2_1025 (b1 blob(1025), b2 blob(1025)); -create table t3_1025 (c1 blob(1025), c2 blob(1025)); -insert into t1_1025 values -(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); -insert into t1_1025 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t1_1025 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_1025 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t2_1025 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_1025 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_1025 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t3_1025 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t3_1025 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -insert into t3_1025 values -(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len))); -explain extended select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select b1 from t2_1025 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select b1 from t2_1025 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_1025 -where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary -1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0')) -select left(a1,7), left(a2,7) -from t1_1025 -where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Start temporary -1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` > '0') and (`test`.`t1_1025`.`a1` = substr(`test`.`t2_1025`.`b1`,1,1025))) -select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -1 - 02x 2 - 02x -explain extended select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select group_concat(b1) from t2_1025 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where -2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select group_concat(b1) from t2_1025 group by b2); -left(a1,7) left(a2,7) -set @@group_concat_max_len = 256; -explain extended select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select group_concat(b1) from t2_1025 group by b2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where -2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort -Warnings: -Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`) -select left(a1,7), left(a2,7) -from t1_1025 -where a1 in (select group_concat(b1) from t2_1025 group by b2); -left(a1,7) left(a2,7) -drop table t1_1025, t2_1025, t3_1025; -create table t1bit (a1 bit(3), a2 bit(3)); -create table t2bit (b1 bit(3), b2 bit(3)); -insert into t1bit values (b'000', b'100'); -insert into t1bit values (b'001', b'101'); -insert into t1bit values (b'010', b'110'); -insert into t2bit values (b'001', b'101'); -insert into t2bit values (b'010', b'110'); -insert into t2bit values (b'110', b'111'); -explain extended select bin(a1), bin(a2) -from t1bit -where (a1, a2) in (select b1, b2 from t2bit); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func,func 1 100.00 -2 SUBQUERY t2bit ALL NULL NULL NULL NULL 3 100.00 -Warnings: -Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` semi join (`test`.`t2bit`) where 1 -select bin(a1), bin(a2) -from t1bit -where (a1, a2) in (select b1, b2 from t2bit); -bin(a1) bin(a2) -1 101 -10 110 -drop table t1bit, t2bit; -create table t1bb (a1 bit(3), a2 blob(3)); -create table t2bb (b1 bit(3), b2 blob(3)); -insert into t1bb values (b'000', '100'); -insert into t1bb values (b'001', '101'); -insert into t1bb values (b'010', '110'); -insert into t2bb values (b'001', '101'); -insert into t2bb values (b'010', '110'); -insert into t2bb values (b'110', '111'); -explain extended select bin(a1), a2 -from t1bb -where (a1, a2) in (select b1, b2 from t2bb); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1bb ALL NULL NULL NULL NULL 3 100.00 Start temporary -1 PRIMARY t2bb ALL NULL NULL NULL NULL 3 100.00 Using where; End temporary; Using join buffer (flat, BNL join) -Warnings: -Note 1003 select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` semi join (`test`.`t2bb`) where ((`test`.`t2bb`.`b2` = `test`.`t1bb`.`a2`) and (`test`.`t2bb`.`b1` = `test`.`t1bb`.`a1`)) -select bin(a1), a2 -from t1bb -where (a1, a2) in (select b1, b2 from t2bb); -bin(a1) a2 -1 101 -10 110 -drop table t1bb, t2bb; -drop table t1, t2, t3, t1i, t2i, t3i, columns; -/****************************************************************************** -* Test the cache of the left operand of IN. -******************************************************************************/ -# Test that default values of Cached_item are not used for comparison -create table t1 (s1 int); -create table t2 (s2 int); -insert into t1 values (5),(1),(0); -insert into t2 values (0), (1); -select s2 from t2 where s2 in (select s1 from t1); -s2 -0 -1 -drop table t1, t2; -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); -insert into t1 values (1,10); -insert into t1 values (1,20); -insert into t1 values (2,10); -insert into t1 values (2,20); -insert into t1 values (2,30); -insert into t1 values (3,20); -insert into t1 values (4,40); -insert into t2 values (2,10); -insert into t2 values (2,20); -insert into t2 values (2,40); -insert into t2 values (3,20); -insert into t2 values (4,10); -insert into t2 values (5,10); -insert into t3 values (10); -insert into t3 values (10); -insert into t3 values (20); -insert into t3 values (30); -explain extended -select a from t1 where a in (select c from t2 where d >= 20); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20)) -select a from t1 where a in (select c from t2 where d >= 20); -a -2 -2 -2 -3 -create index it1a on t1(a); -explain extended -select a from t1 where a in (select c from t2 where d >= 20); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ref it1a it1a 4 test.t2.c 2 100.00 Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20)) -select a from t1 where a in (select c from t2 where d >= 20); -a -2 -2 -2 -3 -insert into t2 values (1,10); -explain extended -select a from t1 where a in (select c from t2 where d >= 20); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` >= 20)) -select a from t1 where a in (select c from t2 where d >= 20); -a -2 -2 -2 -3 -explain extended -select a from t1 group by a having a in (select c from t2 where d >= 20); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`)))))) -select a from t1 group by a having a in (select c from t2 where d >= 20); -a -2 -3 -create index iab on t1(a, b); -explain extended -select a from t1 group by a having a in (select c from t2 where d >= 20); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index -2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where -Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`)))))) -select a from t1 group by a having a in (select c from t2 where d >= 20); -a -2 -3 -explain extended -select a from t1 group by a -having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 index NULL iab 8 NULL 7 100.00 Using index -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where -3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`c` from `test`.`t2` where (<nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`)))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))) -select a from t1 group by a -having a in (select c from t2 where d >= some(select e from t3 where max(b)=e)); -a -2 -3 -explain extended -select a from t1 -where a in (select c from t2 where d >= some(select e from t3 where b=e)); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 Start temporary -1 PRIMARY t1 ref it1a,iab iab 4 test.t2.c 1 100.00 Using where; Using index; End temporary -3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -Warnings: -Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) -select a from t1 -where a in (select c from t2 where d >= some(select e from t3 where b=e)); -a -2 -2 -2 -3 -1 -drop table t1, t2, t3; -create table t2 (a int, b int, key(a), key(b)); -insert into t2 values (3,3),(3,3),(3,3); -select 1 from t2 where -t2.a > 1 -or -t2.a = 3 and not t2.a not in (select t2.b from t2); -1 -1 -1 -1 -drop table t2; -create table t1 (a1 int key); -create table t2 (b1 int); -insert into t1 values (5); -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -min(a1) -NULL -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch=@optimizer_switch_local_default; -set @@optimizer_switch='materialization=off,in_to_exists=on'; -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -min(a1) -NULL -set @@optimizer_switch=@optimizer_switch_local_default; -set @@optimizer_switch='semijoin=off'; -explain select min(a1) from t1 where 7 in (select b1 from t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -select min(a1) from t1 where 7 in (select b1 from t2); -min(a1) -NULL -set @@optimizer_switch=@optimizer_switch_local_default; -set @@optimizer_switch='materialization=off,in_to_exists=on'; -# with MariaDB and MWL#90, this particular case is solved: -explain select min(a1) from t1 where 7 in (select b1 from t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -select min(a1) from t1 where 7 in (select b1 from t2); -min(a1) -NULL -# but when we go around MWL#90 code, the problem still shows up: -explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4; -min(a1) -NULL -set @@optimizer_switch= @save_optimizer_switch; -drop table t1,t2; -create table t1 (a char(2), b varchar(10)); -insert into t1 values ('a', 'aaa'); -insert into t1 values ('aa', 'aaaa'); -explain select a,b from t1 where b in (select a from t1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 -select a,b from t1 where b in (select a from t1); -a b -prepare st1 from "select a,b from t1 where b in (select a from t1)"; -execute st1; -a b -execute st1; -a b -drop table t1; -# -# BUG#49630: Segfault in select_describe() with double -# nested subquery and materialization -# -CREATE TABLE t1 (t1i int); -CREATE TABLE t2 (t2i int); -CREATE TABLE t3 (t3i int); -CREATE TABLE t4 (t4i int); -INSERT INTO t1 VALUES (1); -INSERT INTO t2 VALUES (1),(2); -INSERT INTO t3 VALUES (1),(2); -INSERT INTO t4 VALUES (1),(2); - -EXPLAIN -SELECT t1i -FROM t1 JOIN t4 ON t1i=t4i -WHERE (t1i) IN ( -SELECT t2i -FROM t2 -WHERE (t2i) IN ( -SELECT t3i -FROM t3 -GROUP BY t3i -) -); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 5 const 1 Start temporary -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using temporary -DROP TABLE t1,t2,t3,t4; -CREATE TABLE t1 ( -pk INTEGER AUTO_INCREMENT, -col_int_nokey INTEGER, -col_int_key INTEGER, -col_varchar_key VARCHAR(1), -PRIMARY KEY (pk), -KEY (col_int_key), -KEY (col_varchar_key, col_int_key) -) -; -INSERT INTO t1 ( -col_int_key, col_int_nokey, col_varchar_key -) -VALUES -(2, NULL, 'w'), -(9, 7, 'm'), -(3, 9, 'm'), -(9, 7, 'k'), -(NULL, 4, 'r'), -(9, 2, 't'), -(3, 6, 'j'), -(8, 8, 'u'), -(8, NULL, 'h'), -(53, 5, 'o'), -(0, NULL, NULL), -(5, 6, 'k'), -(166, 188, 'e'), -(3, 2, 'n'), -(0, 1, 't'), -(1, 1, 'c'), -(9, 0, 'm'), -(5, 9, 'y'), -(6, NULL, 'f'), -(2, 4, 'd') -; -SELECT table2.col_varchar_key AS field1, -table2.col_int_nokey AS field2 -FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2 -ON (table2.col_varchar_key = table1.col_varchar_key ) ) -WHERE table1.pk = 6 -HAVING ( field2 ) IN -( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2 -FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2 -ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) ) -ORDER BY field2 -; -field1 field2 -t 1 -t 2 -drop table t1; -# -# BUG#53103: MTR test ps crashes in optimize_cond() -# when running with --debug -# -CREATE TABLE t1(track varchar(15)); -INSERT INTO t1 VALUES ('CAD'), ('CAD'); -PREPARE STMT FROM -"SELECT 1 FROM t1 - WHERE - track IN (SELECT track FROM t1 - GROUP BY track - HAVING track>='CAD')"; -EXECUTE STMT ; -1 -1 -1 -EXECUTE STMT ; -1 -1 -1 -DEALLOCATE PREPARE STMT; -DROP TABLE t1; -# End of BUG#53103 -# -# BUG#54511 - Assertion failed: cache != 0L in file -# sql_select.cc::sub_select_cache on HAVING -# -CREATE TABLE t1 (i int(11)); -CREATE TABLE t2 (c char(1)); -CREATE TABLE t3 (c char(1)); -INSERT INTO t1 VALUES (1), (2); -INSERT INTO t2 VALUES ('a'), ('b'); -INSERT INTO t3 VALUES ('x'), ('y'); -SELECT COUNT( i ),i -FROM t1 -HAVING ('c') -IN (SELECT t2.c FROM (t2 JOIN t3)); -COUNT( i ) i -DROP TABLE t1,t2,t3; -# End BUG#54511 -# -# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION... -# on subquery in FROM -# -CREATE TABLE t1 (a INTEGER); -CREATE TABLE t2 (b INTEGER); -INSERT INTO t2 VALUES (1); -explain SELECT a FROM ( -SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) -) table1; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table -SELECT a FROM ( -SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) -) table1; -a -DROP TABLE t1, t2; -# End BUG#56367 -# -# Bug#59833 - materialization=on/off leads to different result set -# when using IN -# -CREATE TABLE t1 ( -pk int NOT NULL, -f1 int DEFAULT NULL, -PRIMARY KEY (pk) -) ENGINE=MyISAM; -CREATE TABLE t2 ( -pk int NOT NULL, -f1 int DEFAULT NULL, -PRIMARY KEY (pk) -) ENGINE=MyISAM; -INSERT INTO t1 VALUES (10,0); -INSERT INTO t2 VALUES (10,0),(11,0); -explain SELECT * FROM t1 JOIN t2 USING (f1) -WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 const 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY t1 system NULL NULL NULL NULL 1 -SELECT * FROM t1 JOIN t2 USING (f1) -WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1); -f1 pk pk -DROP TABLE t1, t2; -# End Bug#59833 -# -# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT -# -CREATE TABLE t1 ( -col_varchar_key varchar(1) DEFAULT NULL, -col_varchar_nokey varchar(1) DEFAULT NULL, -KEY col_varchar_key (col_varchar_key)) -; -INSERT INTO t1 VALUES -('v','v'),('r','r'); -CREATE TABLE t2 ( -col_varchar_key varchar(1) DEFAULT NULL, -col_varchar_nokey varchar(1) DEFAULT NULL, -KEY col_varchar_key(col_varchar_key)) -; -INSERT INTO t2 VALUES -('r','r'),('c','c'); -CREATE VIEW v3 AS SELECT * FROM t2; -SELECT DISTINCT alias2.col_varchar_key -FROM t1 AS alias1 JOIN v3 AS alias2 -ON alias2.col_varchar_key = alias1.col_varchar_key -HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2) -; -col_varchar_key -r -DROP TABLE t1, t2; -DROP VIEW v3; -# End Bug#11852644 - -# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW -# INSTEAD OF NULL WHEN MATERIALIZATION ON - -CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY; -CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY; -INSERT INTO t2 VALUES (8),(7); -CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY; -INSERT INTO t3 VALUES (7); -SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3 -FROM t3 -LEFT JOIN t1 -ON t1.col_int_nokey -WHERE (194, 200) IN ( -SELECT SQ4_alias1.col_int_nokey, -SQ4_alias2.col_int_nokey -FROM t2 AS SQ4_alias1 -JOIN -t2 AS SQ4_alias2 -ON SQ4_alias2.col_int_nokey = 5 -) -GROUP BY field3 ; -MIN(t3.col_int_nokey) field3 -DROP TABLE t1; -DROP TABLE t2; -DROP TABLE t3; -CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM; -INSERT INTO t1 (f1, f2) VALUES (1, 1.789); -INSERT INTO t1 (f1, f2) VALUES (13, 1.454); -INSERT INTO t1 (f1, f2) VALUES (10, 1.668); -CREATE TABLE t2 LIKE t1; -INSERT INTO t2 VALUES (1, 1.789); -INSERT INTO t2 VALUES (13, 1.454); -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch=@optimizer_switch_local_default; -SET @@optimizer_switch='semijoin=on,materialization=on'; -EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 -SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); -COUNT(*) -2 -set @@optimizer_switch= @save_optimizer_switch; -DROP TABLE t1, t2; -CREATE TABLE t1 ( -pk int, -a varchar(1), -b varchar(4), -c varchar(4), -d varchar(4), -PRIMARY KEY (pk) -); -INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); -CREATE TABLE t2 LIKE t1; -INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch=@optimizer_switch_local_default; -SET @@optimizer_switch='semijoin=on,materialization=on'; -EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 func 1 -2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan -SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); -pk -2 -SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); -pk -2 -DROP TABLE t1, t2; -set optimizer_switch=@save_optimizer_switch; -# -# BUG#50019: Wrong result for IN-subquery with materialization -# -create table t1(i int); -insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -create table t2(i int); -insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -create table t3(i int); -insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); -i -1 -2 -3 -4 -set @save_optimizer_switch=@@optimizer_switch; -set session optimizer_switch='materialization=off,in_to_exists=on'; -select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5); -i -4 -3 -2 -1 -set session optimizer_switch=@save_optimizer_switch; -drop table t1, t2, t3; -create table t0 (a int); -insert into t0 values (0),(1),(2); -create table t1 (a int); -insert into t1 values (0),(1),(2); -explain select a, a in (select a from t1) from t0; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 3 -2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 -select a, a in (select a from t1) from t0; -a a in (select a from t1) -0 1 -1 1 -2 1 -prepare s from 'select a, a in (select a from t1) from t0'; -execute s; -a a in (select a from t1) -0 1 -1 1 -2 1 -update t1 set a=123; -execute s; -a a in (select a from t1) -0 0 -1 0 -2 0 -drop table t0, t1; -set optimizer_switch='firstmatch=on'; -# -# MWL#90, review feedback: check what happens when the subquery -# looks like candidate for MWL#90 checking at the first glance -# but then subselect_hash_sj_engine::init_permanent() discovers -# that it's not possible to perform duplicate removal for the -# selected datatypes, and so materialization isn't applicable after -# all. -# -set @blob_len = 1024; -set @suffix_len = @blob_len - @prefix_len; -create table t1_1024 (a1 blob(1024), a2 blob(1024)); -create table t2_1024 (b1 blob(1024), b2 blob(1024)); -insert into t1_1024 values -(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len))); -insert into t1_1024 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t1_1024 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_1024 values -(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len))); -insert into t2_1024 values -(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len))); -insert into t2_1024 values -(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len))); -explain select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1,1,1024), count(*) from t2_1024 where b1 > '0'); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 Using where -select left(a1,7), left(a2,7) from t1_1024 where (a1,3) in (select substring(b1,1,1024), count(*) from t2_1024 where b1 > '0'); -left(a1,7) left(a2,7) -1 - 01x 2 - 01x -drop table t1_1024, t2_1024; -# -# BUG##836491: Crash in Item_field::Item_field from add_ref_to_table_cond() with semijoin+materialization -# -CREATE TABLE t1 (c int, d varchar(1), KEY(d)) ; -INSERT INTO t1 VALUES (2,'x'),(2,'x'),(2,'j'),(2,'c'); -CREATE TABLE t2 (a int, d varchar(1)) ; -INSERT INTO t2 VALUES (1,'x'); -CREATE TABLE t3 (d varchar(1)) ; -INSERT INTO t3 VALUES ('x'),('x'),('j'),('c'); -SELECT t2.a, t1.c -FROM t1, t2 -WHERE t2.d IN ( SELECT d FROM t3 ) -AND t1.d = t2.d -GROUP BY 1 , 2; -a c -1 2 -drop table t1,t2,t3; -# -# BUG#836523: Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization -# -CREATE TABLE t1 (a varchar(1)); -INSERT INTO t1 VALUES ('a'),('a'); -CREATE TABLE t2 (a varchar(1)); -CREATE TABLE t3 (a int); -INSERT INTO t3 VALUES (1),(2); -CREATE TABLE t4 (a varchar(1)); -INSERT INTO t4 VALUES ('a'),('a'); -SELECT t1.a -FROM t1 -WHERE t1.a IN ( -SELECT t2.a -FROM t2, t3 -) -HAVING a IN ( -SELECT a -FROM t4 -); -a -DROP TABLE t1, t2, t3, t4; -# -# BUG#836507: Crash in setup_sj_materialization_part1() with semijoin+materialization -# -CREATE TABLE t1 (a int) ; -INSERT IGNORE INTO t1 VALUES (1),(1); -CREATE TABLE t2 (a int); -INSERT INTO t2 VALUES (1); -CREATE TABLE t3 (a int); -CREATE TABLE t4 (a int); -INSERT INTO t4 VALUES (2),(2); -CREATE TABLE t5 (a int); -INSERT INTO t5 VALUES (1); -SELECT * FROM t1 -WHERE (a) IN ( -SELECT t5.a -FROM ( -t2 -LEFT JOIN ( t3 , t4 ) -ON 1 = 1 -) -JOIN t5 -); -a -1 -1 -DROP TABLE t1,t2,t3,t4,t5; -# -# BUG#836532: Crash in Item_equal_fields_iterator::get_curr_field with semijoin+materialization -# -CREATE TABLE t2 (a int); -INSERT INTO t2 VALUES ('a'),('a'); -Warnings: -Warning 1366 Incorrect integer value: 'a' for column 'a' at row 1 -Warning 1366 Incorrect integer value: 'a' for column 'a' at row 2 -CREATE TABLE t4 (a varchar(1)); -INSERT INTO t4 VALUES ('m'),('o'); -CREATE TABLE t3 (a varchar(1) , b varchar(1) ) ; -INSERT INTO t3 VALUES ('b','b'); -CREATE TABLE t5 (a varchar(1), KEY (a)) ; -INSERT INTO t5 VALUES ('d'),('e'); -SELECT * -FROM t2 -WHERE t2.a = ALL ( -SELECT t4.a -FROM t4 -WHERE t4.a IN ( -SELECT t3.a -FROM t3 , t5 -WHERE ( t5.a = t3.b ) -) -); -a -0 -0 -DROP TABLE t2,t3,t4,t5; -# -# BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization -# -set @tmp_860300=@@optimizer_switch; -set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; -CREATE TABLE t1 (f2 int); -INSERT INTO t1 VALUES (9),(6); -CREATE TABLE t3 (f4 int); -CREATE TABLE t4 (f6 varchar(1)); -SELECT * -FROM t3 -WHERE 'h' IN (SELECT f6 -FROM t4 -WHERE 5 IN (SELECT f2 FROM t1) -GROUP BY t4.f6); -f4 -DROP TABLE t1,t3,t4; -set optimizer_switch=@tmp_860300; -# -# BUG#860535: Assertion `keypart_map' failed in mi_rkey with semijoin -# -set @tmp_860535=@@optimizer_switch; -set optimizer_switch='semijoin=on,materialization=on,loosescan=off,firstmatch=off'; -CREATE TABLE t1 (f3 int) ; -INSERT INTO t1 VALUES (1),(7); -CREATE TABLE t2 (f3 int , f5 varchar(1), KEY (f3)) ; -INSERT INTO t2 VALUES (7,'b'); -CREATE TABLE t3 (f3 int , f4 varchar(1) , KEY(f3), KEY (f4,f3)) ; -INSERT INTO t3 VALUES (1,'t'),(7,'g'); -CREATE TABLE t4 -SELECT f3 -FROM t1 WHERE ( f3 ) NOT IN ( -SELECT f3 -FROM t2 -WHERE f5 IN ( -SELECT f4 -FROM t3 -WHERE t3.f3 < 3 -) -); -SELECT * FROM t4; -f3 -1 -7 -DROP TABLE t1, t2, t3, t4; -set optimizer_switch=@tmp_860535; -# -# BUG#860553: Crash in create_ref_for_key with semijoin + materialization -# -CREATE TABLE t1 (f1 int) ; -CREATE TABLE t2 (f5 varchar(52) NOT NULL) ; -CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1)); -CREATE TABLE t4 (f3 int, KEY (f3)); -INSERT INTO t4 VALUES (17),(20); -CREATE TABLE t5 (f2 int); -INSERT INTO t5 VALUES (0),(0); -SELECT * -FROM t1 -JOIN t2 -ON ( t2.f5 ) IN ( -SELECT t3.f4 -FROM t3 -WHERE ( 1 ) IN ( -SELECT t4.f3 -FROM t4 , t5 -) -); -f1 f5 -DROP TABLE t1, t2, t3, t4, t5; -# This must be at the end: -set optimizer_switch=@subselect_sj_mat_tmp; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index f1a7af82a34..d884fd29687 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -988,5 +988,25 @@ WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1 DROP TABLE t1,t2,t3; +--echo # +--echo # BUG#869012: Wrong result with semijoin + materialization + AND in WHERE +--echo # +CREATE TABLE t1 (f3 varchar(1) , f4 varchar(1) ) engine=InnoDB; +INSERT IGNORE INTO t1 VALUES ('x','x'),('x','x'); +CREATE TABLE t2 ( f4 varchar(1) ) ; +INSERT IGNORE INTO t2 VALUES ('g'); +CREATE TABLE t3 (f4 varchar(1) ) Engine=InnoDB; +INSERT IGNORE INTO t3 VALUES ('x'); + +set @tmp_869012=@@optimizer_switch; +SET optimizer_switch='semijoin=on,materialization=on'; +SELECT * +FROM t1 , t2 +WHERE ( t1.f4 ) IN ( SELECT f4 FROM t3 ) +AND t2.f4 != t1.f3 ; +set optimizer_switch= @tmp_869012; + + +--echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; |