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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None
    • 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

            psergei Sergei Petrunia
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.