[MDEV-567] Wrong result from a query with correlated subquery if ICP is allowed Created: 2012-10-01  Updated: 2014-04-07  Resolved: 2014-04-07

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
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.

 Comments   
Comment by Patryk Pomykalski [ 2012-10-08 ]

The fix introduced other bug: MDEV-536

Comment by Sergei Petrunia [ 2012-10-10 ]

Fix backported and pushed into 5.3

Comment by Sergei Petrunia [ 2012-10-10 ]

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.

Comment by Sergei Petrunia [ 2012-11-02 ]

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).

Comment by Sergei Petrunia [ 2012-11-04 ]

Finished, pushed the fix into 5.5

Comment by Igor Babaev [ 2012-11-04 ]

Sergey,

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

Comment by Elena Stepanova [ 2014-04-07 ]

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.

Generated at Thu Feb 08 06:29:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.