Details
Minor 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
|