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
-