[MDEV-26665] Regression in IN subquery results in MariaDB 10.5 and 10.6 Created: 2021-09-22  Updated: 2022-07-29

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5.12, 10.6.4, 10.5, 10.6
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: Jeremy Evans Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

OpenBSD/amd64 and Windows/x86_64

Attachments: File foreign_key.sql     File no_foreign_key.sql    

 Description   

There is a regression in MariaDB 10.5 and 10.6 affecting subqueries accessing tables that use foreign key constraints, possibly limited to queries that use window functions. I've tested that this issue does not affect 10.2, 10.3, and 10.4. If foreign key constraints are enabled, the IN predicate filters rows it shouldn't filter in MariaDB 10.5 and 10.6. If foreign key constraints are not enabled, the IN predicate operates correctly.

See attached foreign_key.sql and no_foreign_key.sql files. The only difference between the two is in the use of FOREIGN KEY constraints when creating the tables. On MariaDB 10.2, 10.3, and 10.4, the final query in both files returns:

```
id name
1 Ar
2 Ar
```

On MariaDB 10.5 and 10.6, the final query in the no_foreign_key.sql file returns both records, but the final query in foreign_key.sql returns only one of the records:

```
id name
1 Ar
```

There are a total of 5 select queries in both files, with the first four queries showing the same results in both the foreign key and non-foreign key cases.

1) The 1st query shows that there are 2 entries in the artists table.
2) The 2nd query shows that the inner-most subquery of the common table expression returns the expected results.
3) The 3rd query shows that another of the subqueries in the common table expression returns the expected results.
4) The 4th query shows that the common table expression itself returns the expected results of ids for both rows in the artists table.
5) The 5th query shows the use of the common table expression in an IN subquery, with unexpected results of only a single row in MariaDB 10.5 and 10.6.

Inlining the common table expression does not change the behavior, so this issue doesn't appear to be related to the use of common table expressions.

Both files return expected results (the same results as MariaDB 10.2, 10.3, and 10.4) on SQLite 3.35.5.

This issue was discovered during testing of Sequel, a database library for the Ruby programming language.

 Comments   
Comment by Alice Sherepa [ 2021-09-23 ]

Thank you!
I could repeat on 10.5-10.6,
setting optimizer_switch='split_materialized=off' could be used as a temporary workaround

 
 
-- source include/have_innodb.inc
 
 
 
set optimizer_switch='split_materialized=on';
 
CREATE TABLE t1 (id int PRIMARY KEY, name varchar(25))engine=innodb;
 
CREATE TABLE t2 (id int PRIMARY KEY, name varchar(25), artist_id int, KEY (artist_id))engine=innodb;
 
CREATE TABLE t3 (id int PRIMARY KEY, name varchar(25))engine=innodb;
 
CREATE TABLE t4 (album_id int, tag_id int,  KEY (tag_id) )engine=innodb;
 
 
 
INSERT INTO t1 VALUES (1, 'Ar'), (2, 'Ar');
 
INSERT INTO t2 VALUES (1, 'Al', 1), (2, 'Al', 2);
 
INSERT INTO t3 VALUES (1, 'T'), (2, 'T');
 
INSERT INTO t4 VALUES (1, 1), (2, 2);
 
 
 
WITH cte AS
 
 ( SELECT t2.artist_id
 
  FROM t3
 
  JOIN t4 ON (t4.tag_id = t3.id)
 
  JOIN t2 ON (t2.id = t4.album_id)
 
  WHERE ( (t3.name = 'T')
 
         AND (t2.artist_id IS NOT NULL)
 
         AND ((t2.artist_id,t3.id) IN
 
               ( SELECT b,c
 
                FROM
 
                 ( SELECT t2.artist_id AS b,
 
                          t3.id AS c,
 
                          row_number() over (partition BY t2.artist_id
 
                                             ORDER BY t3.name) AS rownum
 
                  FROM t3
 
                  JOIN t4 ON (t4.tag_id = t3.id)
 
                  JOIN t2 ON (t2.id = t4.album_id) ) AS t1
 
                WHERE (rownum = 1) ) )))
 
SELECT * FROM t1
 
WHERE (t1.id IN (SELECT * FROM cte));


MariaDB [test]> WITH cte AS
 
    ->  ( SELECT t2.artist_id
 
    ->   FROM t3
 
    ->   JOIN t4 ON (t4.tag_id = t3.id)
 
    ->   JOIN t2 ON (t2.id = t4.album_id)
 
    ->   WHERE ( (t3.name = 'T')
 
    ->          AND (t2.artist_id IS NOT NULL)
 
    ->          AND ((t2.artist_id,t3.id) IN
 
    ->                ( SELECT b,c
 
    ->                 FROM
 
    ->                  ( SELECT t2.artist_id AS b,
 
    ->                           t3.id AS c,
 
    ->                           row_number() over (partition BY t2.artist_id
 
    ->                                              ORDER BY t3.name) AS rownum
 
    ->                   FROM t3
 
    ->                   JOIN t4 ON (t4.tag_id = t3.id)
 
    ->                   JOIN t2 ON (t2.id = t4.album_id) ) AS t1
 
    ->                 WHERE (rownum = 1) ) )))
 
    -> SELECT * FROM t1
 
    -> WHERE (t1.id IN (SELECT * FROM cte));
 
+----+------+
 
| id | name |
 
+----+------+
 
|  2 | Ar   |
 
+----+------+
 
1 row in set (0.010 sec)
 
 
 
MariaDB [test]> set optimizer_switch='split_materialized=off';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> WITH cte AS  ( SELECT t2.artist_id   FROM t3   JOIN t4 ON (t4.tag_id = t3.id)   JOIN t2 ON (t2.id = t4.album_id)   WHERE ( (t3.name = 'T')          AND (t2.artist_id IS NOT NULL)          AND ((t2.artist_id,t3.id) IN                ( SELECT b,c                 FROM                  ( SELECT t2.artist_id AS b,                           t3.id AS c,                           row_number() over (partition BY t2.artist_id                                              ORDER BY t3.name) AS rownum                   FROM t3                   JOIN t4 ON (t4.tag_id = t3.id)                   JOIN t2 ON (t2.id = t4.album_id) ) AS t1                 WHERE (rownum = 1) ) ))) SELECT * FROM t1 WHERE (t1.id IN (SELECT * FROM cte));
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | Ar   |
 
|  2 | Ar   |
 
+----+------+
 
2 rows in set (0.008 sec)

Comment by Jeremy Evans [ 2022-07-29 ]

MariaDB 10.8.3 returns correct results by default and with split_materialized set to both off and on. So I think this issue has been resolved.

Generated at Thu Feb 08 09:47:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.