"LIMIT 0" is a degenerate case.
EXISTS (SELECT ... LIMIT 0) should evaluate to FALSE, but it evaluates to TRUE.
create table t10 (a int);
|
insert into t10 values (1),(2),(3);
|
create table t12 (a int);
|
insert into t12 values (1),(2),(3);
|
LIMIT 0 query:
select * from t12 order by a limit 0;
|
Empty set (0.00 sec)
|
Make it a subquery:
select * from t10 where exists (select * from t12 order by a limit 0);
|
+------+
|
| a |
|
+------+
|
| 1 |
|
| 2 |
|
| 3 |
|
+------+
|
3 rows in set (0.00 sec)
|
mysql> explain select * from t10 where exists (select * from t12 order by a limit 0);
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|
| 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 3 | |
|
| 2 | SUBQUERY | t12 | ALL | NULL | NULL | NULL | NULL | 3 | Using filesort |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|
Major 