[MDEV-9685] Wrong result (missing rows) with MERGE view or derived_merge, IN subqueries Created: 2016-03-04  Updated: 2018-06-11  Resolved: 2018-06-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-12429 Wrong result from a query with IN sub... Closed

 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.



 Comments   
Comment by Varun Gupta (Inactive) [ 2018-06-11 ]

Fixed by

commit b0395d8701ec49f49ad23f9917a3b2369bb49e7a
 
Author: Igor Babaev <igor@askmonty.org>
 
Date:   Tue Apr 4 10:04:52 2017 -0700
 
 
 
    Fixed 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 used
 
    inside 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 implementation
 
    of semi-joins.
 
    Adjusted output from EXPLAIN for many other test cases.

Generated at Thu Feb 08 07:36:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.