SELECT ( 

  SELECT MIN(b) FROM t1, t2 

  WHERE b = a AND 

  ( b = alias1.b OR 

     EXISTS ( SELECT * FROM t3 )

  )

) 

FROM t2 alias1, t1 alias2, t1 alias3;

revision-id: timour@askmonty.org-20120518115201-s6byggvesqxcntkd

date: 2012-05-18 14:52:01 +0300

revno: 3404

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	100.00	

1	PRIMARY	alias3	ALL	NULL	NULL	NULL	NULL	20	100.00	Using join buffer (flat, BNL join)

1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	100	100.00	Using join buffer (incremental, BNL join)

2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	100.00	

2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where; Using join buffer (flat, BNL join)

3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1276	Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select <expr_cache><>((select min(`test`.`t2`.`b`) from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`b` = `test`.`t1`.`a`))) AS `( 

SELECT MIN(b) FROM t1, t2 

WHERE b = a AND 

( b = alias1.b OR 

EXISTS ( SELECT * FROM t3 ) 

)

)` from `test`.`t2` `alias1` join `test`.`t1` `alias2` join `test`.`t1` `alias3`

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	100.00	

1	PRIMARY	alias3	ALL	NULL	NULL	NULL	NULL	20	100.00	Using join buffer (flat, BNL join)

1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	100	100.00	Using join buffer (incremental, BNL join)

2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	100.00	Using where

2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where; Using join buffer (flat, BNL join)

3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1276	Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select <expr_cache><`test`.`alias1`.`b`>((select min(`test`.`t2`.`b`) from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and ((`test`.`t1`.`a` = `test`.`alias1`.`b`) or exists(select 1 from `test`.`t3`))))) AS `( 

SELECT MIN(b) FROM t1, t2 

WHERE b = a AND 

( b = alias1.b OR 

EXISTS ( SELECT * FROM t3 ) 

)

)` from `test`.`t2` `alias1` join `test`.`t1` `alias2` join `test`.`t1` `alias3`

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES

(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),

(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);

CREATE TABLE t2 (b INT);

INSERT INTO t2 VALUES

(4),(5),(2),(5),(1),(1),(2),(2),(2),(197),

(4),(5),(3),(1),(2),(7),(6),(1),(156),(8),

(7),(2),(6),(2),(1),(0),(7),(5),(7),(2),(1),

(80),(3),(8),(5),(0),(9),(9),(7),(0),(5),

(6),(9),(3),(91),(6),(7),(3),(161),(7),(7),

(6),(5),(8),(7),(2),(1),(3),(6),(6),(5),(0),

(7),(7),(6),(0),(0),(8),(0),(4),(0),(213),

(248),(1),(6),(6),(3),(140),(0),(7),(6),(6),

(8),(5),(8),(7),(3),(7),(3),(8),(0),(1),(3),

(6),(8),(1),(1),(9),(0),(6);

CREATE TABLE t3 (c INT);

INSERT INTO t3 VALUES (8),(3);

SELECT ( 

  SELECT MIN(b) FROM t1, t2 

  WHERE b = a AND 

  ( b = alias1.b OR 

     EXISTS ( SELECT * FROM t3 )

  )

) 

FROM t2 alias1, t1 alias2, t1 alias3;