[MDEV-27520] Wrong result upon query with GROUP BY and correlated subquery Created: 2022-01-16  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-10.5, not-10.6, not-10.7, not-10.8


 Description    

CREATE TABLE t (a INT, b varchar(1), KEY (b,a));
 
 
INSERT INTO t VALUES
 
  (1,'a'),(NULL,'a'),(NULL,'x'),(8,'x'),(6,'f'),
 
  (6,'l'),(9,'r'),(9,'s'),(7,'y'),(6,'k');
 
 
 
SELECT b, COUNT(*) FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
 
SELECT b FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
 
 
DROP TABLE t;

The first query, with COUNT, returns all expected rows:

10.2 a92f07f4

 
SELECT b, COUNT(*) FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
 
b	COUNT(*)
 
a	1
 
f	1
 
k	1
 
l	1
 
r	1
 
s	1
 
x	1
 
y	1

The second query, same but without COUNT, doesn't return rows a and x:

SELECT b FROM t AS t_outer WHERE EXISTS ( SELECT * FROM t WHERE a >= t_outer.a ) GROUP BY b;
 
b
 
f
 
k
 
l
 
r
 
s
 
y

Reproducible on 10.2-10.4, with at least MyISAM and InnoDB.
On 10.5+ it was recently fixed by this commit:

commit d314bd266491baf0954d13fa51dc22b730a6f4d1
 
Author: Monty
 
Date:   Wed Feb 2 14:09:21 2022 +0200
 
 
 
    MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery


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