Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
The following test case produces a wrong result set:
CREATE TABLE t1 (
|
pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
|
|
SELECT sq1.f2 FROM t1 AS sq1
|
WHERE EXISTS ( SELECT * FROM t1 AS sq2
|
WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
|
|
MariaDB [test]> SELECT sq1.f2 FROM t1 AS sq1 WHERE EXISTS ( SELECT * FROM t1 AS sq2 WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
|
Empty set (0.00 sec)
|
The problem is reproduced in 10.0,10.1,10.2 with the same test case if subquery_cache is set to 'off' in the optimizer switch:
set optimizer_switch='subquery_cache=off';
|
Attachments
Issue Links
- is duplicated by
-
MDEV-9685 Wrong result (missing rows) with MERGE view or derived_merge, IN subqueries
- Closed
-
MDEV-12145 Wrong result (missing rows) on query with IN and EXISTS subqueries from InnoDB tables
- Closed
- relates to
-
MDEV-9686 Wrong result (wrong values) with subquery in select list, semijoin+materialization
- Closed