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

Wrong result from a query with correlated subquery if ICP is allowed

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      The following test case produces a wrong result set in MariaDB 5.3:

      CREATE TABLE t1 (a int, b int, INDEX idx(a));
      INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1);
       
      CREATE TABLE t2 (a int, b int, INDEX idx(a));
      INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4);
       
      CREATE TABLE t3 (a int, b int);
      INSERT INTO t3 VALUES (1,0), (1,1), (1,3);
       
      SELECT * FROM t3
        WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
                      WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
                            AND t3.b = t1.b
                    GROUP BY t1.b);

      The returned result for the above query is:

      MariaDB [test]> SELECT * FROM t3
          ->   WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
          ->                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
          ->                       AND t3.b = t1.b
          ->               GROUP BY t1.b);
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    0 |
      |    1 |    3 |
      +------+------+

      The EXPLAIN output for this query is:

       
      MariaDB [test]> EXPLAIN
          -> SELECT * FROM t3
          ->   WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
          ->                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
          ->                       AND t3.b = t1.b
          ->               GROUP BY t1.b);
      +----+--------------------+-------+-------+---------------+------+---------+-----------+------+----------------------------------------------------+
      | id | select_type        | table | type  | possible_keys | key  | key_len | ref       | rows | Extra                                              |
      +----+--------------------+-------+-------+---------------+------+---------+-----------+------+----------------------------------------------------+
      |  1 | PRIMARY            | t3    | ALL   | NULL          | NULL | NULL    | NULL      |    3 | Using where                                        |
      |  2 | DEPENDENT SUBQUERY | t1    | range | idx           | idx  | 5       | NULL      |    2 | Using index condition; Using where; Using filesort |
      |  2 | DEPENDENT SUBQUERY | t2    | ref   | idx           | idx  | 5       | test.t1.a |    2 | Using index condition                              |
      +----+--------------------+-------+-------+---------------+------+---------+-----------+------+----------------------------------------------------+
      3 rows in set (0.00 sec)

      If ICP is turned off the result returned by the query is correct:

      MariaDB [test]> set optimizer_switch='index_condition_pushdown=off';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t3
          ->   WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
          ->                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
          ->                       AND t3.b = t1.b
          ->               GROUP BY t1.b);
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    0 |
      |    1 |    1 |
      |    1 |    3 |
      +------+------+
      3 rows in set (0.00 sec)

      The above test case is a simplified version of the test case for Oracle's bug#12667154 that can be found in http://lists.mysql.com/commits/143149
      The fix itself (without any test case) was pulled into MariaDB 5.5.
      The validity of the fix should be re-checked.

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: