Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.27, 5.3.8
-
None
-
None
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
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; |