LIMIT_ROWS_EXAMINED: query with the limit and NOT EXISTS, without GROUP BY or aggregate, returns rows, while the same query without the limit returns empty set

SELECT * FROM t1 AS alias1, t2 AS alias2 

WHERE NOT EXISTS ( 

  SELECT * FROM t1 LEFT OUTER JOIN t3 

    ON (d = a) 

  WHERE b <= alias1.b OR e != alias2.c  

)

LIMIT_ROWS_EXAMINED 20

revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r

date: 2012-02-13 16:52:48 +0200

build-date: 2012-02-21 07:04:39 +0400

revno: 3417

id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra

1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    3       100.00

1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    3       100.00  Using where; Using join buffer (flat, BNL join)

2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    3       100.00

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

Warnings:

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

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

Note    1003    select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(exists(select 1 from `test`.`t1` left join `test`.`t3` on((`test`.`t3`.`d` = `test`.`t1`.`a`)) where ((`test`.`t1`.`b` <= `test`.`alias1`.`b`) or (`test`.`t3`.`e` <> `test`.`alias2`.`c`))))))

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

CREATE TABLE t1 ( a INT, b INT );

INSERT INTO t1 VALUES 

  (3911,17),(3847,33),(3857,26);

CREATE TABLE t2 ( c VARCHAR(16) );

INSERT INTO t2 VALUES 

  ('English'),('French'),('German');

CREATE TABLE t3 ( d INT, e VARCHAR(32) );

INSERT INTO t3 VALUES 

  (3813,'United States'),(3814,'United States');

SELECT * FROM t1 AS alias1, t2 AS alias2 

WHERE NOT EXISTS ( 

  SELECT * FROM t1 LEFT OUTER JOIN t3 

    ON (d = a) 

  WHERE b <= alias1.b OR e != alias2.c  

);

SELECT * FROM t1 AS alias1, t2 AS alias2 

WHERE NOT EXISTS ( 

  SELECT * FROM t1 LEFT OUTER JOIN t3 

    ON (d = a) 

  WHERE b <= alias1.b OR e != alias2.c  

)

LIMIT_ROWS_EXAMINED 20;

SELECT * FROM t1 AS alias1, t2 AS alias2 

WHERE NOT EXISTS ( 

SELECT * FROM t1 LEFT OUTER JOIN t3 

ON (d = a) 

WHERE b <= alias1.b OR e != alias2.c  

);

a       b       c

SELECT * FROM t1 AS alias1, t2 AS alias2 

WHERE NOT EXISTS ( 

SELECT * FROM t1 LEFT OUTER JOIN t3 

ON (d = a) 

WHERE b <= alias1.b OR e != alias2.c  

)

LIMIT_ROWS_EXAMINED 20;

a       b       c

3847    33      English

3857    26      English

Warnings:

Warning 1930    Query execution was interrupted. The query examined at least 25 rows, which exceeds LIMIT_ROWS_EXAMINED. The query result may be incomplete.