Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
|
Test case |
CREATE TABLE t1 (f1 int); |
INSERT INTO t1 VALUES (1),(2); |
|
|
CREATE TABLE t2 (f2 INT); |
INSERT INTO t2 VALUES (1),(2); |
|
|
CREATE TABLE t3 (f3 INT); |
INSERT INTO t3 VALUES (1),(2); |
|
|
SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ; |
|
|
SELECT * FROM ( SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ) mrg; |
|
|
CREATE OR REPLACE ALGORITHM=MERGE VIEW vmerge AS |
SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ; |
|
|
SELECT * FROM vmerge; |
|
|
DROP VIEW vmerge; |
DROP TABLE t1, t2, t3; |
|
Basic query, correct result |
MariaDB [test]> SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ; |
+------+ |
| f1 |
|
+------+ |
| 1 |
|
| 2 |
|
+------+ |
2 rows in set (0.00 sec) |
|
|
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+ |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) | |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; End temporary; Using join buffer (incremental, BNL join) | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+ |
3 rows in set, 2 warnings (0.00 sec) |
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1276 | Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 | |
| Note | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2`) where ((`test`.`t2`.`f2` = `test`.`t1`.`f1`) and (`test`.`t3`.`f3` = `test`.`t1`.`f1`)) | |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
2 rows in set (0.00 sec) |
|
Derived table, wrong result |
MariaDB [test]> SELECT * FROM ( SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ) mrg; |
Empty set (0.00 sec) |
|
|
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT * FROM ( SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f1 IN ( SELECT f3 FROM t3 ) ) ) mrg; |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | |
| 3 | DEPENDENT SUBQUERY | <subquery4> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (flat, BNL join) | |
| 4 | MATERIALIZED | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+ |
4 rows in set, 2 warnings (0.00 sec) |
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1276 | Field or reference 'test.t1.f1' of SELECT #3 was resolved in SELECT #2 | |
| Note | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`t2`.`f2` from `test`.`t2` semi join (`test`.`t3`) where ((`test`.`t1`.`f1` = `test`.`t3`.`f3`) and (<cache>(`test`.`t1`.`f1`) = `test`.`t2`.`f2`)))) | |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
|
MERGE view, wrong result |
MariaDB [test]> SELECT * FROM vmerge; |
Empty set (0.00 sec) |
|
|
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT * FROM vmerge; |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | |
| 3 | DEPENDENT SUBQUERY | <subquery4> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (flat, BNL join) | |
| 4 | MATERIALIZED | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+ |
4 rows in set, 2 warnings (0.00 sec) |
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1276 | Field or reference 'test.t1.f1' of SELECT #3 was resolved in SELECT #2 | |
| Note | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`f1`,<exists>(select `test`.`t2`.`f2` from `test`.`t2` semi join (`test`.`t3`) where ((`test`.`t1`.`f1` = `test`.`t3`.`f3`) and (<cache>(`test`.`t1`.`f1`) = `test`.`t2`.`f2`)))) | |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
Results slightly differ with InnoDB vs MyISAM/Aria, but either way they are wrong.
Results from 5.5 as of ceba41c0951d1d8c9b4961772b4a088769814a66.
Also reproducible on earlier releases of 5.5.
Reproducible on 10.0 and 10.1.
Did not check 5.3.
Not reproducible on MySQL 5.6, 5.7.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Fixed by
commit b0395d8701ec49f49ad23f9917a3b2369bb49e7aAuthor: Igor Babaev <igor@askmonty.org>Date: Tue Apr 4 10:04:52 2017 -0700Fixed the bug mdev-12429 and its duplicates mdev-12145 and mdev-9886.Also fixed a wrong result for a test case for mdev-7691(the alternative one).The test cases for all these bug have materialized semi-joins usedinside dependent sub-queries.The patch actually reverts the change inroduced by Monty in 2003.It looks like this change is not valid anymore after the implementationof semi-joins.Adjusted output from EXPLAIN for many other test cases.