The following query
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.
Also reproducible on maria/5.3 revno 3551, and on release builds 5.3.7 and 5.5.25.
Full optimizer_switch (default):
EXPLAIN (with the default optimizer_switch):
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):
It still returns 1 row instead of 2.
Note: I've set it to Minor, because join_cache_level > 4 is not default