~drizzle-trunk/drizzle/development : contents of tests/r/subselect

~drizzle-trunk/drizzle/development : (revision 2400)

drop table if exists t0, t1, t2, t3;
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	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
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(100), pk2 char(100), pk3 char(100),
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	NULL	NULL	NULL	NULL	10	Using where
2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
select * from t3 where b in (select a from t1);
a	b	pk1	pk2	pk3
1	1	1	1	1
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;
drop table t3;
create table t3 (
a int, 
b int,
key(b),
pk1 char(100), pk2 char(100),
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	t3	ALL	NULL	NULL	NULL	NULL	50	Using where
2	SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	
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
10	10	10	10
11	11	11	11
12	12	12	12
13	13	13	13
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
set join_buffer_size= @save_join_buffer_size;
set max_heap_table_size= @save_max_heap_table_size;
explain select * from t1 where a in (select b from t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
2	SUBQUERY	t2	index	NULL	b	5	NULL	10	Using index
select * from t1;
a	b
1	1
1	1
2	2
select * from t1 where a in (select b from t2);
a	b
1	1
1	1
2	2
drop table t1, t2, t3;
set @save_join_buffer_size = @@join_buffer_size;
set join_buffer_size=8000;
Warnings:
Error	1292	Truncated incorrect join_buffer_size value: '8000'
create table t1 (a int, filler1 varbinary(20), filler2 varbinary(20));
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 t0 where a in (select a from t2 it);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	32	Using where
2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
select 
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
from t1 t0 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 t0 where a in (select a from t1 it);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	22	Using where
2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	32	
select 
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
from t2 t0 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 t0 where a in (select a from t2 it);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	52	Using where
2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
select 
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
from t1 t0 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 t0 where a in (select a from t1 it);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	22	Using where
2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	52	
select 
a, mid(filler1, 1,10), length(filler1)=length(filler2) 
from t2 t0 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	Using where
2	SUBQUERY	t1	index	NULL	a	5	NULL	10	Using index
2	SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
2	SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
drop table t0, t1,t2,t3;
CREATE TABLE t1 (
ID int NOT NULL auto_increment,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int 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 NOT NULL default '0',
Capital int 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	t2	ALL	NULL	NULL	NULL	NULL	16	Using where
3	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY,Percentage	PRIMARY	136	func,const	1	Using where
2	SUBQUERY	t1	range	Population	Population	4	NULL	1	Using where
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 int DEFAULT NULL,
Population int 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 DEFAULT NULL,
Code2 char(2) NOT NULL DEFAULT '',
PRIMARY KEY (Code)
);
CREATE TABLE t2 (
ID int NOT NULL, 
Name char(35) NOT NULL DEFAULT '',
CountryCode char(3) NOT NULL DEFAULT '',
Population int 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	NULL	NULL	NULL	NULL	31	Using where
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	125	Using where
SELECT Name FROM t1 
WHERE t1.Code IN (
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
Name
drop table t1, t2;
#
# MySQL BUG #42742: crash in setup_sj_materialization, Copy_field::set
# 
create table t3 ( c1 date) engine=innodb;
insert into t3 values ('2009-10-22'),('2142-10-22');
create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
select * from t3;
c1
2009-10-22
2142-10-22
explain select 1 from t2 where  c2 in (select 1 from t3 CROSS JOIN 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
3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
drop table t2, t3;

1 by brian clean slate	1	drop table if exists t0, t1, t2, t3;
	2	create table t0 (a int);
	3	insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
	4	create table t1 (
	5	a int,
	6	b int
	7	);
	8	insert into t1 values (1,1),(1,1),(2,2);
	9	create table t2 (
	10	a int,
	11	b int,
	12	key(b)
	13	);
	14	insert into t2 select a, a/2 from t0;
	15	select * from t1;
	16	a b
	17	1 1
	18	1 1
	19	2 2
	20	select * from t2;
	21	a b
	22	0 0
	23	1 1
	24	2 1
	25	3 2
	26	4 2
	27	5 3
	28	6 3
	29	7 4
	30	8 4
	31	9 5
	32	explain select * from t2 where b in (select a from t1);
	33	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	34	1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
	35	2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
1 by brian clean slate	36	select * from t2 where b in (select a from t1);
	37	a b
	38	1 1
	39	2 1
	40	3 2
	41	4 2
	42	create table t3 (
	43	a int,
	44	b int,
	45	key(b),
642.1.78 by Lee enable subselect_sj2	46	pk1 char(100), pk2 char(100), pk3 char(100),
1 by brian clean slate	47	primary key(pk1, pk2, pk3)
	48	) engine=innodb;
	49	insert into t3 select a,a, a,a,a from t0;
	50	explain select * from t3 where b in (select a from t1);
	51	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	52	1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where
	53	2 SUBQUERY t1 ALL NULL NULL NULL NULL 3
1 by brian clean slate	54	select * from t3 where b in (select a from t1);
	55	a b pk1 pk2 pk3
	56	1 1 1 1 1
	57	2 2 2 2 2
	58	set @save_max_heap_table_size= @@max_heap_table_size;
	59	set max_heap_table_size=16384;
	60	set @save_join_buffer_size = @@join_buffer_size;
	61	drop table t3;
	62	create table t3 (
	63	a int,
	64	b int,
	65	key(b),
642.1.78 by Lee enable subselect_sj2	66	pk1 char(100), pk2 char(100),
1 by brian clean slate	67	primary key(pk1, pk2)
	68	) engine=innodb;
	69	insert into t3 select
	70	A.a + 10B.a, A.a + 10B.a, A.a + 10B.a, A.a + 10B.a
	71	from t0 A, t0 B where B.a <5;
	72	explain select * from t3 where b in (select a from t0);
	73	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	74	1 PRIMARY t3 ALL NULL NULL NULL NULL 50 Using where
	75	2 SUBQUERY t0 ALL NULL NULL NULL NULL 10
1 by brian clean slate	76	select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
	77	a b pk1 pk2
	78	0 0 0 0
	79	1 1 1 1
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	80	10 10 10 10
	81	11 11 11 11
	82	12 12 12 12
	83	13 13 13 13
1 by brian clean slate	84	2 2 2 2
	85	3 3 3 3
	86	4 4 4 4
	87	5 5 5 5
	88	6 6 6 6
	89	7 7 7 7
	90	8 8 8 8
	91	9 9 9 9
	92	set join_buffer_size= @save_join_buffer_size;
	93	set max_heap_table_size= @save_max_heap_table_size;
	94	explain select * from t1 where a in (select b from t2);
	95	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	96	1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
	97	2 SUBQUERY t2 index NULL b 5 NULL 10 Using index
1 by brian clean slate	98	select * from t1;
	99	a b
	100	1 1
	101	1 1
	102	2 2
	103	select * from t1 where a in (select b from t2);
	104	a b
	105	1 1
	106	1 1
	107	2 2
	108	drop table t1, t2, t3;
	109	set @save_join_buffer_size = @@join_buffer_size;
642.1.78 by Lee enable subselect_sj2	110	set join_buffer_size=8000;
1 by brian clean slate	111	Warnings:
910.4.10 by Stewart Smith fix system variables for correct endian architectures.	112	Error 1292 Truncated incorrect join_buffer_size value: '8000'
642.1.78 by Lee enable subselect_sj2	113	create table t1 (a int, filler1 varbinary(20), filler2 varbinary(20));
1 by brian clean slate	114	insert into t1 select a, 'filler123456', 'filler123456' from t0;
	115	insert into t1 select a+10, 'filler123456', 'filler123456' from t0;
	116	create table t2 as select * from t1;
	117	insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
	118	insert into t1 values (2, 'duplicate ok', 'duplicate ok');
	119	insert into t1 values (18, 'duplicate ok', 'duplicate ok');
	120	insert into t2 values (3, 'duplicate ok', 'duplicate ok');
	121	insert into t2 values (19, 'duplicate ok', 'duplicate ok');
	122	explain select
	123	a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
642.1.78 by Lee enable subselect_sj2	124	from t1 t0 where a in (select a from t2 it);
1 by brian clean slate	125	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	126	1 PRIMARY t0 ALL NULL NULL NULL NULL 32 Using where
	127	2 SUBQUERY it ALL NULL NULL NULL NULL 22
1 by brian clean slate	128	select
1 by brian clean slate	129	a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
642.1.78 by Lee enable subselect_sj2	130	from t1 t0 where a in (select a from t2 it);
1 by brian clean slate	131	a mid(filler1, 1,10) Z
	132	0 filler1234 1
	133	1 filler1234 1
	134	2 filler1234 1
	135	3 filler1234 1
	136	4 filler1234 1
	137	5 filler1234 1
	138	6 filler1234 1
	139	7 filler1234 1
	140	8 filler1234 1
	141	9 filler1234 1
	142	10 filler1234 1
	143	11 filler1234 1
	144	12 filler1234 1
	145	13 filler1234 1
	146	14 filler1234 1
	147	15 filler1234 1
	148	16 filler1234 1
	149	17 filler1234 1
	150	18 filler1234 1
	151	19 filler1234 1
	152	2 duplicate 1
	153	18 duplicate 1
	154	explain select
	155	a, mid(filler1, 1,10), length(filler1)=length(filler2)
642.1.78 by Lee enable subselect_sj2	156	from t2 t0 where a in (select a from t1 it);
1 by brian clean slate	157	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	158	1 PRIMARY t0 ALL NULL NULL NULL NULL 22 Using where
	159	2 SUBQUERY it ALL NULL NULL NULL NULL 32
1 by brian clean slate	160	select
1 by brian clean slate	161	a, mid(filler1, 1,10), length(filler1)=length(filler2)
642.1.78 by Lee enable subselect_sj2	162	from t2 t0 where a in (select a from t1 it);
1 by brian clean slate	163	a mid(filler1, 1,10) length(filler1)=length(filler2)
	164	0 filler1234 1
	165	1 filler1234 1
	166	2 filler1234 1
	167	3 filler1234 1
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	168	4 filler1234 1
	169	5 filler1234 1
	170	6 filler1234 1
	171	7 filler1234 1
	172	8 filler1234 1
	173	9 filler1234 1
	174	10 filler1234 1
	175	11 filler1234 1
	176	12 filler1234 1
	177	13 filler1234 1
	178	14 filler1234 1
	179	15 filler1234 1
	180	16 filler1234 1
	181	17 filler1234 1
	182	18 filler1234 1
	183	19 filler1234 1
1 by brian clean slate	184	3 duplicate 1
	185	19 duplicate 1
	186	insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
	187	insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
	188	explain select
	189	a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
642.1.78 by Lee enable subselect_sj2	190	from t1 t0 where a in (select a from t2 it);
1 by brian clean slate	191	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	192	1 PRIMARY t0 ALL NULL NULL NULL NULL 52 Using where
	193	2 SUBQUERY it ALL NULL NULL NULL NULL 22
1 by brian clean slate	194	select
1 by brian clean slate	195	a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z
642.1.78 by Lee enable subselect_sj2	196	from t1 t0 where a in (select a from t2 it);
1 by brian clean slate	197	a mid(filler1, 1,10) Z
	198	0 filler1234 1
	199	1 filler1234 1
	200	2 filler1234 1
	201	3 filler1234 1
	202	4 filler1234 1
	203	5 filler1234 1
	204	6 filler1234 1
	205	7 filler1234 1
	206	8 filler1234 1
	207	9 filler1234 1
	208	10 filler1234 1
	209	11 filler1234 1
	210	12 filler1234 1
	211	13 filler1234 1
	212	14 filler1234 1
	213	15 filler1234 1
	214	16 filler1234 1
	215	17 filler1234 1
	216	18 filler1234 1
	217	19 filler1234 1
	218	2 duplicate 1
	219	18 duplicate 1
	220	explain select
	221	a, mid(filler1, 1,10), length(filler1)=length(filler2)
642.1.78 by Lee enable subselect_sj2	222	from t2 t0 where a in (select a from t1 it);
1 by brian clean slate	223	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	224	1 PRIMARY t0 ALL NULL NULL NULL NULL 22 Using where
	225	2 SUBQUERY it ALL NULL NULL NULL NULL 52
1 by brian clean slate	226	select
1 by brian clean slate	227	a, mid(filler1, 1,10), length(filler1)=length(filler2)
642.1.78 by Lee enable subselect_sj2	228	from t2 t0 where a in (select a from t1 it);
1 by brian clean slate	229	a mid(filler1, 1,10) length(filler1)=length(filler2)
	230	0 filler1234 1
	231	1 filler1234 1
	232	2 filler1234 1
	233	3 filler1234 1
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	234	4 filler1234 1
	235	5 filler1234 1
	236	6 filler1234 1
	237	7 filler1234 1
	238	8 filler1234 1
	239	9 filler1234 1
	240	10 filler1234 1
	241	11 filler1234 1
	242	12 filler1234 1
	243	13 filler1234 1
	244	14 filler1234 1
	245	15 filler1234 1
	246	16 filler1234 1
	247	17 filler1234 1
	248	18 filler1234 1
	249	19 filler1234 1
642.1.78 by Lee enable subselect_sj2	250	3 duplicate 1
1 by brian clean slate	251	19 duplicate 1
	252	drop table t1, t2;
	253	create table t1 (a int, b int, key(a));
	254	create table t2 (a int, b int, key(a));
	255	create table t3 (a int, b int, key(a));
	256	insert into t1 select a,a from t0;
	257	insert into t2 select a,a from t0;
	258	insert into t3 select a,a from t0;
	259	t2 and t3 must be use 'ref', not 'ALL':
	260	explain select *
	261	from t0 where a in
	262	(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
	263	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	264	1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
	265	2 SUBQUERY t1 index NULL a 5 NULL 10 Using index
	266	2 SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
	267	2 SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
1 by brian clean slate	268	drop table t0, t1,t2,t3;
1 by brian clean slate	269	CREATE TABLE t1 (
642.1.78 by Lee enable subselect_sj2	270	ID int NOT NULL auto_increment,
1 by brian clean slate	271	Name char(35) NOT NULL default '',
1 by brian clean slate	272	Country char(3) NOT NULL default '',
642.1.78 by Lee enable subselect_sj2	273	Population int NOT NULL default '0',
1 by brian clean slate	274	PRIMARY KEY (ID),
	275	INDEX (Population),
	276	INDEX (Country)
	277	);
	278	CREATE TABLE t2 (
	279	Code char(3) NOT NULL default '',
	280	Name char(52) NOT NULL default '',
	281	SurfaceArea float(10,2) NOT NULL default '0.00',
642.1.78 by Lee enable subselect_sj2	282	Population int NOT NULL default '0',
642.1.78 by Lee enable subselect_sj2	283	Capital int default NULL,
1 by brian clean slate	284	PRIMARY KEY (Code),
	285	UNIQUE INDEX (Name),
	286	INDEX (Population)
	287	);
	288	CREATE TABLE t3 (
	289	Country char(3) NOT NULL default '',
	290	Language char(30) NOT NULL default '',
	291	Percentage float(3,1) NOT NULL default '0.0',
	292	PRIMARY KEY (Country, Language),
	293	INDEX (Percentage)
	294	);
	295	EXPLAIN
	296	SELECT Name FROM t2
	297	WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000)
	298	AND
	299	t2.Code IN (SELECT Country FROM t3
	300	WHERE Language='English' AND Percentage > 10 AND
	301	t2.Population > 100000);
	302	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	303	1 PRIMARY t2 ALL NULL NULL NULL NULL 16 Using where
	304	3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,Percentage PRIMARY 136 func,const 1 Using where
1103.1.3 by Brian Aker Remove dead options.	305	2 SUBQUERY t1 range Population Population 4 NULL 1 Using where
1 by brian clean slate	306	DROP TABLE t1,t2,t3;
	307	CREATE TABLE t1 (
	308	Code char(3) NOT NULL DEFAULT '',
	309	Name char(52) NOT NULL DEFAULT '',
	310	Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
	311	Region char(26) NOT NULL DEFAULT '',
	312	SurfaceArea float(10,2) NOT NULL DEFAULT '0.00',
642.1.78 by Lee enable subselect_sj2	313	IndepYear int DEFAULT NULL,
642.1.78 by Lee enable subselect_sj2	314	Population int NOT NULL DEFAULT '0',
1 by brian clean slate	315	LifeExpectancy float(3,1) DEFAULT NULL,
	316	GNP float(10,2) DEFAULT NULL,
	317	GNPOld float(10,2) DEFAULT NULL,
	318	LocalName char(45) NOT NULL DEFAULT '',
	319	GovernmentForm char(45) NOT NULL DEFAULT '',
	320	HeadOfState char(60) DEFAULT NULL,
642.1.78 by Lee enable subselect_sj2	321	Capital int DEFAULT NULL,
1 by brian clean slate	322	Code2 char(2) NOT NULL DEFAULT '',
	323	PRIMARY KEY (Code)
	324	);
	325	CREATE TABLE t2 (
642.1.78 by Lee enable subselect_sj2	326	ID int NOT NULL,
1 by brian clean slate	327	Name char(35) NOT NULL DEFAULT '',
1 by brian clean slate	328	CountryCode char(3) NOT NULL DEFAULT '',
642.1.78 by Lee enable subselect_sj2	329	Population int NOT NULL DEFAULT '0',
1 by brian clean slate	330	PRIMARY KEY (ID),
	331	KEY CountryCode (CountryCode)
	332	);
	333	Fill the table with test data
	334	This must not use LooseScan:
	335	EXPLAIN SELECT Name FROM t1
	336	WHERE t1.Code IN (
	337	SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
	338	id select_type table type possible_keys key key_len ref rows Extra
1100.2.1 by Brian Aker First pass through removing most of the semi_join code.	339	1 PRIMARY t1 ALL NULL NULL NULL NULL 31 Using where
	340	2 SUBQUERY t2 ALL NULL NULL NULL NULL 125 Using where
1 by brian clean slate	341	SELECT Name FROM t1
	342	WHERE t1.Code IN (
	343	SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
	344	Name
	345	drop table t1, t2;
1183.3.1 by Lee Bieber Add test case for MySQL BUG #42742: crash in setup_sj_materialization, Copy_field::set	346	#
	347	# MySQL BUG #42742: crash in setup_sj_materialization, Copy_field::set
	348	#
	349	create table t3 ( c1 date) engine=innodb;
	350	insert into t3 values ('2009-10-22'),('2142-10-22');
	351	create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb;
	352	select * from t3;
	353	c1
	354	2009-10-22
	355	2142-10-22
2141.4.2 by Andrew Hutchings Implicit joins of the form "SELECT * FROM t1, t2" without WHERE or ON now error.	356	explain select 1 from t2 where c2 in (select 1 from t3 CROSS JOIN t2) and c1 in (select convert(c6,char(1)) from t2);
1183.3.1 by Lee Bieber Add test case for MySQL BUG #42742: crash in setup_sj_materialization, Copy_field::set	357	id select_type table type possible_keys key key_len ref rows Extra
	358	1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
	359	3 SUBQUERY t2 ALL NULL NULL NULL NULL 1
	360	2 SUBQUERY t2 ALL NULL NULL NULL NULL 1
	361	2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using join buffer
	362	drop table t2, t3;