Details
Major Description
"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 | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+ |