Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4, 10.6.11, 10.5, 10.6, 10.7, 10.8, 10.9
-
Centos 7.9.2009, Aria engine, MariaDB-server 10.6.11 installed from package (http://yum.mariadb.org)
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-