MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 9 2010 1:46pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (guilhem:3189) Bug#53305
View as plain text  
#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 <null)  ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+drop table t1, t2;
+# 
+# BUG#42742: crash in setup_sj_materialization, Copy_field::set
+# 
+create table t3 ( c1 year) engine=innodb;
+insert into t3 values (2135),(2142);
+create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
+# The following must not crash, EXPLAIN should show one SJ strategy, not a mix:
+explain select 1 from t2 where 
+c2 in (select 1 from t3, t2) and
+c1 in (select convert(c6,char(1)) from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+drop table t2, t3;
+#
+# Bug#33062: subquery in stored routine cause crash
+#
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(c INT);
+CREATE PROCEDURE p1(v1 int)
+BEGIN
+SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2);
+END
+//
+CREATE PROCEDURE p2(v1 int)
+BEGIN
+SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2);
+END
+//
+CREATE PROCEDURE p3(v1 int)
+BEGIN
+SELECT 1 
+FROM 
+t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08,
+t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16,
+t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24,
+t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32,
+t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40,
+t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48,
+t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56,
+t1 t57,t1 t58,t1 t59,t1 t60
+WHERE t01.a IN (SELECT c FROM t2);
+END
+//
+CREATE PROCEDURE p4(v1 int)
+BEGIN
+SELECT 1 
+FROM 
+t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08,
+t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16,
+t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24,
+t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32,
+t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40,
+t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48,
+t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56,
+t1 t57,t1 t58,t1 t59,t1 t60
+WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2);
+END
+//
+CALL p1(1);
+1
+CALL p2(1);
+1
+CALL p3(1);
+1
+CALL p4(1);
+1
+DROP TABLE t1, t2;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+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/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2010-06-09 11:02:07 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2010-06-09 13:46:16 +0000
@@ -2808,6 +2808,53 @@ 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(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

=== added file 'mysql-test/r/subselect_sj_jcl7.result'
--- a/mysql-test/r/subselect_sj_jcl7.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/subselect_sj_jcl7.result	2010-06-09 13:46:16 +0000
@@ -0,0 +1,2861 @@
+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, t10, t11, t12;
+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 t11(a int, b int);
+create table t10 (pk int, a int, primary key(pk));
+insert into t10 select a,a from t0;
+create table t12 like t10;
+insert into t12 select * from t10;
+Flattened because of dependency, t10=func(t1)
+explain select * from t1 where a in (select pk from t10);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+select * from t1 where a in (select pk from t10);
+a	b
+0	0
+1	1
+2	2
+A confluent case of dependency
+explain select * from t1 where a in (select a from t10 where pk=12);
+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 a in (select a from t10 where pk=12);
+a	b
+explain select * from t1 where a in (select a from t10 where pk=9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t10	const	PRIMARY	PRIMARY	4	const	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+select * from t1 where a in (select a from t10 where pk=9);
+a	b
+An empty table inside
+explain select * from t1 where a in (select a from t11);
+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 a in (select a from t11);
+a	b
+explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using index
+select * from t1 where a in (select pk from t10) and b in (select pk from t10);
+a	b
+0	0
+1	1
+2	2
+flattening a nested subquery
+explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer
+1	PRIMARY	t12	eq_ref	PRIMARY	PRIMARY	4	test.t10.a	1	Using index
+select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
+a	b
+0	0
+1	1
+2	2
+flattening subquery w/ several tables
+explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	Using where; Using join buffer
+1	PRIMARY	t12	eq_ref	PRIMARY	PRIMARY	4	test.t10.a	1	100.00	Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
+subqueries within outer joins go into ON expr.
+explAin extended
+select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.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	A	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+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`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join (`test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And <in_optimizer>(`test`.`B`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`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 <in_optimizer>(`test`.`t2`.`A`,<exists>(<primAry_index_lookup>(<cAche>(`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


Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20100609134616-sfxomd7wg5cmn184.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (guilhem:3189) Bug#53305Guilhem Bichot9 Jun