[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 ]

Patryk Pomykalski added a comment - 2012-10-08 12:57

The fix introduced other bug: ~~MDEV-536~~

Patryk Pomykalski added a comment - 2012-10-08 12:57 The fix introduced other bug: MDEV-536

Sergei Petrunia added a comment - 2012-10-10 12:33

Fix backported and pushed into 5.3

Sergei Petrunia added a comment - 2012-10-10 12:33 Fix backported and pushed into 5.3

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

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

Sergei Petrunia added a comment - 2012-10-10 22:06

Sorry, somehow missed comment by Patryk.

Note that lp:~maria-captains/maria/5.5-show-explain, which is based on 5.5.27 and has alternative fix developed for use with SHOW EXPLAIN, doesn't produce a wrong result.

Sergei Petrunia added a comment - 2012-10-10 22:06 Sorry, somehow missed comment by Patryk. Note that lp:~maria-captains/maria/5.5-show-explain, which is based on 5.5.27 and has alternative fix developed for use with SHOW EXPLAIN, doesn't produce a wrong result.

Sergei Petrunia added a comment - 2012-11-02 19:21

have made a patch that takes out Olav's fix
(revid: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql )
and instead adds my fix ( revid:
psergey@askmonty.org-20120719115219-212cxmm6qvf0wlrb, branch: 5.5-show-explain,
then 10.0)

As a result of that, I've got a patch that can handle subqueries with pushed
index conditions. Testcase for ~~MDEV-567~~ passes with it. However, it doesn't
work with MRR+ICP. I don't have a ready testcase for this, but looking at the
code I see that it won't work. These lines in create_sort_index():

<quote>
if (table->file->inited == handler::INDEX)

{ // Save index #, save index condition join->pre_sort_index= table->file->active_index; join->pre_sort_idx_pushed_cond= table->file->pushed_idx_cond; // no need to save key_read? err= table->file->ha_index_end(); }

</quote>

will not function correctly when using DS-MRR. With DS-MRR,
table->file->inited==handler::RND (DS-MRR uses secondary handler object for
index scans and primary handler object for fetching fows).

Sergei Petrunia added a comment - 2012-11-02 19:21 have made a patch that takes out Olav's fix (revid: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql ) and instead adds my fix ( revid: psergey@askmonty.org-20120719115219-212cxmm6qvf0wlrb, branch: 5.5-show-explain, then 10.0) As a result of that, I've got a patch that can handle subqueries with pushed index conditions. Testcase for MDEV-567 passes with it. However, it doesn't work with MRR+ICP. I don't have a ready testcase for this, but looking at the code I see that it won't work. These lines in create_sort_index(): <quote> if (table->file->inited == handler::INDEX) { // Save index #, save index condition join->pre_sort_index= table->file->active_index; join->pre_sort_idx_pushed_cond= table->file->pushed_idx_cond; // no need to save key_read? err= table->file->ha_index_end(); } </quote> will not function correctly when using DS-MRR. With DS-MRR, table->file->inited==handler::RND (DS-MRR uses secondary handler object for index scans and primary handler object for fetching fows).

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

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

Sergei Petrunia added a comment - 2012-11-04 21:42

Finished, pushed the fix into 5.5

Sergei Petrunia added a comment - 2012-11-04 21:42 Finished, pushed the fix into 5.5

Igor Babaev (Inactive) added a comment - 2012-11-04 22:03

Sergey,

Do you plan to back-port the fix into 5.3:? The bug was reported against 5.3.

Igor Babaev (Inactive) added a comment - 2012-11-04 22:03 Sergey, Do you plan to back-port the fix into 5.3:? The bug was reported against 5.3.

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 added a comment - 2014-04-07 22:49

The initial reported problem was fixed in 5.3.10 by the backport of the Oracle fix:

revno: 3586

revision-id: psergey@askmonty.org-20121010052122-1uua5ogs8pyoanbf

parent: sanja@montyprogram.com-20121005092655-1xfcw36gsdyhawu5

committer: Sergey Petrunya <psergey@askmonty.org>

branch nick: 5.3

timestamp: Wed 2012-10-10 09:21:22 +0400

message:

  Backport of: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql

  .. into MariaDB 5.3

  Fix for Bug#12667154 SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT

...

So, the test case from the description does not fail anymore.
The fix introduced the bug ~~MDEV-614~~/~~MDEV-536~~. This other bug was only fixed in 5.5 and up, and is still reproducible on 5.3. If it's necessary to fix it in 5.3, ~~MDEV-614~~ can be re-opened, but I suppose it makes no sense to keep this one open any longer, so I close it as fixed.

Elena Stepanova added a comment - 2014-04-07 22:49 The initial reported problem was fixed in 5.3.10 by the backport of the Oracle fix: revno: 3586 revision-id: psergey@askmonty.org-20121010052122-1uua5ogs8pyoanbf parent: sanja@montyprogram.com-20121005092655-1xfcw36gsdyhawu5 committer: Sergey Petrunya <psergey@askmonty.org> branch nick: 5.3 timestamp: Wed 2012-10-10 09:21:22 +0400 message: Backport of: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql .. into MariaDB 5.3 Fix for Bug#12667154 SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT ... So, the test case from the description does not fail anymore. The fix introduced the bug MDEV-614 / MDEV-536 . This other bug was only fixed in 5.5 and up, and is still reproducible on 5.3. If it's necessary to fix it in 5.3, MDEV-614 can be re-opened, but I suppose it makes no sense to keep this one open any longer, so I close it as fixed.

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