|
The following query
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
|
on the test data returns two rows, while the same query without LIMIT_ROWS_EXAMINED clause returns an empty set.
bzr version-info
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
|
EXPLAIN:
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`))))))
|
optimizer_switch:
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
|
Test case:
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;
|
Result:
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.
|
|