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

Wrong result (missing rows) on query with IN and EXISTS subqueries from InnoDB tables

    XMLWordPrintable

Details

    • 10.2.6-3

    Description

      Results are different before and after ANALYZE TABLE.

      Result before ANALYZE (wrong)

      MariaDB [test]> SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
      Empty set (0.00 sec)
      

      Execution plan before ANALYZE

      +------+--------------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------------------------------------------+
      | 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 | Using where                                                  |
      |    2 | DEPENDENT SUBQUERY | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func       |    1 |   100.00 |                                                              |
      |    2 | DEPENDENT SUBQUERY | t2          | index  | i2            | i2           | 5       | NULL       |    3 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
      |    2 | DEPENDENT SUBQUERY | t3          | ref    | i3            | i3           | 5       | test.t2.i2 |    4 |   100.00 | Using where; Using index                                     |
      |    3 | MATERIALIZED       | t3          | ALL    | NULL          | NULL         | NULL    | NULL       |    8 |   100.00 | Using where                                                  |
      |    3 | MATERIALIZED       | t4          | ref    | i4            | i4           | 5       | test.t3.f3 |    1 |   100.00 | Using where                                                  |
      +------+--------------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------------------------------------------+
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 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` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3` join `test`.`t4`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t4`.`i4` = `test`.`t3`.`f3`) and (`test`.`t4`.`f4` <> `test`.`t3`.`f3`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)))) |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
      

      Result after ANALYZE (correct)

      MariaDB [test]> ANALYZE TABLE t1, t2, t3, t4;
      ...
      MariaDB [test]> SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
      +------+
      | f1   |
      +------+
      |    6 |
      +------+
      1 row in set (0.01 sec)
      

      Execution plan after ANALYZE

      +------+--------------------+-------+-------+---------------+------+---------+------------+------+----------+--------------------------------------------------------------+
      | 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 | Using where                                                  |
      |    2 | DEPENDENT SUBQUERY | t3    | ALL   | NULL          | NULL | NULL    | NULL       |    8 |   100.00 | Using where                                                  |
      |    2 | DEPENDENT SUBQUERY | t4    | ref   | i4            | i4   | 5       | test.t3.f3 |    1 |   100.00 | Using where; FirstMatch                                      |
      |    2 | DEPENDENT SUBQUERY | t2    | index | i2            | i2   | 5       | NULL       |    3 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
      |    2 | DEPENDENT SUBQUERY | t3    | ref   | i3            | i3   | 5       | test.t2.i2 |    1 |   100.00 | Using where; Using index                                     |
      +------+--------------------+-------+-------+---------------+------+---------+------------+------+----------+--------------------------------------------------------------+
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 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` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3` join `test`.`t4`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t4`.`i4` = `test`.`t3`.`f3`) and (`test`.`t4`.`f4` <> `test`.`t3`.`f3`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)))) |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Test case

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f1 INT) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (4),(6);
       
      CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB;
      INSERT INTO t2 VALUES (8),(7),(1);
       
      CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB;
      CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
      INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
       
      CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB;
      INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1);
       
      SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
      ANALYZE TABLE t1,t2,t3,t4;
      SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
       
      DROP VIEW v3;
      DROP TABLE t1, t2, t3, t4;
      

      The problem isn't always reproducible right away. Here is the alternative technical test case (only to reproduce it more easily, don't put it into the test suite!)

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f1 INT) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (4),(6);
       
      CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB;
      INSERT INTO t2 VALUES (8),(7),(1);
       
      CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB;
      CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
      INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
       
      CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB;
      INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1);
       
      --let $query = SELECT COUNT(*) FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) )
       
      --let $try = 100
      --let $found_zero= 0
      --let $found_non_zero = 0
      --let $printed_zero_plan= 0
      --let $printed_non_zero_plan= 0
       
      while ($try)
      {
          --let $res= `$query`
          dec $try;
          --echo # 
          if ($res)
          {
              --let $found_non_zero= $res
              if (!$printed_non_zero_plan)
              {
                  --echo #------- NON-ZERO PLAN -------
                  eval EXPLAIN EXTENDED $query;
                  --let $printed_non_zero_plan= 1
                  --echo #-----------------------------
              }
          }
          if (!$res)
          {
              --let $found_zero= 1
              if (!$printed_zero_plan)
              {
                  --echo #------- ZERO PLAN -------
                  eval EXPLAIN EXTENDED $query;
                  --let $printed_zero_plan= 1
                  --echo #-------------------------
              }
          }
          if ($found_zero)
          {
              if ($found_non_zero)
              {
                  --let $try= 0
              }
          }
          --disable_query_log
          --disable_result_log
          ANALYZE TABLE t1,t2,t3,t4;
          --enable_result_log
          --enable_query_log
      }
       
      --echo #-------- RESULT --------
      if ($found_zero)
      {
          --echo # Found 0
      }
      if ($found_non_zero)
      {
          --echo # Found $found_non_zero
      }
      --echo #------------------------
       
      DROP VIEW v3;
      DROP TABLE t1, t2, t3, t4;
      

      The indication that the problem was reproduced is this output at the end of the test:

      #-------- RESULT --------
      # Found 0
      # Found 1
      #------------------------
      

      Before it, there will also be plans for the zero- and non-zero results.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.