Bug #9350 ALL/ANY subqueries work incorrectly with HAVING clause

• Submitted: 23 Mar 2005 9:04
• Modified: 11 Apr 2005 2:17
• Reporter: Oleksandr Byelkin
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1
• OS: Any (all)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[23 Mar 2005 9:04] Oleksandr Byelkin

Description:
following queries results do not much to results of queries in which HAVING clause replaced with WHERE one:
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);

How to repeat:
above subqueries are rewritten subqueries with WHERE clause in subselect.test
just add them after
#
# ALL/ANY test
#
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
and compare results with analog with WHERE clause

[23 Mar 2005 9:59] Oleksandr Byelkin

subqueries above are wrong, (SELECT b) should be used instead of b in HAVING clause for correct name resolving. But results are still wrong even after this change

[30 Mar 2005 7:07] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23462

[30 Mar 2005 20:14] Oleksandr Byelkin

Thank you for bugreport. bugfix is pushed into source tree marked as 4.1.11.

[11 Apr 2005 2:17] Paul DuBois

Noted in 4.1.11 changelog.