~drizzle-trunk/drizzle/development

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	

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	t1	ALL	NULL	NULL	NULL	NULL	3	

1	PRIMARY	t10	const	PRIMARY	PRIMARY	4	const	1	Using where

select * from t1 where a in (select a from t10 where pk=12);

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	t11	ALL	NULL	NULL	NULL	NULL	1	Start temporary

1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; End temporary; Using join buffer

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	

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	

1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	

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	

1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	100.00	

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.

select * from

t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10)) 

where t1.a < 5;

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	t1	ALL	NULL	NULL	NULL	NULL	103	100.00	Using where

1	PRIMARY	t10	eq_ref	PRIMARY	PRIMARY	4	test.t1.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`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))

drop table t0, t1;

drop table t10, t11, t12;