Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
5.5.25, 5.3.7
-
None
-
None
Description
The following query
SELECT a, COUNT(*) AS cnt
|
FROM t1 LEFT JOIN t2 ON (d = b)
|
WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' )
|
GROUP BY a ORDER BY a, cnt LIMIT 2
|
on the test data returns 1 row if it's run with semijoin and join_cache_level > 4, and 2 rows otherwise. 2 rows is the correct result.
bzr version-info
revision-id: psergey@askmonty.org-20120705103901-9ero4x0eifk0wtks
|
date: 2012-07-05 14:39:01 +0400
|
build-date: 2012-07-11 01:54:42 +0400
|
revno: 3462
|
Also reproducible on maria/5.3 revno 3551, and on release builds 5.3.7 and 5.5.25.
Minimal optimizer_switch:
semijoin=on
|
Full optimizer_switch (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=off
|
EXPLAIN (with the default optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t1 index a a 4 NULL 3 100.00 Using where; Using temporary; Using filesort
|
1 PRIMARY t2 ref c c 5 test.t1.a 2 100.00 Using index; Start temporary; End temporary
|
1 PRIMARY t2 ref d d 2 test.t1.b 2 100.00 Using where; Using index
|
Warnings:
|
Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
|
Note 1003 select `test`.`t1`.`a` AS `a`,count(0) AS `cnt` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t2` on(((`test`.`t2`.`d` = `test`.`t1`.`b`) and (`test`.`t1`.`b` is not null))) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` > 'k')) group by `test`.`t1`.`a` order by `test`.`t1`.`a`,count(0) limit 2
|
Test case:
|
|
SET join_cache_level = 5;
|
SET optimizer_switch = 'semijoin=on';
|
|
|
CREATE TABLE t1 (a INT NOT NULL, b CHAR(1), KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (4,'p'),(1,'q'),(8,'e');
|
|
|
CREATE TABLE t2 (c INT, d CHAR(1), KEY(c), KEY(d)) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (4,'f'),(2,'i'),(5,'h'),(3,'q'),(1,'g');
|
|
|
SELECT a, COUNT(*) AS cnt
|
FROM t1 LEFT JOIN t2 ON (d = b)
|
WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' )
|
GROUP BY a ORDER BY a, cnt LIMIT 2;
|
Expected result:
a cnt
|
1 1
|
4 1
|
Actual result:
a cnt
|
1 1
|
Please note that the aggregate function is not required, it just makes the test somewhat more sensible. The query can be modified like this (double a in ORDER BY is not a typo, it is needed for this version of the query):
SELECT a
|
FROM t1 LEFT JOIN t2 ON (d = b)
|
WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' )
|
GROUP BY a ORDER BY a, a LIMIT 2;
|
It still returns 1 row instead of 2.
Note: I've set it to Minor, because join_cache_level > 4 is not default