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).
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):
Actual result, debug build:
Actual result, release build: