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

1 2 3 4 revision-id: monty@askmonty.org-20120910105319-ga1tpymia69h306w date: 2012-09-10 13:53:19 +0300 build-date: 2012-09-10 22:59:38 +0400 revno: 3531

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):

1 2 3 4 5 6 7 id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY a index NULL PRIMARY 2 NULL 2 100.00 Using where; Using index 3 DEPENDENT SUBQUERY b eq_ref PRIMARY PRIMARY 2 test.a.f1 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a.f1' of SELECT #3 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`a`.`f1` AS `f1` from `test`.`t1` `a` where exists(select 1 from `test`.`t1` `b` where (`test`.`b`.`f1` = `test`.`a`.`f1`))

Test case:

1 2 3 4 5 6 7 8 9 CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('u1'),('u2'); SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); FLUSH TABLES; SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); # Cleanup DROP TABLE t1;

Expected result:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('u1'),('u2'); SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); f1 u1 u2 FLUSH TABLES; SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); f1 u1 u2 DROP TABLE t1;

Actual result, debug build:

1 2 3 4 5 6 7 8 CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('u1'),('u2'); SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); f1 FLUSH TABLES; SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); f1 DROP TABLE t1;

Actual result, release build:

1 2 3 4 5 6 7 8 9 INSERT INTO t1 VALUES ('u1'),('u2'); SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); f1 u1 u2 FLUSH TABLES; SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); f1 DROP TABLE t1;

Environment

None

Status

Assignee

Oleksandr Byelkin

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Affects versions

10.0.0
5.3.8
5.5.27

Due date

2012/09/21

Priority

Major
Configure