Diverging results with GROUP BY + NULL in ANY subquery

[Philip Stoev]

The following 2 queries:

SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );

are equivalent and yet they return identical results. The first query returns no rows, the second returns rows.

Test case:

CREATE TABLE t1 (a int(11), b varchar(1));
INSERT INTO t1 VALUES (NULL,'x'),(8,'d'),(1,'r'),(9,'f'),(4,'y'),(3,'u'),(2,'m'),(NULL,NULL),(2,'o'),(NULL,'w'),(6,'m'),(7,'q'),(2,NULL),(5,'d'),(7,'g'),(6,'x'),(6,'f'),(2,'p'),(9,'j'),(6,'c');

SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );

Repeatable in maria-5.3,maria-5.2, mysql-5.5

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It could be connected to https://bugs.launchpad.net/maria/+bug/780425

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

repeatable on 5.1

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

simpilfied data set:

INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

Correct result is that which with rows.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

in max/min subquery we should ignore NULL values (if NULL is not the only value). i.e. when we are finding MAX NULL should be less then everything when we are finding min NULL should be greater then everything.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

fix commited: http://lists.askmonty.org/pipermail/commits/2011-August/002169.html

[Timour Katchaounov (timour)]

This bug is a duplicate of
http://bugs.mysql.com/bug.php?id=56690
Please check the relevant test case.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

This sest suite will show number of erros in both methods of calculating max/min subqueries:

CREATE TABLE t1 (a int(11), b varchar(1));
INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');

SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b );

SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b );

delete from t1;
INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g');

SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b );

SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b );
SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 );
SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b );

drop table t1;