[MDEV-12562] [Draft] Wrong result Created: 2017-04-22  Updated: 2019-03-31  Resolved: 2019-03-31

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.1.23, 10.0.31, 10.2.6, 10.3.2

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None


 Description    

CREATE TABLE t1 (pk INT PRIMARY KEY, i INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1,1),(2,2),(3,10);
 
 
 
SELECT
 
   i,
 
  ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt
 
FROM t1 AS outer_t1;
 
 
 
SELECT
 
  ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt,
 
  GROUP_CONCAT(i)
 
FROM t1 AS outer_t1
 
GROUP BY cnt;
 
 
 
DROP TABLE t1;

This test case is very similar to MDEV-12561, with the only difference in 3rd row inserted into the table. Results are different though, the first query also seems to work wrongly:

MariaDB [test]> SELECT
 
    ->    i,
 
    ->   ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt
 
    -> FROM t1 AS outer_t1;
 
+------+------+
 
| i    | cnt  |
 
+------+------+
 
|    1 |    3 |
 
|    2 |    3 |
 
|   10 |    0 |
 
+------+------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> 
MariaDB [test]> SELECT
 
    ->   ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt,
 
    ->   GROUP_CONCAT(i)
 
    -> FROM t1 AS outer_t1
 
    -> GROUP BY cnt;
 
+------+-----------------+
 
| cnt  | GROUP_CONCAT(i) |
 
+------+-----------------+
 
|    0 | 1,2,10          |
 
+------+-----------------+
 
1 row in set (0.00 sec)

The rest is different on different versions. It should be re-checked after MDEV-12561 is fixed.

 Comments   
Comment by Elena Stepanova [ 2019-03-31 ]

I can't remember why I said that the first result is incorrect, it seems okay.
The second result was fixed by the patch for MDEV-10053

commit 97fb1f26797828427ad850b0420aaafc74205e71
 
Author: Igor Babaev
 
Date:   Fri Apr 21 14:34:24 2017 -0700
 
 
 
    Fixed bug mdev-10053.
 
    
    The implementation of the walk method for the class Item_in_subselect
 
    was missing. As a result the method never traversed the left operand
 
    of any IN subquery predicate.
 
    
    Item_exists_subselect::exists2in_processor() that performs the
 
    Exist-To-In transformation calls the walk method to collect info
 
    on outer references. As the walk method did not traverse the
 
    left operands of the IN subqueries the outer references there
 
    were not taken into account and some subqueries that were actually
 
    correlated were marked as uncorrelated. It could lead to an
 
    attempt of the materialization of such a subquery.

Generated at Thu Feb 08 07:58:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.