LP:1046882 - Wrong result (missing rows) with a 2nd-level EXISTS subquery

Description

Originally filed by Daniel Heimann in Launchpad: https://bugs.launchpad.net/maria/+bug/1046882

The following query
SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) )
on the test data returns an empty result set while there are rows in the table t1.

Please note that the behavior is different on a debug and release builds which I tried: on a debug build, the query seems to always return the wrong result, while on a release build it starts happening after some other action on the table: the reporter encountered it with SHOW FULL COLUMNS FROM t1, I also tried ANALYZE TABLE t1 an FLUSH TABLES, each of them causes the same effect. The provided test case uses the latter. I tried two machines and observed the same, but it might still depend on the build, system or the box.

Reproducible with the default optimizer_switch, as well as with all OFF values except for in_to_exists=on, or with all OFF values except for materialization=on (either of them is required to execute the query).

bzr version-info

Also reproducible on 5.5.25 and 5.5.27 releases, on 5.3 tree and 10.0-base tree.
Could not reproduce on MariaDB 5.2, MySQL 5.5, MySQL 5.6.

EXPLAIN (with the default optimizer_switch):

Test case:

Expected result:

Actual result, debug build:

Actual result, release build:

Environment

None

Status

Assignee

Oleksandr Byelkin

Reporter

Elena Stepanova

Labels

None

Fix versions

Affects versions

Due date

2012/09/21

Priority

Major
Configure