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
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
The bug mdev-9686 also relates to this bug (the same pattern in EXPLAIN output).
The fix for this bug was pushed into the 5.5 tree.
It should be merged into 10.0 as it is.
Yet for the test case we have to add
set optimizer_switch='exists_to_in=off'
|
in order to get the same execution plans.
I encountered this problem when working on the bug mdev-10053.
The output from EXPLAIN EXTENDED for the reported query:
MariaDB [test]> EXPLAIN EXTENDED-> 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 );+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+| 1 | PRIMARY | sq1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | || 2 | DEPENDENT SUBQUERY | sq2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (flat, BNL join) || 3 | MATERIALIZED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+4 rows in set, 3 warnings (0.00 sec)MariaDB [test]> SHOW WARNINGS;+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1276 | Field or reference 'test.sq1.pk' of SELECT #2 was resolved in SELECT #1 || Note | 1276 | Field or reference 'test.sq1.f1' of SELECT #2 was resolved in SELECT #1 || Note | 1003 | select `test`.`sq1`.`f2` AS `f2` from `test`.`t1` `sq1` where <expr_cache><`test`.`sq1`.`f1`,`test`.`sq1`.`pk`>(exists(select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where ((`test`.`sq2`.`f1` = `test`.`sq1`.`f1`) and (`test`.`sq1`.`pk` = `test`.`t1`.`f1`)))) |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+makes me think that this bug relates to the bug mdev-12145.