[#MDEV-521] LP:1046882 - Wrong result (missing rows) with a 2nd-level EXISTS subquery

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

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

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:

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:

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:

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:

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;

[MDEV-521] LP:1046882 - Wrong result (missing rows) with a 2nd-level EXISTS subquery Created: 2012-09-10 Updated: 2012-09-21 Due: 2012-09-21 Resolved: 2012-09-21
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.0, 5.5.27, 5.3.8
Fix Version/s:	10.0.0, 5.5.28, 5.3.9

[MDEV-521] LP:1046882 - Wrong result (missing rows) with a 2nd-level EXISTS subquery Created: 2012-09-10 Updated: 2012-09-21 Due: 2012-09-21 Resolved: 2012-09-21