| Bug #7320 | wrong response, SUM(..) and correlated subquery after HAVING | ||
|---|---|---|---|
| Submitted: | 15 Dec 2004 15:41 | Modified: | 8 Mar 2007 7:56 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Any (any) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[15 Dec 2004 15:43]
Matthias Leich
test case with some comments
Attachment: ml63.test (application/test, text), 2.91 KiB.
[1 Nov 2006 17:41]
Matthias Leich
Tests with mysql-5.0 ChangeSet@1.2290, 2006-10-24:
1. All problems within the NIST tests related to this bug disappeared.
2. The protocol of testcase ml63.test looks good.
I guess the problem was somewhere in history fixed.
The original problem was:
CREATE TABLE t1
(EMPNUM CHAR(3) NOT NULL,
PNUM CHAR(3) NOT NULL,
HOURS DECIMAL(5),
UNIQUE(EMPNUM,PNUM));
CREATE TABLE t2
(PNUM CHAR(3) NOT NULL UNIQUE,
PNAME CHAR(20),
PTYPE CHAR(6),
BUDGET DECIMAL(9),
CITY CHAR(15));
SELECT PNUM, SUM(HOURS) FROM t1
GROUP BY PNUM
HAVING EXISTS (SELECT PNAME FROM t2
WHERE t2.PNUM = t1.PNUM
AND SUM(t1.HOURS) > t2.BUDGET / 200);
--> ERROR HY000: Invalid use of group function
Now I get an empty result set.
[8 Mar 2007 7:56]
Igor Babaev
This is a duplicate of bug #12762, fixed in 5.0 and closed 21.12.2005.
Description: I have a test case (with correct syntax) derived from the NIST tests, where NIST expects success but MySQL 4.1 and 5.0 give the error message ERROR HY000: Invalid use of group function Please look into my test cases. It contains the original NIST test case and similar simplified test cases showing the same or different errors or success. It seems that MySQL dislikes the combination: aggregat function within the outer query and correlated subquery after HAVING My environment: - Intel PC with Linux(SuSE 9.1) - MySQL compiled from source Version 4.1 ChangeSet@1.2092.151.1, 2004-12-14 Version 5.0 ChangeSet@1.1742, 2004-12-14 How to repeat: Please use my attached test file ml63.test , copy it to mysql-test/t ./mysql-test-run ml63