[MDEV-2538] LP:823169 - Diverging results with GROUP BY + NULL in ANY subquery Created: 2011-08-09  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug823169.xml    

 Description   

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

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-08-09 ]

Diverging results with GROUP BY + NULL in ANY subquery
Repeatable in maria-5.3,maria-5.2, mysql-5.5 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 );

Comment by Oleksandr Byelkin [ 2011-08-09 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
It could be connected to https://bugs.launchpad.net/maria/+bug/780425

Comment by Oleksandr Byelkin [ 2011-08-09 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
repeatable on 5.1

Comment by Oleksandr Byelkin [ 2011-08-09 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
simpilfied data set:

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

Comment by Oleksandr Byelkin [ 2011-08-09 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
Correct result is that which with rows.

Comment by Oleksandr Byelkin [ 2011-08-09 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
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.

Comment by Oleksandr Byelkin [ 2011-08-09 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
fix commited: http://lists.askmonty.org/pipermail/commits/2011-August/002169.html

Comment by Timour Katchaounov (Inactive) [ 2011-08-11 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
This bug is a duplicate of
http://bugs.mysql.com/bug.php?id=56690
Please check the relevant test case.

Comment by Oleksandr Byelkin [ 2011-08-30 ]

Re: Diverging results with GROUP BY + NULL in ANY subquery
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;

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 823169

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