[MDEV-567] Wrong result from a query with correlated subquery if ICP is allowed - Jira

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

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2012-10-01 03:01

Igor Babaev (Inactive) made changes - 2012-10-01 03:02

Field	Original Value	New Value
Key	~~TODO-300~~	~~MDEV-567~~
Project	MP todo [ 10100 ]	MariaDB Development [ 10000 ]
Workflow	jira [ 19743 ]	defaullt [ 19744 ]

Igor Babaev (Inactive) made changes - 2012-10-01 03:03

Description

The following test case produces a wrong result set in MariaDB 5.3:
{code:sql}
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);
{code}
The returned result for the above query is:
{code:sql}
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 |
+------+------+
{code}
The EXPLAIN output for this query is:
{code:sql}
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)
{code}
If ICP is turned off the result returned by the query is correct:
{code:sql}
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)
{code}
The above test case is a simplified version of the test case for Oracle's bug#12667154 that cab 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.

The following test case produces a wrong result set in MariaDB 5.3:
{code:sql}
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);
{code}
The returned result for the above query is:
{code:sql}
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 |
+------+------+
{code}
The EXPLAIN output for this query is:
{code:sql}
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)
{code}
If ICP is turned off the result returned by the query is correct:
{code:sql}
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)
{code}
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.

Igor Babaev (Inactive) made changes - 2012-10-01 03:06

Fix Version/s

5.3.9 [ 11000 ]

Sergei Golubchik made changes - 2012-10-02 16:09

Fix Version/s		5.3.10 [ 11500 ]
Fix Version/s	5.3.9 [ 11000 ]

Sergei Petrunia made changes - 2012-10-06 20:21

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2012-10-10 12:33

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Petrunia made changes - 2012-11-02 19:22

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Reopened [ 4 ]

Sergei Golubchik made changes - 2012-11-26 17:55

Fix Version/s		5.3.12 [ 12000 ]
Fix Version/s	5.3.10 [ 11500 ]

Sergei Golubchik made changes - 2013-01-31 11:21

Fix Version/s		5.3.13 [ 12602 ]
Fix Version/s	5.3.12 [ 12000 ]

Elena Stepanova made changes - 2014-04-07 22:49

Fix Version/s	5.3.13 [ 12602 ]
Resolution		Fixed [ 1 ]
Status	Reopened [ 4 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 19744 ]

MariaDB v2 [ 43262 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 43262 ]

MariaDB v3 [ 62345 ]

Sergei Golubchik made changes - 2021-12-06 21:37

Workflow

MariaDB v3 [ 62345 ]

MariaDB v4 [ 145009 ]

People

Assignee:: Sergei Petrunia

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2012-10-01 03:01

Updated:: 2014-04-07 22:49

Resolved:: 2014-04-07 22:49

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration