From: Guilhem Bichot Date: June 9 2010 1:46pm Subject: bzr commit into mysql-next-mr-bugfixing branch (guilhem:3189) Bug#53305 List-Archive: http://lists.mysql.com/commits/110636 X-Bug: 53305 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3648112753888035222==" --===============3648112753888035222== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/mysql_src/bzrrepos_new/opt-back-53305-2/ based on revid:oystein.grovlen@stripped 3189 Guilhem Bichot 2010-06-09 Fix for BUG#53305 "Duplicate weedout + join buffer (join cache --level=7,8) loses rows" When joining records, JOIN_CACHE_BKA_UNIQUE forgot to set the rowid, so some rows were wrongly said to have the same rowid and were wrongly deleted by Duplicate Weedout. @ mysql-test/r/subselect_sj2_jcl7.result result; all correct (except BUG 49129 which subselect_sj2_jcl6 also has). Without the code fix, "select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);" missed rows. @ mysql-test/r/subselect_sj_jcl6.result result, all correct @ mysql-test/r/subselect_sj_jcl7.result result, all correct. Without the code fix, the very final SELECT showed 3 rows "A","A","E" (missed rows). @ mysql-test/t/subselect_sj.test test for BUG#53305 @ mysql-test/t/subselect_sj2_jcl7.test run subselect_sj2 with optimizer_join_cache_level=7, like the existing subselect_sj2_jcl6. =7 is used instead of =8, to test regular buffers, because _jcl6 already tests incremental buffers. @ mysql-test/t/subselect_sj_jcl7.test run subselect_sj with optimizer_join_cache_level=7, like the existing subselect_sj_jcl6. @ sql/sql_join_cache.cc JOIN_CACHE_BKA_UNIQUE::join_matching_records(), unlike JOIN_CACHE_BNL::join_matching_records() and JOIN_CACHE_BKA::join_matching_records(), doesn't think of putting the record's position in the proper place (handler::ref) for do_sj_dups_weedout() to read. As a result, in the testcase (subselect_sj2_jcl6), where query plan is t3,t4,t1,t2, when we want to access t1: - we use JOIN_CACHE_BKA_UNIQUE (as join_cache_level is 7 or 8) - JOIN_CACHE_BKA_UNIQUE::join_matching_records() first goes through buffered records of t4 and for each such record, collects key (to be used for looking up into t1's index) and rowid: this is done by DsMrr_impl::dsmrr_fill_buffer(). - after that, JOIN_CACHE_BKA_UNIQUE::join_matching_records() scans this collection of keys; for each key, does a lookup in t1, finds a matching t1 record - then calls JOIN_CACHE::generate_full_extensions for this record, which calls do_sj_dups_weedout(). That function expects to find in t1's handler::ref the rowid of the matching t1 record. But that rowid is not there, because it was not saved there after the key lookup. - So do_sj_dups_weedout() reads the same rowid (an out-of-date value) from handler::ref for each record of t1, so several records of t1 are wrongly eliminated as duplicate (Duplicate Weedout eliminates rows with identical rowids, as expected). The fix: save rowid in t1's handler::ref after key lookup, like in JOIN_CACHE_BKA::join_matching_records(). added: mysql-test/r/subselect_sj2_jcl7.result mysql-test/r/subselect_sj_jcl7.result mysql-test/t/subselect_sj2_jcl7.test mysql-test/t/subselect_sj_jcl7.test modified: mysql-test/r/subselect_sj.result mysql-test/r/subselect_sj_jcl6.result mysql-test/t/subselect_sj.test mysql-test/t/subselect_sj2.test sql/sql_join_cache.cc === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-06-09 11:02:07 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-06-09 13:46:16 +0000 @@ -2804,3 +2804,50 @@ i 2 DROP TABLE t1, t2; # End of BUG#53060 +# +# Bug#53305 "Duplicate weedout + join buffer (join cache --level=7,8) loses rows" +# +create table t1 (uid int, fid int, index(uid)); +insert into t1 values +(1,1), (1,2), (1,3), (1,4), +(2,5), (2,6), (2,7), (2,8), +(3,1), (3,2), (3,9); +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values +(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), +(6, "F"), (7, "G"), (8, "H"), (9, "I"); +create table t3 (uid int, fid int, index(uid)); +insert into t3 values +(1,1), (1,2), (1,3),(1,4), +(2,5), (2,6), (2,7), (2,8), +(3,1), (3,2), (3,9); +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values +(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), +(6, "F"), (7, "G"), (8, "H"), (9, "I"); +set optimizer_switch="materialization=off,loosescan=off,firstmatch=off"; +explain select name from t2, t1 +where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) +and t2.uid=t1.fid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index +1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using index condition; End temporary +1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer +select name from t2, t1 +where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) +and t2.uid=t1.fid; +name +A +A +B +B +C +D +E +F +G +H +I +set optimizer_switch=default; +drop table t1,t2,t3,t4; === added file 'mysql-test/r/subselect_sj2_jcl7.result' --- a/mysql-test/r/subselect_sj2_jcl7.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/subselect_sj2_jcl7.result 2010-06-09 13:46:16 +0000 @@ -0,0 +1,723 @@ +set optimizer_join_cache_level=7; +show variables like 'optimizer_join_cache_level'; +Variable_name Value +optimizer_join_cache_level 7 +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; +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 +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 t1 ALL NULL NULL NULL NULL 3 Using where; Materialize; Scan +1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer +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; +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 10 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer +select * from t3 where b in (select a from t1); +a b pk1 pk2 pk3 +1 1 1 1 1 +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= 8192; +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 t0 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan +1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer +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 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 +1 PRIMARY t2 index b b 5 NULL 10 Using index; Materialize +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= 8192; +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 it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer +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 it ALL NULL NULL NULL NULL 32 Materialize +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 it ALL NULL NULL NULL NULL 22 Materialize; Scan +1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer +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 it ALL NULL NULL NULL NULL 52 Materialize +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 t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer +1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index +1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary +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) +); +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; Using MRR; Materialize; Scan +1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; Using join buffer +1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition(BKA); Using where; Using join buffer +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 t2 ALL CountryCode NULL NULL NULL 545 Using where; Materialize +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; +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; +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 t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End temporary; Using join buffer +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`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t0`.`a`) and (`test`.`t2`.`a` = `test`.`t0`.`a`)) +update t1 set a=3, b=11 where a=4; +update t2 set b=11 where a=3; + +# The following query gives wrong result due to Bug#49129 +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 +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; Using join buffer +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 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 t3 index a a 5 NULL 30000 Using index; Materialize +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 (`test`.`B`.`A`,(((`test`.`B`.`A`) in t10 on PRIMARY))))) where 1 +t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)" +explAin extended +select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10)); +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 t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join Buffer +2 DEPENDENT SUBQUERY t10 unique_suBquery PRIMARY PRIMARY 4 func 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And (`test`.`t2`.`A`,(((`test`.`t2`.`A`) in t10 on PRIMARY))))) where 1 +we shouldn't flatten if we're going to get a join of > MAX_TABLES. +explain select * from +t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09, +t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19, +t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29, +t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39, +t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49 +where +s00.a in ( +select m00.a from +t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09, +t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY s01 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s02 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s03 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s04 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s05 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s06 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s07 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s08 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s09 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s10 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s11 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s12 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s13 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s14 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s15 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s16 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s17 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s18 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s19 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s20 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s21 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s22 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s23 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s24 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s25 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s26 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s27 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s28 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s29 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s30 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s31 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s32 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s33 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s34 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s35 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s36 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s37 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s38 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s39 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s40 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s41 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s42 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s43 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s44 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s45 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s46 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s47 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s48 ALL NULL NULL NULL NULL 3 Using join buffer +1 PRIMARY s49 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer +2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer +select * from +t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) +where t1.a < 5; +a b a b +0 0 0 0 +1 1 1 1 +2 2 2 2 +prepare s1 from +' select * from + t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) + where t1.a < 5'; +execute s1; +a b a b +0 0 0 0 +1 1 1 1 +2 2 2 2 +execute s1; +a b a b +0 0 0 0 +1 1 1 1 +2 2 2 2 +insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B; +explain extended select * from t1 where a in (select pk from t10 where pk<3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t10`.`pk`) and (`test`.`t10`.`pk` < 3)) +drop table t0, t1, t2; +drop table t10, t11, t12; +# +# Check that subqueries with outer joins or straight_join work for +# different permutations of const and non-const tables. (Ref. Bug#46692) +# +CREATE TABLE t1 (i INTEGER); +CREATE TABLE t2 (i INTEGER); +CREATE TABLE t3 (i INTEGER); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 0 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 0 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 0 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 1 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 1 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 1 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 2 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 2 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +0 2 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (0); +DELETE FROM t2; +INSERT INTO t1 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 0 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 0 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 system NULL NULL NULL NULL 1 +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 0 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 1 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 1 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 1 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 2 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 0 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 0 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 2 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t3) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +1 2 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (0); +DELETE FROM t2; +INSERT INTO t1 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 0 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 0 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 0 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Materialize +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 1 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 1 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 1 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Materialize +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Start materialize; Scan +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +1 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t2) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 2 0 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 FirstMatch(t1); Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 0 const row not found +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize; Scan +1 PRIMARY t3 ALL NULL NULL NULL NULL 0 End materialize; Using join buffer +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 0 Using where; End materialize; Using join buffer +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (2); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 2 1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize; Scan +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize; Scan +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 End materialize; Using join buffer +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; End temporary; Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +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 * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; FirstMatch +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (1); +SELECT (SELECT COUNT(*) from t1), +(SELECT COUNT(*) from t2), +(SELECT COUNT(*) from t3); +(SELECT COUNT(*) from t1) (SELECT COUNT(*) from t2) (SELECT COUNT(*) from t3) +2 2 2 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +2 +1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +2 +1 +EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 End materialize; Using join buffer +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +2 +1 +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using join buffer +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start materialize +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i); +i +EXPLAIN SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize; Using join buffer +SELECT * FROM t1 WHERE (11) IN +(SELECT t3.i FROM t2 STRAIGHT_JOIN t3); +i +INSERT INTO t3 VALUES (0); +DELETE FROM t3; +INSERT INTO t2 VALUES (0); +DELETE FROM t2; +INSERT INTO t1 VALUES (0); +DROP TABLE t1, t2, t3; +# +# Bug#48868: Left outer join in subquery causes segmentation fault in +# make_join_select. +# +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +CREATE TABLE t2 (i INTEGER); +INSERT INTO t2 VALUES(1); +CREATE TABLE t3 (i INTEGER); +INSERT INTO t3 VALUES (1); +INSERT INTO t3 VALUES (2); +SELECT * FROM t1 WHERE (t1.i) IN +(SELECT t2.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i); +i +1 +DROP TABLE t1, t2, t3; + +BUG#37120 optimizer_switch allowable values not according to specification + +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='materialization=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=off,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='semijoin=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='loosescan=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='semijoin=off,materialization=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='materialization=off,semijoin=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=off,semijoin=off,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='semijoin=off,materialization=off,loosescan=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=off,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='semijoin=off,loosescan=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=off,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; +set optimizer_switch='materialization=off,loosescan=off'; +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=off,semijoin=on,loosescan=off,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +set optimizer_switch='default'; + +Bug#37899: Wrongly checked optimization prerequisite caused failed +assertion. + +CREATE TABLE t1 ( +`pk` int(11), +`varchar_nokey` varchar(5) +); +INSERT INTO t1 VALUES +(1,'qk'),(2,'j'),(3,'aew'); +SELECT * +FROM t1 +WHERE varchar_nokey IN ( +SELECT +varchar_nokey +FROM +t1 +) XOR pk = 30; +pk varchar_nokey +1 qk +2 j +3 aew +drop table t1; +# +# BUG#41842: Semi-join materialization strategy crashes when the upper query has HAVING +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +time_key time NOT NULL, +datetime_key datetime NOT NULL, +datetime_nokey datetime NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY time_key (time_key), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,0, '00:16:10','2008-09-03 14:25:40','2008-09-03 14:25:40','h','h'), +(2,7, '00:00:00','2001-01-13 00:00:00','2001-01-13 00:00:00','',''), +(3,0, '00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(4,2, '16:29:24','2000-10-16 01:39:08','2000-10-16 01:39:08','w','w'), +(5,1, '09:23:32','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'), +(6,3, '00:00:00','2007-12-02 00:00:00','2007-12-02 00:00:00','o','o'), +(7,3, '00:00:00','2008-09-11 00:00:00','2008-09-11 00:00:00','',''), +(8,0, '13:59:04','0000-00-00 00:00:00','0000-00-00 00:00:00','s','s'), +(9,7, '09:01:06','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'), +(10,5,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','n','n'), +(11,0,'21:06:46','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(12,2,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','',''), +(13,6,'14:45:34','2003-07-28 02:34:08','2003-07-28 02:34:08','w','w'), +(14,1,'15:04:12','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), +(15,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','x','x'), +(16,0,'15:55:23','2004-03-17 00:32:27','2004-03-17 00:32:27','p','p'), +(17,1,'16:30:00','2004-12-27 19:20:00','2004-12-27 19:20:00','d','d'), +(18,0,'00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'), +(19,0,'14:13:26','2008-11-09 05:53:48','2008-11-09 05:53:48','o','o'), +(20,0,'00:00:00','2009-10-11 06:58:04','2009-10-11 06:58:04','k','k'); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +int_nokey int(11) NOT NULL, +time_key time NOT NULL, +datetime_key datetime NOT NULL, +datetime_nokey datetime NOT NULL, +varchar_key varchar(1) NOT NULL, +varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY time_key (time_key), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES +(10,0,'19:39:13','0000-00-00 00:00:00','0000-00-00 00:00:00','g','g'), +(11,8,'03:43:53','0000-00-00 00:00:00','0000-00-00 00:00:00','b','b'); +SELECT OUTR.datetime_nokey AS X FROM t1 AS OUTR +WHERE +OUTR.varchar_nokey IN (SELECT +INNR . varchar_nokey AS Y +FROM t2 AS INNR +WHERE +INNR . datetime_key >= INNR . time_key OR +INNR . pk = INNR . int_nokey +) +AND OUTR . varchar_nokey <= 'w' +HAVING X > '2012-12-12'; +X +drop table t1, t2; + +Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order +with semijoin=on" + +CREATE TABLE t1 ( +varchar_key varchar(1) DEFAULT NULL, +KEY varchar_key (varchar_key) +); +CREATE TABLE t2 ( +varchar_key varchar(1) DEFAULT NULL, +KEY varchar_key (varchar_key) +); +INSERT INTO t2 VALUES +(NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'), +('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'), +('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'), +('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'), +('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'), +('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'), +('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'), +('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z'); +CREATE TABLE t3 ( +varchar_key varchar(1) DEFAULT NULL, +KEY varchar_key (varchar_key) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t3 VALUES +(NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'), +('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y'); +SELECT varchar_key FROM t3 +WHERE (SELECT varchar_key FROM t3 +WHERE (varchar_key,varchar_key) +IN (SELECT t1.varchar_key, t2 .varchar_key +FROM t1 RIGHT JOIN t2 ON t1.varchar_key +) +); +varchar_key +DROP TABLE t1, t2, t3; +# +# Bug#46556 Returning incorrect, empty results for some IN subqueries +# w/semijoin=on +# +CREATE TABLE t0 ( +pk INTEGER, +vkey VARCHAR(1), +vnokey VARCHAR(1), +PRIMARY KEY (pk), +KEY vkey(vkey) +); +INSERT INTO t0 +VALUES (1,'g','g'), (2,'v','v'), (3,'t','t'), (4,'u','u'), (5,'n','n'); +EXPLAIN EXTENDED SELECT vkey FROM t0 WHERE pk IN +(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t0 ALL PRIMARY NULL NULL NULL 5 100.00 +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where; Using join buffer +1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1) +Warnings: +Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`)) +SELECT vkey FROM t0 WHERE pk IN +(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey); +vkey +g +v +t +u +n +DROP TABLE t0; +# End of bug#46556 + +Bug#48834: Procedure with view + subquery + semijoin=on +crashes on second call. + +SET SESSION optimizer_switch ='semijoin=on'; +CREATE TABLE t1 ( t1field integer, primary key (t1field)); +CREATE TABLE t2 ( t2field integer, primary key (t2field)); +CREATE VIEW v1 AS +SELECT t1field as v1field +FROM t1 A +WHERE A.t1field IN (SELECT t1field FROM t2 ); +CREATE VIEW v2 AS +SELECT t2field as v2field +FROM t2 A +WHERE A.t2field IN (SELECT t2field FROM t2 ); +CREATE PROCEDURE p1 () +BEGIN +SELECT v1field +FROM v1 +WHERE v1field IN ( SELECT v2field as vf_inner FROM v2 ); +END| +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (2),(3),(4); +CALL p1; +v1field +2 +3 +CALL p1; +v1field +2 +3 +DROP TABLE t1,t2; +DROP VIEW v1,v2; +DROP PROCEDURE p1; +set SESSION optimizer_switch='default'; +# End of BUG#48834 +# +# Bug#46692 "Crash occurring on queries with nested FROM subqueries +# using materialization." +# +CREATE TABLE t1 ( +pk INTEGER PRIMARY KEY, +int_key INTEGER, +KEY int_key(int_key) +); +INSERT INTO t1 VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1); +CREATE TABLE t2 ( +pk INTEGER PRIMARY KEY, +int_key INTEGER, +KEY int_key(int_key) +); +INSERT INTO t2 VALUES (1,7),(2,2); +SELECT * FROM t1 WHERE (140, 4) IN +(SELECT t2.int_key, t2 .pk FROM t2 STRAIGHT_JOIN t1 ON t2.int_key); +pk int_key +DROP TABLE t1, t2; +# +# Bug#42353 "SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query +# causes crash." +# +CREATE TABLE t1 ( +pk INTEGER PRIMARY KEY, +int_nokey INTEGER, +int_key INTEGER, +date_key DATE, +datetime_nokey DATETIME, +varchar_nokey VARCHAR(1) +); +CREATE TABLE t2 ( +date_nokey DATE +); +CREATE TABLE t3 ( +pk INTEGER PRIMARY KEY, +int_nokey INTEGER, +date_key date, +varchar_key VARCHAR(1), +varchar_nokey VARCHAR(1), +KEY date_key (date_key) +); +SELECT date_key FROM t1 +WHERE (int_key, int_nokey) +IN (SELECT t3.int_nokey, t3.pk +FROM t2 LEFT JOIN t3 ON (t2.date_nokey < t3.date_key) +WHERE t3.varchar_key <= t3.varchar_nokey OR t3.int_nokey <= t3.pk +) +AND (varchar_nokey <> 'f' OR NOT int_key < 7); +date_key +# +# Bug#45933 "Crash in optimize_semijoin_nests on JOIN in subquery +# + AND in outer query". +# +INSERT INTO t1 VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w'), +(11,7,0,'0000-00-00','0000-00-00 00:00:00','s'), +(12,4,0,'2003-07-14','2006-09-14 04:01:02','y'), +(13,0,4,'2002-07-25','0000-00-00 00:00:00','c'), +(14,1,8,'2007-07-03','0000-00-00 00:00:00','q'), +(15,6,5,'2001-11-12','0000-00-00 00:00:00',''), +(16,2,9,'0000-00-00','0000-00-00 00:00:00','j'), +(29,9,1,'0000-00-00','2003-08-11 00:00:00','m'); +INSERT INTO t3 VALUES (1,9,'0000-00-00','b','b'), +(2,2,'2002-09-17','h','h'); +SELECT t1.varchar_nokey FROM t1 JOIN t3 ON t1.datetime_nokey +WHERE t1.varchar_nokey +IN (SELECT varchar_nokey FROM t1 +WHERE (pk) +IN (SELECT t3.int_nokey +FROM t3 LEFT JOIN t1 ON t1.varchar_nokey +WHERE t3.date_key BETWEEN '2008-06-07' AND '2006-06-26' + ) +); +varchar_nokey +DROP TABLE t1, t2, t3; +# +# Bug#45219 "Crash on SELECT DISTINCT query containing a +# LEFT JOIN in subquery" +# +CREATE TABLE t1 ( +pk INTEGER NOT NULL, +int_nokey INTEGER NOT NULL, +datetime_key DATETIME NOT NULL, +varchar_key VARCHAR(1) NOT NULL, +PRIMARY KEY (pk), +KEY datetime_key (datetime_key), +KEY varchar_key (varchar_key) +); +INSERT INTO t1 VALUES +(1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'), +(3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'), +(5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'), +(7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'), +(9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'), +(11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'), +(13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'), +(15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'), +(17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''), +(19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u'); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES +(10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'), +(12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'), +(14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''), +(16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'), +(18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'), +(20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'), +(22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''), +(24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'), +(26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'), +(28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x'); +CREATE TABLE t3 LIKE t1; +INSERT INTO t3 VALUES +(10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51',''); +SELECT DISTINCT datetime_key FROM t1 +WHERE (int_nokey, pk) +IN (SELECT t3.pk, t3.pk FROM t2 LEFT JOIN t3 ON t3.varchar_key) +AND pk = 9; +datetime_key +DROP TABLE t1, t2, t3; +# +# Bug#46550 Azalea returning duplicate results for some IN subqueries +# w/ semijoin=on +# +DROP TABLE IF EXISTS t0, t1, t2; +CREATE TABLE t0 ( +int_key int(11) DEFAULT NULL, +varchar_key varchar(1) DEFAULT NULL, +varchar_nokey varchar(1) DEFAULT NULL, +KEY int_key (int_key), +KEY varchar_key (varchar_key,int_key) +); +INSERT INTO t0 VALUES +(1,'m','m'), +(40,'h','h'), +(1,'r','r'), +(1,'h','h'), +(9,'x','x'), +(NULL,'q','q'), +(NULL,'k','k'), +(7,'l','l'), +(182,'k','k'), +(202,'a','a'), +(7,'x','x'), +(6,'j','j'), +(119,'z','z'), +(4,'d','d'), +(5,'h','h'), +(1,'u','u'), +(3,'q','q'), +(7,'a','a'), +(3,'e','e'), +(6,'l','l'); +CREATE TABLE t1 ( +int_key int(11) DEFAULT NULL, +varchar_key varchar(1) DEFAULT NULL, +varchar_nokey varchar(1) DEFAULT NULL, +KEY int_key (int_key), +KEY varchar_key (varchar_key,int_key) +); +INSERT INTO t1 VALUES (7,NULL,NULL),(4,'x','x'); +CREATE TABLE t2 ( +int_key int(11) DEFAULT NULL, +varchar_key varchar(1) DEFAULT NULL, +varchar_nokey varchar(1) DEFAULT NULL, +KEY int_key (int_key), +KEY varchar_key (varchar_key,int_key) +); +INSERT INTO t2 VALUES (123,NULL,NULL); +SELECT int_key +FROM t0 +WHERE varchar_nokey IN ( +SELECT t1 .varchar_key from t1 +); +int_key +9 +7 +SELECT t0.int_key +FROM t0 +WHERE t0.varchar_nokey IN ( +SELECT t1_1 .varchar_key +FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key +); +int_key +9 +7 +SELECT t0.int_key +FROM t0, t2 +WHERE t0.varchar_nokey IN ( +SELECT t1_1 .varchar_key +FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key +); +int_key +9 +7 +DROP TABLE t0, t1, t2; +# End of bug#46550 +# +# Bug #46744 Crash in optimize_semijoin_nests on empty view +# with limit and procedure. +# +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v1; +DROP PROCEDURE IF EXISTS p1; +CREATE TABLE t1 ( f1 int ); +CREATE TABLE t2 ( f1 int ); +insert into t2 values (5), (7); +CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2; +create procedure p1() +select COUNT(*) +FROM v1 WHERE f1 IN +(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1)); +SET SESSION optimizer_switch = 'semijoin=on'; +CALL p1(); +COUNT(*) +0 +SET SESSION optimizer_switch = 'semijoin=off'; +CALL p1(); +COUNT(*) +0 +drop table t1, t2; +drop view v1; +drop procedure p1; +set SESSION optimizer_switch='default'; +# End of bug#46744 + +Bug #48073 Subquery on char columns from view crashes Mysql + +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v1; +CREATE TABLE t1 ( +city VARCHAR(50) NOT NULL, +country_id SMALLINT UNSIGNED NOT NULL +); +INSERT INTO t1 VALUES +('Batna',2), +('Bchar',2), +('Skikda',2), +('Tafuna',3), +('Algeria',2) ; +CREATE TABLE t2 ( +country_id SMALLINT UNSIGNED NOT NULL, +country VARCHAR(50) NOT NULL +); +INSERT INTO t2 VALUES +(2,'Algeria'), +(3,'American Samoa') ; +CREATE VIEW v1 AS +SELECT country_id, country +FROM t2 +WHERE LEFT(country,1) = "A" +; +SELECT city, country_id +FROM t1 +WHERE city IN ( +SELECT country +FROM t2 +WHERE LEFT(country, 1) = "A" +); +city country_id +Algeria 2 +SELECT city, country_id +FROM t1 +WHERE city IN ( +SELECT country +FROM v1 +); +city country_id +Algeria 2 +drop table t1, t2; +drop view v1; +# End of bug#48073 + +Bug#49097 subquery with view generates wrong result with +non-prepared statement + +DROP TABLE IF EXISTS t1, t2; +DROP VIEW IF EXISTS v1; +CREATE TABLE t1 ( +city VARCHAR(50) NOT NULL, +country_id SMALLINT UNSIGNED NOT NULL +); +INSERT INTO t1 VALUES +('Batna',2), +('Bchar',2), +('Skikda',2), +('Tafuna',3), +('Algeria',2) ; +CREATE TABLE t2 ( +country_id SMALLINT UNSIGNED NOT NULL, +country VARCHAR(50) NOT NULL +); +INSERT INTO t2 VALUES +(2,'Algeria'), +(3,'XAmerican Samoa') ; +CREATE VIEW v1 AS +SELECT country_id, country +FROM t2 +WHERE LEFT(country,1) = "A" +; +SELECT city, country_id +FROM t1 +WHERE country_id IN ( +SELECT country_id +FROM t2 +WHERE LEFT(country,1) = "A" +); +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +SELECT city, country_id +FROM t1 +WHERE country_id IN ( +SELECT country_id +FROM v1 +); +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +PREPARE stmt FROM +" +SELECT city, country_id +FROM t1 +WHERE country_id IN ( + SELECT country_id + FROM v1 +); +"; +execute stmt; +city country_id +Batna 2 +Bchar 2 +Skikda 2 +Algeria 2 +deallocate prepare stmt; +drop table t1, t2; +drop view v1; +# End of Bug#49097 +# +# Bug#49198 Wrong result for second call of procedure +# with view in subselect. +# +CREATE TABLE t1 (t1field integer, primary key (t1field)); +CREATE TABLE t2 (t2field integer, primary key (t2field)); +CREATE TABLE t3 (t3field integer, primary key (t3field)); +CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t2 VALUES(1),(2); +INSERT INTO t3 VALUES(1),(2); +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2); +"; +EXECUTE stmt; +t1field +1 +2 +EXECUTE stmt; +t1field +1 +2 +PREPARE stmt FROM +" +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT * FROM v2) + AND t1field IN (SELECT * FROM v3) +"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +DROP TABLE t1, t2, t3; +DROP VIEW v2, v3; +# End of Bug#49198 +# +# Bug#48623 Multiple subqueries are optimized incorrectly +# +CREATE TABLE ot(val VARCHAR(10)); +CREATE TABLE it1(val VARCHAR(10)); +CREATE TABLE it2(val VARCHAR(10)); +INSERT INTO ot VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO it1 VALUES('aaa'), ('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +INSERT INTO it2 VALUES('aaa'), ('bbb'), ('eee'), ('mmm'), ('ppp'); +EXPLAIN +SELECT * +FROM ot +WHERE ot.val IN (SELECT it1.val FROM it1 +WHERE it1.val LIKE 'a%' OR it1.val LIKE 'e%') +AND ot.val IN (SELECT it2.val FROM it2 +WHERE it2.val LIKE 'a%' OR it2.val LIKE 'e%'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ot ALL NULL NULL NULL NULL 5 +1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(ot); Using join buffer +1 PRIMARY it1 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(it2); Using join buffer +SELECT * +FROM ot +WHERE ot.val IN (SELECT it1.val FROM it1 +WHERE it1.val LIKE 'a%' OR it1.val LIKE 'e%') +AND ot.val IN (SELECT it2.val FROM it2 +WHERE it2.val LIKE 'a%' OR it2.val LIKE 'e%'); +val +aaa +eee +DROP TABLE ot; +DROP TABLE it1; +DROP TABLE it2; +# End of Bug#48623 +# +# Bug #51487 Assertion failure when semi-join flattening occurs +# for a subquery in HAVING +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,10),(2,11),(1,13); +CREATE TABLE t2 AS SELECT * FROM t1; +CREATE TABLE t3 AS SELECT * FROM t1; +SELECT COUNT(*) FROM t1 +GROUP BY t1.a +HAVING t1.a IN (SELECT t3.a FROM t3 +WHERE t3.b IN (SELECT b FROM t2 WHERE t2.a=t1.a)); +COUNT(*) +2 +1 +DROP TABLE t1, t2, t3; +# End of Bug#51487 +# +# BUG#38075: Wrong result: rows matching a subquery with outer join not returned +# +DROP TABLE IF EXISTS ot1, it1, it2; +CREATE TABLE it2 ( +int_key int(11) NOT NULL, +datetime_key datetime NOT NULL, +KEY int_key (int_key), +KEY datetime_key (datetime_key) +); +INSERT INTO it2 VALUES +(5,'2002-04-10 14:25:30'), (0,'0000-00-00 00:00:00'), +(0,'2006-09-14 04:01:02'), (4,'0000-00-00 00:00:00'), +(8,'0000-00-00 00:00:00'), (5,'0000-00-00 00:00:00'), +(9,'0000-00-00 00:00:00'), (8,'2007-04-01 11:04:17'), +(1,'0000-00-00 00:00:00'), (7,'2009-01-12 00:00:00'), +(0,'2009-06-05 00:00:00'), (3,'2006-02-14 18:06:35'), +(5,'2006-02-21 07:08:16'), (0,'0000-00-00 00:00:00'), +(7,'0000-00-00 00:00:00'), (0,'0000-00-00 00:00:00'), +(0,'2007-02-13 00:00:00'), (1,'0000-00-00 00:00:00'), +(0,'0000-00-00 00:00:00'), (1,'2003-08-11 00:00:00'); +CREATE TABLE ot1 ( +int_nokey int(11) NOT NULL, +int_key int(11) NOT NULL, +KEY int_key (int_key) +); +INSERT INTO ot1 VALUES +(5,0), (3,0), (0,2), (3,0), (1,3), (0,0), (1,7), (7,0), (1,7), (0,7), +(0,9), (8,2), (4,4), (9,3), (0,9), (2,5), (0,5), (8,0), (5,8), (1,5); +CREATE TABLE it1 ( +int_nokey int(11) NOT NULL, +int_key int(11) NOT NULL, +KEY int_key (int_key) +); +INSERT INTO it1 VALUES +(9,5), (0,4); +SELECT int_key FROM ot1 +WHERE int_nokey IN (SELECT it2.int_key +FROM it1 LEFT JOIN it2 ON it2.datetime_key); +int_key +0 +0 +0 +0 +0 +0 +2 +2 +3 +5 +5 +7 +7 +7 +8 +9 +9 +EXPLAIN +SELECT int_key FROM ot1 +WHERE int_nokey IN (SELECT it2.int_key +FROM it1 LEFT JOIN it2 ON it2.datetime_key); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY it1 index NULL int_key 4 NULL 2 Using index; Start temporary +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer +1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer +DROP TABLE ot1, it1, it2; +# End of BUG#38075 +# +# BUG#50089: Second call of procedure with view in subselect crashes server +# +CREATE TABLE t1(t1field INTEGER, PRIMARY KEY(t1field)); +CREATE VIEW v1 AS +SELECT t1field AS v1field +FROM t1 a +WHERE a.t1field IN (SELECT t1field FROM t1); +INSERT INTO t1 VALUES(1),(2); +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT v1field FROM v1); +t1field +1 +2 +EXPLAIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT v1field FROM v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index +CREATE PROCEDURE p1() +BEGIN +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT v1field FROM v1); +END| +CALL p1; +t1field +1 +2 +CALL p1; +t1field +1 +2 +PREPARE stmt FROM +" +SELECT t1field +FROM t1 +WHERE t1field IN (SELECT v1field FROM v1); +"; +EXECUTE stmt; +t1field +1 +2 +EXECUTE stmt; +t1field +1 +2 +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; +# End of BUG#50089 +# +# Bug#45191: Incorrectly initialized semi-join led to a wrong result. +# +CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL, +EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); +CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL, +PNAME CHAR(20), PTYPE CHAR(6), +BUDGET DECIMAL(9), +CITY CHAR(15)); +CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL, +PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5)); +INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); +INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); +INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); +INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); +INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); +INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale'); +INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna'); +INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa'); +INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale'); +INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna'); +INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale'); +INSERT INTO WORKS VALUES ('E1','P1',40); +INSERT INTO WORKS VALUES ('E1','P2',20); +INSERT INTO WORKS VALUES ('E1','P3',80); +INSERT INTO WORKS VALUES ('E1','P4',20); +INSERT INTO WORKS VALUES ('E1','P5',12); +INSERT INTO WORKS VALUES ('E1','P6',12); +INSERT INTO WORKS VALUES ('E2','P1',40); +INSERT INTO WORKS VALUES ('E2','P2',80); +INSERT INTO WORKS VALUES ('E3','P2',20); +INSERT INTO WORKS VALUES ('E4','P2',20); +INSERT INTO WORKS VALUES ('E4','P4',40); +INSERT INTO WORKS VALUES ('E4','P5',80); +set optimizer_switch='default,materialization=off'; +explain SELECT EMPNUM, EMPNAME +FROM STAFF +WHERE EMPNUM IN +(SELECT EMPNUM FROM WORKS +WHERE PNUM IN +(SELECT PNUM FROM PROJ)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY STAFF ALL NULL NULL NULL NULL 5 +1 PRIMARY PROJ ALL NULL NULL NULL NULL 6 +1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF) +SELECT EMPNUM, EMPNAME +FROM STAFF +WHERE EMPNUM IN +(SELECT EMPNUM FROM WORKS +WHERE PNUM IN +(SELECT PNUM FROM PROJ)); +EMPNUM EMPNAME +E1 Alice +E2 Betty +E3 Carmen +E4 Don +set optimizer_switch='default'; +drop table STAFF,WORKS,PROJ; +# End of bug#45191 +# +# BUG#53060: LooseScan semijoin strategy does not return all rows +# +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j)); +INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); +EXPLAIN +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer +SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); +i +1 +2 +DROP TABLE t1, t2; +# End of BUG#53060 +# +# Bug#53305 "Duplicate weedout + join buffer (join cache --level=7,8) loses rows" +# +create table t1 (uid int, fid int, index(uid)); +insert into t1 values +(1,1), (1,2), (1,3), (1,4), +(2,5), (2,6), (2,7), (2,8), +(3,1), (3,2), (3,9); +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values +(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), +(6, "F"), (7, "G"), (8, "H"), (9, "I"); +create table t3 (uid int, fid int, index(uid)); +insert into t3 values +(1,1), (1,2), (1,3),(1,4), +(2,5), (2,6), (2,7), (2,8), +(3,1), (3,2), (3,9); +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values +(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), +(6, "F"), (7, "G"), (8, "H"), (9, "I"); +set optimizer_switch="materialization=off,loosescan=off,firstmatch=off"; +explain select name from t2, t1 +where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) +and t2.uid=t1.fid; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index +1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using index condition(BKA); End temporary; Using join buffer +1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer +select name from t2, t1 +where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) +and t2.uid=t1.fid; +name +A +A +B +B +C +D +E +F +G +H +I +set optimizer_switch=default; +drop table t1,t2,t3,t4; +set optimizer_join_cache_level=default; +show variables like 'optimizer_join_cache_level'; +Variable_name Value +optimizer_join_cache_level 1 === modified file 'mysql-test/t/subselect_sj.test' --- a/mysql-test/t/subselect_sj.test 2010-06-09 11:02:07 +0000 +++ b/mysql-test/t/subselect_sj.test 2010-06-09 13:46:16 +0000 @@ -1067,7 +1067,6 @@ drop table STAFF,WORKS,PROJ; --echo # End of bug#45191 - --echo # --echo # BUG#53060: LooseScan semijoin strategy does not return all rows --echo # @@ -1084,3 +1083,41 @@ SELECT * FROM t1 WHERE (i) IN (SELECT i DROP TABLE t1, t2; --echo # End of BUG#53060 +--echo # +--echo # Bug#53305 "Duplicate weedout + join buffer (join cache --level=7,8) loses rows" +--echo # + +create table t1 (uid int, fid int, index(uid)); +insert into t1 values + (1,1), (1,2), (1,3), (1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +create table t3 (uid int, fid int, index(uid)); +insert into t3 values + (1,1), (1,2), (1,3),(1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +set optimizer_switch="materialization=off,loosescan=off,firstmatch=off"; + +explain select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +set optimizer_switch=default; +drop table t1,t2,t3,t4; === modified file 'mysql-test/t/subselect_sj2.test' --- a/mysql-test/t/subselect_sj2.test 2010-06-07 11:46:37 +0000 +++ b/mysql-test/t/subselect_sj2.test 2010-06-09 13:46:16 +0000 @@ -515,7 +515,7 @@ t1.b=t2.b); update t1 set a=3, b=11 where a=4; update t2 set b=11 where a=3; -if (`select @@optimizer_join_cache_level=6`) +if (`select @@optimizer_join_cache_level in (6,7)`) { --echo --echo # The following query gives wrong result due to Bug#49129 === added file 'mysql-test/t/subselect_sj2_jcl7.test' --- a/mysql-test/t/subselect_sj2_jcl7.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/subselect_sj2_jcl7.test 2010-06-09 13:46:16 +0000 @@ -0,0 +1,11 @@ +# +# Run subselect_sj2.test with BKA_UNIQUE enabled +# + +set optimizer_join_cache_level=7; +show variables like 'optimizer_join_cache_level'; + +--source t/subselect_sj2.test + +set optimizer_join_cache_level=default; +show variables like 'optimizer_join_cache_level'; === added file 'mysql-test/t/subselect_sj_jcl7.test' --- a/mysql-test/t/subselect_sj_jcl7.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/subselect_sj_jcl7.test 2010-06-09 13:46:16 +0000 @@ -0,0 +1,11 @@ +# +# Run subselect_sj.test with BKA_UNIQUE enabled +# + +set optimizer_join_cache_level=7; +show variables like 'optimizer_join_cache_level'; + +--source t/subselect_sj.test + +set optimizer_join_cache_level=default; +show variables like 'optimizer_join_cache_level'; === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-06-01 14:09:11 +0000 +++ b/sql/sql_join_cache.cc 2010-06-09 13:46:16 +0000 @@ -3209,6 +3209,9 @@ JOIN_CACHE_BKA_UNIQUE::join_matching_rec key_chain_ptr= key_ref_ptr+get_size_of_key_offset(); } + if (join_tab->keep_current_rowid) + join_tab->table->file->position(join_tab->table->record[0]); + uchar *last_rec_ref_ptr= get_next_rec_ref(key_chain_ptr); uchar *next_rec_ref_ptr= last_rec_ref_ptr; do --===============3648112753888035222== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/guilhem@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: guilhem@stripped # target_branch: file:///home/mysql_src/bzrrepos_new/opt-back-53305-2/ # testament_sha1: 0334cec019d6b7d5e9896381994d695a2aedcad9 # timestamp: 2010-06-09 15:46:19 +0200 # source_branch: file:///home/mysql_src/bzrrepos_new/opt-back-53305-2/ # base_revision_id: oystein.grovlen@stripped\ # 3ghzyo7roslb07yr # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWaLuZ74AaqRfgHTwWv///3// //r////0YFTe8uX3ObqucO7t4zdgLd0nEWu1vY473Q3nZvNF223u1i6711t653sNetzO3t5bj2Wl N2Om89ztAKArxt7Zu8s973Nda29u9e2aB2wO65k15Hr3a3ButZ72TnUHr3ni7vXe95dbb2zpCpdm oprQqtaDC9ivNDFrsddFbZJFSqvXbhy7ZbBVN3NO9gHnpU9TbY9zl5vNm7x6d529EcRWzEolddbn bOqWLCjWLNZqBQtjFswVrzA3V6dd47uxrWm1UVSQnruJj4SSBAATQBMIyIyGjU0xqap4FH6p6h4j RqAMgkghohAmhACNKfoKbUeSaHqaY1DQAAAAONDQNGmRpo0yAxMEAANAaA0yAwJkCTSRITRMgmTN Qap+QSNN6jTU2k0DJkDQDQANBEkQmmiNMp6aTIqn/iammTZJop+iZMUbU9I9T0InlNtUD0nqCJIg mphMmmgCZTano0lPCT2ip6T2aqZtUbKHqAM1AbiAhmP+03FqSMAnZ6OsrrBuwWwSuwpZhBBkEBjI ciQmoFy2237JAMgZlra5If8IQmtaZCt/p/rGf473vj9c4VOSNfwmeFRQh/1/QzfWCHQQIQKRg6EF dlmgdneQog4dVHPo2Ap4YjLuNjsJoVyP85iLHWz2BLOMO76YCIcJQGEyDkyBBGDT3GGWVVUln/04 VhIBkLkRFD9K28bheZG2xshwcKQLRMPb5YBqOnY2N7VoSm/2YBsJM3kdJs3Hr4eAKiooIxHznn/U hqAsjJqoyLCksFBkGooIxGSCiLpP2Bw/cb80u1vDp06c+XGc5znOc5znOc5znOc5znOc5znOc5zn Oc5qqqqqqznOc5znNUmZVnc/BrTaD9Dl7ussc9j+6XOHbYE54kPob9WmHI/qorCCSAjICHCxs0QF oIoDriJmSRFKYChIqpUazoBBTrgijtgSJshTERf1ESRQYCghSAv/R6cLCO4wSIC4N+jwD/sgYAip DK2F220HXYKa1hYhRWn0ZT4gCUZAZNZiMuBFLlktwyVEttl83lzFUVVRFRAVmCHtC6pntDjs02sE sR6FoJjX0HT4I6nRd1fDq91e46AnlwtAB1RyNHL1jR0uhzVxzKd/osGAYMBIWC4aDsHtDYMlAxQU FBQUFBQekVgyiqY6/HwAh/CkgEuqQC+YxBN4QeBoZ8VAWuELbSdSgLTZtyUBcxBuPcoC3aJMBkGh akYNCKRZFCyCJRdQJPFCEihIMijmoC9PCTCFSSRCo02UBdQ5jUUzY3x+SIsMypY0zfD2dN790jNh 81T463hs9sV/zuxxx/30Ptip5XcZYO6pWGQ4n54r3+szHyE6DOPgzRehJQY9IOoUoDuDFzcGMG7o s7qx4L8a4bIuIqabhrz6ovLb89Pt9/r39HHfh4sVAVQ74Sx93XDj/35fYaGQEifYJdkymVJUUUWL JRklZJUUUk3cLFju0UaFH8xMlVU5Tn9VHWaQlUHtEP7CMIlrk8uV7X6KLdh1/TZ39nYfsHT9rowI T/MTu98L+6w8cCnEq+VEyfx+3lAJKv4qU91bTxXreNgEocMPRyUb9PQTjW0YobMBPuEMHsaM6dJQ kKFvBrWXeQwzkxRYuZSM7DCYiXicuXG3WNhnJFKKDbaxbGa5RN5AwdG6Ijk1klEMVVVFRDXG04yG kdiVK8pmzqGe28H/DDJfNUmvkQ7/CwNIAYREYKTu8iQJCh919Q5ZaGI3FIFv60NJx22C3yoL+0lB Xz0FvcbnGrhK9Ng4NYd5fX9FaSiHFDi+dxyyGYZ2Zhui3dk86+S5GLNU5XWvRYegiWK1VJ6KV3n6 f28Z29s7JhN+u8o/Sw7krOqFC0xyqtgl1sByoG8hwzDvzXqiCkMXME9FIzznjvnw3qTn4QqhPmmS vkHoXx6rDNFrHzqcZIsscMy4d739BJVWB4IUUakhE+qRxYsRW1yQqGAiiIjINEqLX7/knk9JSn6T Jk+ZS0syB82q+VnGxeaz9He65+TgUfzfI9LhcNtGpRPfA90RToQkQkQhjs7OyyLeBt4jpYOkwlCG 2yj5+6vmrvlpIU0SiRGFCCJW0kWqlLRtAqVgo46t1Pw9uPAGhY0xZq6C00AuK3Rq6makESbOAYa3 mHTxt6B1MNvuMqZwW5TYlz769+p2lj2nnbOwQ4GJVECPcWC3o/Ms54r+JXBiY4RE6d7k+3mtBmvu xwcgTgoA83lAtsJMfVj0j6xDHDy1JoTUFF7C4nk0jUXjei0CRgMxMapcK6YdZv6d3Gsv6+n8M/u8 9Y5Un3Xo26UlEJNq1uNzVzjjrld5dcXvr+uBMw2ly0cS7TMPYeT7m2JNkzVVP2G42aQtLZ26ZfFQ F7DHTuQxwTVHCAjucmEjCG6OrZ1dX/3N19IUaAajXaj9dvZ6aXxXz+B5Ws23wUb00uOg8MW5RZW8 WWGDxaGLDxRYkfaJEP5WhSHX/++H+R088CEFm/Jd02/UcLkyeOzL+0C55mJX9aWoa9XLadsQwmzK gSgMTTm0RXWUfHDu9y5w67+8gx2j8FVU68jb6hyYzvexSK0eistY+DOl4dHEo/xq9+BFXuTapHK1 v8BnHZW6jEDOtmjPj8qPwBkRbl1k9ZBgN1gIp0qKZDb1lEEaQ09DcrBwYg5bCIkoCIrxdU05QmYW 1u4p5Q5jdhMwuKvJbmFn/mFwPKAH4PHx+jQHwggQ16/1TqfXPkvlQVVVVVXlA9vh+N8Nz3eYNQED G1hNiGBJq/ty37pV1lSxCxfntEHLjnTWZkJJuMda1qW/MeIREEREiKqqq9YTqG/UTUJoKMLANjY0 wuARDyASEXDMAECsXQgSsBQohJUJTnZDSEPD5t/c9h+blm8P8Y23JxUYBhFDZDhb+3b9hISb+EOK Rqcac95bZ5JcXg/g0sNga5L7GBSX+SN9s2wG6EuWXQLayQCNST2ZF95MsDh/IGAkJQV18Mt0OiuD N1AIDSCV6kPlsmv7d6eQ6uc0I/ch/cmtJIwkkieB5HK7lA4z0jqjaqBq9r3UqHLC2F2QT9vhQR/M s4ZPxKcKOP0S+ldB2LbXggDokv7jqyTXVHKuZtG/TnRYaJxHRnrF46mT3yCFzGv+foiUxoow0wPr oxWFNDQwcYMV+mNMJtPS8wbxDj3cjiMwbpjbMHYvsPyOJk6oRvqKyby7/D/Mq4hfPoeABJJt18kq vnc82HAx/eZes69KDQhPKNpsNABENf5rSSPM7bgqaefR5sQrpM2j1phmbgfx9e2AjfJw3xZzMD6r vfrDzZjq9hwvckAil7ibxzBN8KTcJVG5IKCakcBOhiB8UD4fD5iLUJgPx5w2B+wazxh9hsp8T5V5 KqpOpysYzGx9ygJqgKMgKNjH8rH7Ay4Pz6MLWQ5tWgX8I70IWfT5db6Rf9LaIqzGQJJIsvTGRenP ZPMsQzyORkQycIGUhrfrPH+w06s9c5tqda0lfhsvyRV/f7uVnYeYpqICB1cOr/xPWDlVJ36j4aIg n8NYbAHwiTxu3tZ5/fARPf9xwElmZE7IHjHazwZU64WZuSE+phA+HR8D6zL5cKZJIAZSvD8tcCvf t0HBOzp2h3HpgEZJJJ/5H1KcdVPZnz3gkLy+W8+Rb9WPsQkkMDx2L+MrslsN3QMM5loRh0Io5gO4 9yHI9KA7+1NBkJCEIRUSRhJ0+31GdgdOYEn2+J2d0pbYnpxVVVVVVR/QT8e1VV3A3AoMrFqVa2lG 1K1iyl9duW/OT2RDyzyERF3IqpgQk3oC79h7CjhyMuQORl/r6D6dHrPgGX3WWR8pxgS2yfb6Tu+L LFFqIWLEI0Wy46eUNWh769uZETagpTWMyr86+26OGU77ZmFdmHj7fBAS7Z+cjLw8DYeTIrY+Vo6A QbSBZsdokiMa3OmWFiF+H2YNjqlYhBDO/d4GM79QQB6PFGf/DE7BR7JE6JJs9kD1642xbHN8MCHx mpKGZg5MndRgh5qxy4c5+afhy7kBdmHwY7NZIkdrDb7TDm/Ks6qvblSODwfwEcY3G2vjlZfMztVr s0lKEtBmLEoVFgUy97Ix0TN8D4Sb51uA6Bms89H4ssYb7695m4fn9lfT2qN70ZZ3Vfw75sle8ase V7WfskkYdcwmBwoMZCEkiAXGQmUshLJ2IMJqlLMY5tC2G3AsWlCj6yYO8C42UdUUccgOfb3ct0IU zv5tGUW2EO6Co103QbMyAC1ucXLLMnAwMCiyXQZ4yMUF7WB62fwta4l6c5Hh1xycCWUAB8Jam3Lo c4A9ffC6+knghiS4zMF7UFZYTDMxx1FIUBIBYaCX6vUYw9fE35j3dqj0xJK+7KX25bS4YY+osY+o daYHI+RCQ3UTDnEgkSJotSMfWYLd1G0HvSSS+5HeboBiyM+mJtss3syJOiyH6mFyYDtou1jMEwig BrCeyq2qzBFMmSCmFeEUK5CYsAp8YztECIH5ILZp4E0uaSVnjTD+SASatR8dYYD5MiuRPwIlmkDr 3CfwK8n2NI0YU1qqgvA3qtyHDCRymqq5HK8NYZ12my7fuenh299U1PD8R6XOaaXA73BRT5fkgdnu 7+6RAA3pUon21PtileymA49smHRfNNp7d9cm7mQCVvT52y8ee2a5cypXzsa63J8hGYSSsh9D/H79 IwNgmBBIwV0owz+7n3dGnNQaT0muBIz3GIWTFrGewq/ge+6ZelZS1EMxx9StJ+vy7dmmruymXdC3 RVuzSTE5DgEhhUxY+dB3S1zywbft4O9EPBhaIjUlXj3uwIIgjMKMQvMyIrVM0/e9TMtBA1jehnAD 29CCMryIGDKFJk5IRAYIct7ZzGJ4lixNipBAB9cQMfKMhQbyrRkZGRkNyGkRwAE3lazbgzBF1wFk TG/ZnPgBNFWx8JgYzSGsfyCejR+bU8Zk8h4+ShIXgWAh2h8E8jpwg9jATyfxIOIeWccki8SSTA4l 5xtu/gQMRgJTC7EHIc/QDoMHxoX3XpJ2tOxIMGKhTBMthAhenrLIKPnEYGIbE3hA3JaAUBczzso1 FGwQWjCCTjhrLtnhgascdYAgTECjgCZrpAjnpYDI26sVmJCEJIEqZiCBsRS5psB0TQCKOuAurNBG R91tefMfg9ISSPXq1VkLQJqo4AiSCwQ0mGDbeFoHADnA1CJ5Pu1JSRAgskIkU3xqCVKiYqo+MHKM YQO7Xhq9fkIZfRpRObq8KhxptkY/O+Y7qqLjoePzNFtNM97/GD7RpJ40vPYH4bhkUM+Z7NWxuS/m nnhiQmF6tr03fujHw/SMfYLyI9iDiL3aUJ4HlqQVluoc7ACV8/XzbmwwTlLaJT6befDj1yRva7V1 IAozg38j9ls1oSUBrgYTBAe0LElRumqfG8KLuAGgoP7ZuvjMf26bc45nZnOPe4thHeSW9voDdPv+ 4LPRg7J/xf0u7PXmeamlpA/H7ojkzNXSgl+VvW530lfoAxA4gfsB5gfQD7QP+AP4gZHb9QBu7Qjw +VH2RAJEEwQMIwlLAFgSKBE+vqsUZCopWRFzhwMbg6BmjV7uXK3+AmR6qbLgKkkykQXoXG2ryPh+ v04BhCRd66s2h/lXZ9Jf3jddH/T5Wer7b5SdQ7xtjTboVkP2ulN33YTxpw0/3ttxwje2NXvordy2 wn01hoV6yVhxY1tq8dj8GNHQNo9aSawk+ok5uJKr93OVDrevglV4PYSdq0rePbYGccxnZmbSG5Dm 3G5Obeq8a7sH646KZpsHVQaMKLZ8tmVFV9z6Zyq0ZmYYeM4Z7OGZs9vVttzWcWPgcEVRFRexNB80 PZnzcfZhfxs0muGlg+qF87AWYI/K5eIifoclhWs8b5GyFH6D9R/afrKOCmMnnD6Zm1d3eCAcYYY4 HJLbGhyd+MkfYx3bKPYTkkkL76O+HSb5p3Xk3dlejCip7AYpbeMVt2DQY7e5xq6VO6QGa3udzGKT pkyeNa44N29pkXE9rNtgzbhj7/z4hoGLM7JmPgExArKTmTI7PTzXyH4APwSSRBViCyQFCKASfy7g 0dXjLBqv+3PFCwGeafoQKpChCmAUgCfa4kwQRAREQRBEBFEkVoUsYgggxBZGMjMhMCZkSMZUGQpR FgggLBBggyIzKWMEssqCQQQwBLEGMYkVB78758WjYOuAXuMNETVsEGCDWWRBYIkGCINAkn4z6ewK GEkEKanwmqDHSSLCxgVkCy0WSxCskUkUhyOWhTsF95zVPVbk/SGMgCYCmYQCfDJCRIFtYzQNXDp5 XwV2Q1ixRIsWJzipAWMQzL0lL2ha7yGASEYQuCiqLnUCmCsc2GYMUUcpWpdZYKLEkWQwtQLaMpEA UDNBhr5dAoZm2q5arYbA1oBIaKljwUpEDLQNz466ksDTa26mjNQSGoseA2cU3DRsCt3kLAxYo2CB UW8DCgZFP8Av+B5pSaV8CGNoRqQSKMUf6KNKMUYo0QRIsBGIiQkkGB8GejoUrFGlpyoSMWbKhtGo UU0SHRQW4hS5idQMEpT7YGRCEW0Hbr9QPrwwcSBCKphJELKn5xaUBNkfSNkCAQiJmB2duPPuqsVN PxLqN1HaH+nQ2wzq+VW7dV+ixSd8Q+PjygczxLAp9VgZ/p6vynLx9Ade/qKrERYqphmNOJ/1do8B PMfT2kiuNjnHsZ8he2Mf64r7KOpkOHIrqhhBnvodDmr7/RQo0BFGKEVhBVVVVVUUUWSMhGSMgIRF VFWLFkkSSCQEVVREUMDrHq8mGec2Ubve5drSmdOMDIgYB9drboxPCwbevjp+VcpzHw158eIo7CxQ o5QUdNodyezB8/6VJ1A0IsOpz86pjmdjrBptJQNwTMGtfyfBvOrZORJXwfAoDmNwfsBzwcHAuP1M 9RUK8F9bAnIYjkMAyGIG5RxUeR+OU5/jsP2M/r3wgwme9n8CBzcSOkeR1iJLAPzAkOsE9QGe/PIH UDQGqAuYigGA1YCxHWAIL5gC2qgKtrsBiYnCHZNoHG9gb83w0BkRU0TvR7wyb3Tv1eGocSCXM72d ps70OQC0yU74oih1CJ0OsRJWFRrLLfc4Z/9scWbHHnmSF3kO+xrt7f2oPJjxG2TPlovP5eMRxjMU Rmpjxc03dCYNgxJd6dmFZkXC4yJhmlwGcKMzhUNE3Ts58t8wwYZnLXwdOIiooiisMM0uWf8Rq36R dtvPJz0Tee7v9gd++eA60cMYzWuqYVxbPH27ubn8thAgxRIoQBSKOY6EJ0gZCGQGrTse9iuXPWdM yK1I+vJv67knJUViAqqTv53AXnZKMFOGHn8m1uSN0mXD8mB2Bz06hBVk2PDwXGM4MfrNud0tX6bC ceZUUdOkBEg0gbhNy8uf57AgmYoIW1oKSk9hSiLDC+PhhkEFLlVBotYMCB/KPsWpoyMHEOxPM+o7 Dkc5cuayp4RNudJtrwg7HiroB1AbQOQG0DkByA6AdIwO+Xz4Bd74lFjxY4EIRWPS7sChFR7es+p+ XTNnraOeCpHuJanvQYQ1QmVAnSENc5wGAiA1802yzgwHifdwBkDuxY0N3b7kZDkbO7PbD8POQt9W eS222222222gXrZzFxJ8WEBuAh6ZgXG1ybmXpE9So4Zn9Ud8JhKZli2IdizK+nLscpL4kh4e8XYy 3vu7MRgdh7QNGsRVrGGWMuPI0XhrOWp7lNJdgdidMS9wXmcFKwP9jBRlAIiCa0tHJveQCpkOyyyW Kchkr7OD+1wzLhZhXYEVeYZhQwny5TCCt5hPyASFgttrhyranfaoqtXfEYxmJzeJxa2uqcgBgGRO dPozl8PhC3FTAOwVs4MDCgB7gb3Fh3MaoArTAUTrM3BgHo01PmLil0E3fUZlnzguBrNsSAxh85e9 rtlEgTGsjWi9tCcuBNRUPn5HffnNM/t+Q+vUJ2Idc+ajh37UPrSEhCRCKT0z28fHuTaKq4lkJgWq xH4/j6eX1a1rWtTMzMzMzMzMzMzMzM9zzO5gwYMHkeZRRRRRgyUUUUUYMlFFFFGDJRRRRRgyUUUU UYMlFFFFGDJRNUIpKkPz+wD60MXSEvWkJVfLGWbQxtwcXIgwWvi0E3ve+M2tZ3KZm4yxOae8kPkv QUZgf3Fnd2rRowXqBoGWFWTTy1EvYwPGb0VfNjEals1qDPZLuvJMLsT15iSrta2266yJY0FNanC8 Yus1sMWTLTp/VuG1vWXscE9gHx8serVB8zrFPRmZ7F6BxXi8gphUXdruHCpifn4X2yl+lHWK3KD+ IkUTNS5s54b/BnjJZ9RAJzmRVDe5EovqZoQcLicTUspRt/tLtM4N6gycbWiemHq8JGSGzc3+DfdS wGI0KOEEVSygJABijEVihFFhAZCQkSQWQJBKVQDwR6lMlTQ6y69s8sQfuqNQ6Ehzh+734pNTlyEt WUtfpkOQHQDYGAYU4CjhhipzKI5VDWPiPX9Q/FeAW0IPPxx550FyovD4sl9rWq0PNCQO9XyB/KDE EGMZkrTctv4ZWyMDG1hhjQWaq0SFlWJkEX49dkuBeqQmVysZhcSPAS4IVFCjlLHSwrIu4XpYFNNI MFOacpOqB9gvzpQA13UAUDbArCsmQ46HTdNGzVAtE/m6sUwyaAQ/KTUzDITpv4NtKNa7jvYxw48O lJSe6ioZo1NASmyGBAwwphrJxklNNy0S2Jwdhy5ySTcO2kBNL3kjeSUSRkKBQ2vf8KtBZ4qj/NWx FagSJIhIqGsXDG6i4hmIXvYGBhsO23ttuTF7ZCRyagxA+9MJYEREiuEVuQDQETYiZJAYQhAEkLZ6 +GyrcxtbwjTiInAHIlbT6TJsux0NemwuLETfcpGGTLSB8lORioFcMxNFkWRuuR+TW3NixbOqMJ40 ToGGGKLnBfFIuPFEUDFuE3I0RitvHeJ2Ng2YYYGNnEIbxyk+lk2JJM6yrvGjVmZhMjgWoS68GxLk 7r7ha2wbhuRO4UhhgYYWdnNUr4wsPFGLDJMn7ndoi9FjgeBfDJUOHChJFRbVVQgluBzfL21D9CSs 8zwmf/UogrE8wcg9Rx147vgcjwBxgI+DGEgRIsSDBMWmnmUSp636zNkH5zFCEJJgERIQDo/MO6AU nUHHEuf7WYLiQC8VGgIAHFNLAWQQ3/kwhmJs4b9Xv8+jkIzsSH7RgH5EDGGmGgek86ouu5s9OzkR UmjCWECEx2ANVibzpOBDroKAfOAkKDJCRSMOzhx0n8iAwMHescoFQF+WCOza4EuNMb0gEmIZEiOX tshKYmgHdgZkhQ3QrdXaSM3tu3VsnaQ8u183j48wOicgnOHnIaiI/qxQwMRki3YjsBihriFoyS9F aELmhobjMTbk5Qh3FFQqsRwVuwQQO07odXpSXG4RrOtxybFWE9fP1/pxDBOLCkIGS9OzbdwMVKNa QdihtwDVYdwiV2YCzqLl24Tq30k9iD7LaSWDwnIV3iy7nu5FWQgV40c2ui6w1B6rm9IPadI0cZtr 5y45abw456TLIvwcsCAOwem0s4zBkTTvCxESQZmQ7NkspbxCR0LbYAZGczhY9tNXNoZ+Fe3AN5qv NGlw8G0KvRkaZp3BnRUX6YaBPEjKDyjv6FNV4XFDXNPaiU1o+o9qRZTrcGURGk54SBm6q7MAvMML 0EZYFxrK5it3eYKeiwL5rQoassRNPatgkb57RukTeYrkHLhU26tfgpckeLYC19C0fk0E20bk9D7E I4ZnXJdV2HKVdl4m24kLczLAnzExiO3lxctE3UT0TeY7R0lOxxbs+sRPmtttvenxIUTSsG8p67FQ NEKEDkeosXQzNDQ7j14LmDoQYxiQA73bfI8AXrYxIGtpaJEkZIQhMhB33w8+e98E9iZqljN3Z6iw o3gqWvgYuBIFBTPOhQ076tdcJnGPZFoGIRAkiYCRNULxLdUFd8Mtrsj6ccDGEiiJ7iAquEDu7Ocy +mYJpEJFqAeUEPY+roW5d1mmcz6j1jBFwENlbMCWWjHIggGD3h2/Ta60HQwmSEQNsggPO4GioyKW IiPYw8LkWAllmUji7N1xVhLLZDJgKf6XozTBQWAsAUDezDRzO05yamwEjCRpVEJ5koA/7eg44aZw NAPJys6i5QkPUIQVM1iEGAeNCJXYuHvNo/E86p00qi1ZzdBzZvV4T3bfl2GkixbF9No8ttPN6lln odmhrOihxCR6GPFesrgy9GY+J0B+JexStgJ4xBgnB3O5abpy8XPPmcz0FdTDhmwGZyZVDHsINRMP mJj9ZFdNWagsk1z+OxxtJk5CrGYRw5OQb70G+SOncjTeCSdIUa/gct+BYMg2ms857zYX2JmDWnbv 0UVpXACrFd6eUoAIQjmyhCLdxMhIHpSnBwcVhMkpw+3r9Po3t8q0xbiCwOGzKzpnSt282MtuheCC BtiqbJpEHb3hXVTVTcHcSi43FtlYJckJrW7QHsjCx6UEhUupJCf68XAKP4c44Xv5+TfM58CiAiQU CSTGATIsUZGRuNSYo3WgUROrtc6scqg45yyhKobSVd2N1VhYce0fArokQnTzi+TpPkPSsS74isuM jIYARRvrHVGIoiKBIlJc3YE8DyLxkpfJl47Wprn32irPY+nqT82DkfcV574jRknoXrK7TE2VW1lZ srIrtKoU0moZ7Q6qEuLByZJQInoIrOqMxnPSprzaXF5eWmoVGTvIORwD8ViKwAR8/1Jjp5dLc5m/ 1GEjopCQgSL+SqF7owGSgxCHkROqmNAfUTHotIeo2+QH0DMwwmZtDgbgNCZYeXITGWZo1xJJBBsV ZIZIOEHv962FJmdnO7C0yJLGwpPn0WuNciU2OVro2HTHSUDaReNElOe6SjfaOmHlZrGuENXJmkQE jayDdW5DXOxN/jl2uxceiUmGGUkF7EqnuLeVWQw1IzA5hmzdL2KmM3YIgiCe2nnjjoJzAYNI619L i4nnvM4ftaL9jFdQbxinPPXKEN8kQYIDAOBy6RDNAYFYZu2daPcRZw9XtngawQfMQXLCv7wwKCrG aU6NHqrnHLRo0QIGwiBBOJBiTlKkuoxH6JFJ0MJi2ZRMZ9LzIfaVEH9+LoFSXRnnqaBRIwqRA71c 1o4nOsXMPIu3QRRTGjax59zyuNJufCTCfWkvO/jnpo3Ke4YsGqiJ+nXE7QsLRi07hIpeRDXUE6zm neMcp566TPW7hgMu3lN48LRaIDpbcczv6Gsi7IzvE+0bk57I057QN/CEUbDgWYxLsmh3xtDNbW0k EEEAvEYZrIFtyYUekrUCXRhXZJGuW0wsEhxGhkomLo2MGuXhGBuLgoT1JphUkJEDBoORtliRmOnC RylSEaPiG2bnY2C5JwQvs+sv6j1HGunPrMouEHHEFMDGLzVQM4/pr5d8efPlz6W57AnrJztAToWi oO7wOahmcVd8uUkkkkm5ZCI8wQ9vEwvEaUuYOeejRFtPD33eqRrK7QtHaya4aYcMOxnHzZrHPsWF 8B8i9oG/wVZa7Yv2Ssvta/XZyyR4h2KLHdXJOgw5gO4rKwoEjgXoPQPxrPOZSuHXt6MZtkKKKxjn Y1DYg8q8kUMgsB6EXMHhAGGuJmTDtdx7uzRnEEEGiyA+1UK2C5rfJ6D91YJWhY2jLMthIuKVBYnp VZVUZHKcmg5IHFDnjM3JE15XBKwcDxwaW8DkHqOhVEr0e7sdly6tGTlGmMtD4wugbvDupIq8cT3j YEV8dx1F1me6+EIpiQMEid0yje2LYTEvPbtf8faewPc6HE9Gs8rgQ1xM5NuWpmzCmzmdDYQZTZgF MdYSFNdS7+o8FwSSu56tgJfXrq5jctloDNzR5HduB4xehTj7HGt1w9+6JIqLFupsKCutMWjz3e4X v8Ixxuxo0bvMfXl+kpCEYL5UPkzI7PFhsdL84sWl2gp2vM2MMQHVrWKMYu0PBBJi14pmfJa6kozW bSYIGervYu7uPfw/b/Ox+lm/IHPst988Nz+Lg9kuXS0kYgWLgSIu5iOmHcUSkSk+Ampqrl5EPJ1B Qixdvf28SzDOPeJX0c3ZlnF2BljKOH6M6737OQAfegaKGmThG8xLl1rJ8gkhpKIbQmUXXszToHWS sFnbGHYwIbRYu2G0lIgxKMAiMX3jJrL0hTzZNIYMgnKh0daFavOlUxd2Lb/i6Iu9mUMHZo1kWYGw yk3p/hUfNw/E4TJJCG6KOuIIcGJUQmJssjUZCZWsBWStoKBuOaW9QHu8/hs7oYbDgDXpOoJbNN6c U0H6+ZD9z26MACoEWCfooSsHVhpk/ODFtEqigjFCsrHs9980Pv9xSep8DvQsyWIh85oRjD+kU+sz aMCHDu5SVGTvgev2ghNLFVVUsIcc9AeJzmJdw9qkuX+R9hWYYh12X8ziJgG0Q2oRQDBCI2Q9qFBt TADZzFAfSqgHq/chv4wD+B3G3ZJf8f3jR+EPBCIUwP/YYAMUKH7eA/HM1Dgf4lQ3C/2bgeqJhAsY HWIp3H1eSHtbqRFGwoD/AdqIAYCe/z3Cx/dFX8j8xefnIRjD9ZqpVxR0/ELGtUUDEEPo+YQSP8VA s4BhieZf4Cn7jo8jhR4QQtjTsH9msDPce01hqvFJZ8gGgof5/KX9Y5E6HJEGRWFZHc/tlEPtISD+ 0CbNLFgeEks7D1WU7rGm+Z98/sMOEUBSQkQjzgcxY2IXE1Gr/4Lv70Mv6M/nJCiBYJaGKG4d2je1 NAgkBtEQPNuKqhIrIgiNNjQwt94ewJRc42l7mFffRBJiLEz2JYtOgZMGgYZj/cZFgHHuG7sokMr0 oFA0A5gMQ6i5XqGACaC6gyNyheoyiteetNR7/P0fjQZsTiQ9+NFg94EEUd8QRMGEwocgc6gYFrJl VIyapGkgH3mUwDYilIkCRRjIFhpoIiEQWZlDQJCIQeZC+WhtFhBhAhAJD15u01CCd+1dABQDsHAL bRf/VKBgKibBT9l6gBQC4Oj07mcUoI9LCRIQ6rJq5ANFzMB4uoKAZt2f1FOKKKGe1FXiiIYUmBEo HYbupXRC+odokRAYdSKaAb+RdogyClHVZQF9smOiETsAh0qB3KakxYIgiIxEFjJGKiLAAIpGMYxR AYwJEYEEsKiZWsgFQQkP1t9vdTOmGgFI5im81iKNLgUGJpNIaR1AW5IBCxXciihAKt1CKNBgBDsP APJXFDsRR3oo9gecTQbqigdha7iJsyIEClVUoiOkUJAhAkLKqnoEooUAezW6jHCMDI1hVpJBVZoE VEM0NEy6END3nI8kPdN6ckBdNuw2wdt6CIbooIEBRhgQOmu0HqEuDaRGQGJxQM0RFQ4jFQN/cqA8 0c0FOsAUA1Com+1gf73U1BO9FFNIoCqQVVigiRJIgEUgoEWKBEVdlmEbG4IMYDcglKio4GwsG0d4 RUUDLIWjRgQALcjwVGk4kIR/KfMmQog5RVNkVVJAgEQiEgggQVVJDeLEsGiMEaQqijvCBRsQ4WHN UDA6RsirgExAYFKgkaYCIbEQRAxADwPUjVRBaKOYQOYxQ44sPuhlwpfFFXiL+iggwTKKqowNQwFQ KAQzMdjBvbsQGKSA6kkCJdCKqqVfnhn1AJp2hiCHIIfqlIO4UB8EdhA2sa41MErjDIIdMD1kAgDD SRkokiwNc8Hg9nTRh3xUQiRfx1IOoBX1ZMDM6tMwMBuO9VsAYHV4NIo3RuAIM6aAEIjCgiiHYiJ1 BtFRiooGiBsKQe4VVHrATXr5aazWUirBFSAkEBJFhAVSQB6OOwPbBSETBNVIg2qwFAwQ3DAjPMYA Xn6bjrDXpoSFCsijFGKMUYoxRijFGKsUYoxRijAWRlPtSUCCS0aSVUpBkrCypYktliQRjFhJJCQK qiqrpRVslnaWDqdiYD2GxL2A1TQiKMQgL8osIaOsqTEGDJ/SXOw1ET1FiiI6qKQFK2oq9YeUQWMW IRRijFCKxWKEEikIQCAwVGJERigignh4wA5REQZFCRAjCMckM24LQjaAojSoJgEW5ogp7cFOVwYI J1jMRTjRcwO/BFXFbCi33Jgm1FHn5p0AVUkmQD4nxQDgirQutRAENigQQN2WdDA4AIniLDoHJoLh yEZJAhEBgMFkBAVEUVYAiCQkihGEiZRRbhYLBDYBvV77jigoyEYoEGEGCRESBIiJEkIgKTdkfOw0 QgTBKNAoMiIFoKA+JNXWIqBphG6bYtS6BFbbqBzlyBDBMj+Gw1B4fl+8DMDYhQdDrRR7NF8UPm+5 BP9UPUh80GH8KXuh7ZTIgJDkp6EDkEv3id0EGfvVYmSCLEFeeg7YdJIenNLByxEGWFIlgVoUSllo hSJQKI1KIiVUlUkYRpKVRc1AWAOw2DnYsCCGTc4ylZWtUQsGUoWDHIpAofsJ/rIHf/XrfKc8gczE FFbAbKSTwiAb7oYwMFBuFm6UCi3Qzd38v5QhOI/9HWEfBFlJNz2jwWLHer4avWQzZck/pq5h9Yuj lz7OWzs4NKzC023gxLV7pbmnQOPVjLZW2YQJbn5JPWkYICRBSJ9lCjEW0PqSsgYyAoKBfriOVIIi goUpZRqsaizWjTCds9E/PD6JDgjGQUNPqgBJrNzu58TwvQYhFEUTsElL6TJnbJaChAEZBiiAAgwg iMdAcAkLFDHbOn1Br1n6u40dQQ7vYkXW0TRTDmJ1OgxSKM/qnULFnkdaBEcnshzHUkfuMgrP3MDm Maj9TiH7lBtOIfzJjUZxh5oVtZ+5hoWhDOTkf6AFCLhjTrDA/59L0nlSALIKBIgf8x6AiekggtAN aeqYxWYQHDVsCB/UaqhbR4ISnHHZjsJAhLAuP0YasBuYJzXmCEo/t/NzAF7JxK5mvBdPEADmbsme FrEOo/iGtENF+pCAc5ABzIPtLViUgr+5wI+zGVqQf2LhOSPSET7VqWantysmhlRqds77UgM5FIAc VROfM7kHkFwNA/QcUTYB50TkUBr1ocd1SIW6DvQxF+I8sNIcB+0cy18f0VGhS6GMNsQqYFIjKSZw TmoMMVXJSQjIje+CjwW/IM4GcDxISQEr9yWTyED7AxXhfd5yLols/l9ajlUR4xjB+/1epi9iYt/c 6D8TzN53kT4B7jqJfAnIHBe0jq9m9LIwPEUjyP6feg1LUuBxbHNoG0jebSxcsZvEEF8UBYgp9MKQ BYKAVqex6WwJ/SADmCAbkfCpEfNMj0BxriAQDi2vsDQgn5QXI+BRRRrOQ58J4k5pOJrKIFE1C3el bz89X9TOFJwqRrQl1EeyBky+sRA2OGEHfDYRSCZksoMMs/QMg2jusnPiUI1PmQOfjqEAl89AaPGg +p+Z9P2rskobXBs7wCPNFBt4vCGxHtgFkSRiAGQn3EAbZIa3V2ujbzpiQiZWmNT2Cu8UwHkq9tGL E8BCfDngVFWIC/KGC8ohXGEMkK2wYe8M0spYIBIRUJBcDFBU2xUBPNagdwQz1lBdxfcn40hcYgiz nuSTcDRBIXSqOUSwS2Y2yZGQVCwCRSzBLjkffrNQDQohJkLV7i6ojjfDGY4USDaFKsL1PkjmZWFM RMkSe5mXcgof6Cks5zm88Du7xx5FncMJ5ikPfPeyKkjtMjxEi8opMTlAqqZICpAwBHM95/JPhtp3 H4ieP4z575n3WvtwuQWCswMCz78m827HYX2xQx6EwNFPcj/yQq5lKOBjZFHcJeyAt6uqcQgcbAbB PxEPgbBQzMoUMlUdvFX1dAGwAborQKW9It7Yc/wXENAkAm6gL0K+8gwJGBcNTnhU7scZGQkmfiEQ MxaxGwDH4QB4ZkM3iJ9KHMnJ1wqF3cwMl58bMgEmJwkApYaMKsIjRj6XKygaiKZUBPya5yy7QXdm NAQgYh2fBzgeSVhzucu8SFGExIFhETpSoQxKIRgGQ6EQwAXK6PxlrY0Ba0ozZi7HkB2TASI8H4Oh lujcY4Alp32dhOrA/CHQfWYg3NME1pxB7Ej0nErhSUpmbrJwo9xke/j1FZuFnnr8B2IIRCKRIMCS SSSAQXahhQeC0hxOs+w1ReqVPFyQvxAM0PAPG+uRiKikRk3IBJsJ1AnBga2rO2ZDhBtBkFo+sMDi qI/StyQJf5AurNBE5+v5dgp0kfOdxq9LRw8nLQ8/2TWAeYSRFRkIRAQIjPQPHP+APFthHAcdM9yA SbCZ00Ntz691NZArUn0Tyd7RhTIJ+XARWAyGNzs/sxR82TQ9feudHli09j0kAH0dnSAfmA+/MKWa L1qYXvDSJW4c0xO3+tsVf8AV0EVXn9hsRU70GcxgHfBGwBg+7goCzTVhAsXHA1ZmKdxPmMF+xDIE u7RJn479LSQTrCjZ0N1NLc9hKMeXxUL5eYABjE0rWm8gguEDDenOCLjFPTCgpBwBig4mZ/MOKCp4 kVBzHJ19AdAIbEMBV5WC67teZihiItr04CWgvGDzgC4wosRU0+e2WAGF8LjKmDlliZXyQJ70NW3j tAzU/eG+kEbMojJDAEzA3RFNMFH8Ftg8vTii3Lg83vgk6kUf2+oeA/QhSrNiJE/f8BEBeHkIAHLW RSoBQVSyvr9IfMl6EkRDDyW34YnwNg7oP0Hw9rieo+xewpPYQInn4lDvMY+R0HmBQfd6XExxOg1q SCnkajYYm0Ts3mhtuoIao9ag8TkZieyghIfZX5PO+cA8LAHgKdcZFJBhCHCipK7vUh8fRrQi60MP pp7xSgBNglrCWGg/wRiZSehofLzpCh/vhTIGuOkVet/dQOEkCRAkUgQUUgRQQ7EPcCLllRsD6Y+J 9gpq7naKKMFegfH2D8UHAdcIDxCEEIDBDbr50+1Cubx7hwzAJhSesLYIeGDr+8K6O8cxHzMeb9Ty EiFeezdH+D9vZ4v4xD8IwIyyVRRAokIl7d4he4JFBtJS/H2bgjcE6H1sKLRj84UYCF0sFwgNKu73 p+1vFBw/TzBQKiqSCg5hCscUKc5QYBREDtCui7B7FUHsUVTmOcChR+JlwbKAlyKoBLtzlqe5xVQD WeIEtKPFgiGUbCCJwpsQpB7YtkBftL/Zq67ZPghvUBYPrhPj1YoXzZoAxA3jrAMA0fRfOEygYYBI EOOow6KWsQKUMQH8jfljlkCP/EX/URalry2JGH9Kkfh80jbnvQZxC8EbRK8EEc6B32FALdCpuATE IgJ08wYREDyYMIipaAQPwlmYRnyuARMSedlgj7hzKVA9iDlr/M3mZrl94P4IWRH8UMDYhSjjr7QC X7+XmbexAWiwIJ4ldVUZmJMZr50kARFIWeV8L/clAtEJRARqYSSzc4BaGLAyAnuPiVUNKYpqRzLD pAYK26/bHgByihvhIRCAO8QgVBCQoOrvVGI6HCQknP5g+PV3H+B9IPILGaFEQIQDhEflPQoPREDx Oi2jICZ+Avm4cC8JwATICCkEKCiAobdjf2EqkX/QICR6xXecn/MhigIeZPCoQ+uJlSJD/eywSQSQ Q8Bu0TH+swfn7B3ezOEk2TngB7dlIndWHSuM9QI68kPP9CEjnYZEM4j0KHzXPv7NOOe5QFlNoyMh GtlrEWMp+ayFgikMi6LRUUolEYMRRRuUMEiFpYkRKTT9dSmCG7BSG2EUcqBaKHEgEmphkgEmvEP6 IWBvw/zD53vbp3h1h1cBTBtRKaJJ1LYCm6yINBgpDiJeAn1hZ/DrpWGrEzSyr8vuIj6/CO3DYNnR VMT/ChqD9cr6vmRAF2kUF8kI6QUSwkLSCaBACkVHAiN8hFV3GFyu1DrUBe4e8sBwEwVV4hkornqA PakA86GiDhzoNl7NAZmUafLcABmxdXBr3Wt1EpVW7ZhbqHPUGW0ryfCI0mAeP9+nM3K5gRhrHwAv dumyAhiYz0C4TnZE3oqXGQfWCipuT0jJ2YST2eAqqqqqqqqqqqqyzbIB5j6eNImhrRz6Z0jJOk2S fqEpCwjlh5SxtFOCkNFpWogf5RCyLkHOdc82YIdgeoDIME4hsAoRseW+jlhSAnVZ3cHq9gpPLgvM JntAP+6Avmp5CHXqDrB3BjmPYA/UB9h3HDXCKAsdpuEp6Ze9CSQ4pJJWAAg5QoxXYydWBANaPs9d NQRNNhxmBjMRsoEzKYk9AMCs0Sux5IUQoouUFHr40opkuQ1NgpPoNepDGttawKVBkSJ2wELAyCQK CyFBoNkEqAwQidLPESjUYGWHiG96gDwAiFwyTQzzud0ML3lKtFovQwxZ+iMiMbREoCXBxXKkUcVS 4VREDpwt9C+652azc5YqAhEgohqMQDmuFxsCXXK1tZKlDtFLH86LXEuBovjoqyAwjNnRFFD9vnz2 83LSoCuygFCqjCSAX20oxgtoqkkEgqxLzhLCy1IrTLipSn28dlsHH2mdtofJ9r5x+MPTN8KIis7Q cfe8PxHHvbHkOD99cDqA9C/uU8SLa/5DrN0so9/xUHX8gArjHADRQesfHacHsaOW4QAaVfiKWa/T vdxjZqRDWlwiSQSElXLlFi4FAobKkVQxBb9HjbUhgUgoeab14sQMBE3Zp8p6w8vSBcSkIEQiL/sj pv43gEjIekH6wDnXTChL4e/7abjqQ/3zyewN6RAsbxgIiZIfFNj1/XAE90sh7QMIutdMEBfcHfr0 DnVdYegQRGKxIkho8p15E5DKsPq3f2Um2KxDfKQp2ZCFlUXidg+QbhQ7F6WHsvJtQqCc4BBLHiTx TAvcM6QghgXE9woC68R1fK6WL1nXL29u04fj4ENTAhQxGo0eLUG3iXLFFwkW/8V37i8RZz+vQg4A G/D6aF0BYgC3duJye4cebcacJ95FLEAOvUvV1bBiAmjakceQhnbYp34qHoGwfzdwU78uIqcDwfWH R9hzdQfQHCH+JCoEYyAvl958/bMgAHNIxbWhYNoQkpWWE2rYtABieL1Bs/xbiAGB00HohDqKUpUu gBpSq3QBaBlGzpPTAA+HxkJCDjkE+t7Sg1gKZCewiF7iBeKC77UWpoaYC0x3jWKWFB+s6VBeO3aK Nh9cOkZwHUvNt0xUN6qL4O6ZmhmahNcU5B9wYmFqDeGoqiFU0leFCcDucgE3ROmBoDFV2EO6HRhS 7VTDJMg8MNYWGuzJPuhq5hAB/N7/2hCO3yFDXmfzH4epxgwQOM1R7FAWBaL7VD/fpHTxIcVPU+1E DcYQk+OEHkQY/IX4Hx0F2AlBQUTaFkSAQsNigKBfWdjyUBfBQFvkR7nEJLf3TAhmQEZ5Q4cmH2az WeTJS+JEPMRcDMuS98ly0Pi92sJhNJ8MRFmsopkMjlJwTDWsOunPV6fVaqqqqqxf7LVVVVVUPPav 5rVVVVV9UtVVVVVVVVVVVVXxJ6O8/N3X558gdgegDoH3Edg8oAup8noQ8UrZs9EDJs3A0ecRGCjB VVFVQRBWfntftrKP25AyGF85MyCAAkFBhERcfhhmLGUASVBPVdVMFAW6dnuhEh8LNFgxD3+Qi7TH dvSRAw2LcgiQP7RTeB5wwEtrQ1CFwxDKQCUOZ4EEETTih+AIJnFQDWDebDmBQ/ivevpcaDZbT67N R8J0GBsQaGZsnOlZxkwkpRkznPAqVKDcpzLPeCInAcScaMqKsgIVSg2VG6FAgbZhE0UB1GKOAOsG G6OrDIINy6cqKSd/geGZkzFzC+LrXY0a3G40a0a3OgJ4rhmoPcipsOxN5tYSVvhqqxz8N/3oX5CS jEgiYpnlYDCI6kgds7gzuWUUazoTvtUkxqErNF9Wn3WtdJLaIjgQl9oGkUQhOOBfLXlIC82AGAtY ZJJqlCgQJYA0qvqnL7OBtAwMFhDA4LQqWHBIREE6cshzBQQKgaZwMDsYFUDOehLWYQ+93cAw145t ZIugR6eUhTxCyqaP4FFQkAT5ELohlAd0ZARv0wsJDQkktpIFH1sMyyAoSAT+jdUPohaFgkqFhaFh aFhaFhaFgkKUkRJESREkRJESREkRJSoN4o94DEQizEsUoF4Ei6AMnVnJlXKY4BykAhQJNQQOCMKm tCKNEMIGgxbkEogJhFWhTBOUmBKIckQ4AJwizbCQ1gwBQG8ZBQSiCqMgpIt4torUEM00yE1AGQCs CYA464pWTIhzLwYAQBEgkYEVVk4YTmSPJ2pzkFKAEGoogSGrEK/8wMXMChFUiqARULwYQAxMmkC2 DQIc9g4jYU+sOHN6KkCHGgoJ1aF/MABEPhBVoJ2CGwVLBFAXtQpBcu1zQBdgCn1FkpQXMeRk52+4 UtXorW4owEYKEFOdYDUBuEFXi7aUxJ41pDeSaJgFlAZrAPhmkCaB5SIC5kL0OIq5oQ5jPzlKcHzi D7xzA9yC44szUfSlDDfzcqV8gTJcU7yK3lQon5X5X6d9kBc+WoiEIyOonkQSxxPcgJzG3TsOrLBd mutsC0KCBaURWyEChCdHFPi9AYmtBxTPQhzwaUXftxRs5B2qAtlsqk1kvEkdt1NE258EfI/vOGCQ IHQ0UnaoC+KgL1PR6EfF2bjB6dLC9sRHxn7HpC0+kJ9wUi0CH0gifdAAIMjJIySC6/B3gwA8QGAP 6bgHVNyOQdz62nepvJEgMZAiED98MOd5zx/4yYLmBoq9ohvD6iwI8qgeHedaH5kQiYaWd4ZlJtj2 moP4+91ObJJIdIhX2h+XMvhFtkFLll2c/muiPiEALEF+EP2x+h9w9xpl6gDNyUW1kpCQoSw53KDr uWMIS8KkLL8LAZIdU9yL6nv4WJbH2yYPyByRIFzFaV2ERzW2qsUYsP7l1h6yoJmCABAgHJGoa3/7 +mFngTZG0T5X1VfzimjkgGIpJCKQVFRIyBIvAT6ZAaEtA0l1CdREvLKovbtRDrm5AiBR+r2BrCxc A7UgoV/ApH2N5BT1sIFjU0o2UBeN7NJSUiC6WoiwCz1NJnvaKgwmhknmYBiAYgVF2MApS2cPuwmV 3rkwzZCwgpxj97UkuibhoosKEiBCkoTVMo3YEAPE9dANgEXjvs+ocxQF6TrHIEFz+UULj2Iqb+db nO5uz29Sp8GIgfjFUMjiGhzbAOgQUrEKt/F3JFOFCQou5nvg --===============3648112753888035222==--