Wrong result (missing row) with semijoin, join_cache_level>4, LEFT JOIN, GROUP BY, ORDER BY, LIMIT

Description

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.

bzr version-info

Also reproducible on maria/5.3 revno 3551, and on release builds 5.3.7 and 5.5.25.

Minimal optimizer_switch:

Full optimizer_switch (default):

EXPLAIN (with the default optimizer_switch):

Test case:

Expected result:

Actual result:

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

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Affects versions

Priority

Minor
Configure