[MDEV-30005] Correlated subquery with aggregate function returns empty resultset Created: 2022-11-14  Updated: 2023-02-15  Resolved: 2023-02-15

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.4, 10.6.11, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.11.2, 11.0.0, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.8, 10.9.5, 10.10.3

Type: Bug Priority: Critical
Reporter: Bartek Papierski Assignee: Rex Johnston
Resolution: Fixed Votes: 0
Labels: 11.0-sel, not-10.3, regression
Environment:

Centos 7.9.2009, Aria engine, MariaDB-server 10.6.11 installed from package (http://yum.mariadb.org)

Attachments: File my.cnf    
Issue Links:
Relates
relates to MDEV-30345 DML does not find rows it is supposed to Closed

 Description   

Queries with correlated subqueries containing aggregate function return empty results.

Steps to reproduce:

CREATE TABLE `SubqueryBug` (
 
  `id` int(11) DEFAULT NULL,
 
  `datetime` timestamp NULL DEFAULT NULL
 
) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci PAGE_CHECKSUM=1;
 
 
 
INSERT INTO SubqueryBug (id,`datetime`) VALUES
 
	 (1,'2022-11-15 11:00:00.0'),
 
	 (1,'2022-11-15 10:00:00.0'),
 
	 (1,'2022-11-14 11:00:00.0'),
 
	 (2,'2022-11-13 09:00:00.0'),
 
	 (2,'2022-11-09 11:00:00.0');
 
 
 
select * FROM SubqueryBug sb where sb.id =1 and sb.datetime in (select max(datetime) from SubqueryBug WHERE id=sb.id);

Expected result:
1 2022-11-15 11:00:00.000

The query above returns empty result set while the ones below return correct result:

Use scalar query instead:

select * FROM SubqueryBug sb where sb.id =1 and sb.datetime = (select max(datetime) from SubqueryBug WHERE id=sb.id);

Use constant as id instead of reference to parent table:

select * FROM SubqueryBug sb where sb.id =1 and sb.datetime in (select max(datetime) from SubqueryBug WHERE id=1);

Also casting sb.datetime to timestamp works ok:

select * FROM SubqueryBug sb where sb.id =1 and timestamp(sb.datetime) in (select max(datetime) from SubqueryBug WHERE id=sb.id);

The bug is not present in version 10.3. I experienced the bug after upgrading from 10.3 to 10.6, but I cannot confirm if it was also present in intermediate versions 10.4 and 10.5.

 Comments   
Comment by Alice Sherepa [ 2022-11-14 ]

Thank you for the report!
I repeated as described on 10.4-10.10 with InnoDB/Aria/MyIsam.

MariaDB [test]> CREATE TABLE t1 (id int, d1 TIMESTAMP);
 
Query OK, 0 rows affected (0,043 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 (id,d1) VALUES (1,'2022-11-15 11:00:00.0'),(1,'2022-11-15 10:00:00.0'),(1,'2022-11-14 11:00:00.0');
 
Query OK, 3 rows affected (0,003 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select * FROM t1 sb where  sb.d1 in (select max(d1) from t1 WHERE id=sb.id);
 
Empty set (0,002 sec)
 
 
 
MariaDB [test]> select * FROM t1 sb where  sb.d1 = (select max(d1) from t1 WHERE id=sb.id);
 
+------+---------------------+
 
| id   | d1                  |
 
+------+---------------------+
 
|    1 | 2022-11-15 11:00:00 |
 
+------+---------------------+
 
1 row in set (0,002 sec)

Comment by Rex Johnston [ 2023-01-31 ]

this appears to be fixed...

MariaDB [test]> select * FROM t1 sb where  sb.d1 in (select max(d1) from t1 WHERE id=sb.id);
 
+------+---------------------+
 
| id   | d1                  |
 
+------+---------------------+
 
|    1 | 2022-11-15 11:00:00 |
 
+------+---------------------+
 
1 row in set (0.007 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------------+
 
| version()             |
 
+-----------------------+
 
| 10.4.28-MariaDB-debug |
 
+-----------------------+
 
1 row in set (0.002 sec)

Comment by Sergei Petrunia [ 2023-02-03 ]

Need to find out which cset fixed it. Otherwise it could be that it's just this example stopped showing the issue.

Comment by Rex Johnston [ 2023-02-15 ]

Fixed in MDEV-30345

Generated at Thu Feb 08 10:12:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.