Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9685

Wrong result (missing rows) with MERGE view or derived_merge, IN subqueries

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: