Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
Re: Diverging results with GROUP BY + NULL in ANY subquery
fix commited: http://lists.askmonty.org/pipermail/commits/2011-August/002169.html
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.
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;
Launchpad bug id: 823169
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 );