Bug #18064 | GROUP BY with undetermined column definition returns results instead of error | ||
---|---|---|---|
Submitted: | 8 Mar 2006 8:55 | Modified: | 20 Apr 2006 12:24 |
Reporter: | Norbert van Nobelen | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | mysql-standard-5.0.18-linux-i686 | OS: | Linux (OpenSuSE linux 10) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[8 Mar 2006 8:55]
Norbert van Nobelen
[9 Mar 2006 10:37]
Hartmut Holzgraefe
verified in 4.1, 5.0 and 5.1, stripped down test case below: DROP TABLE IF EXISTS user; CREATE TABLE user ( userID INTEGER ); DROP TABLE IF EXISTS transaction; CREATE TABLE transaction ( transactionID INTEGER, userID INTEGER ); SELECT a.userID FROM transaction AS a, (SELECT MIN(a.transactionID) AS transactionID,b.userID FROM transaction AS a,user AS b WHERE a.userID=b.userID GROUP BY userID) AS b WHERE a.userID=b.userID; SELECT a.userID FROM transaction AS a, (SELECT MIN(a.transactionID) AS transactionID FROM transaction AS a,user AS b WHERE a.userID=b.userID GROUP BY userID) AS b WHERE a.transactionID=b.transactionID; SELECT MIN(a.transactionID) AS transactionID FROM transaction AS a,user AS b WHERE a.userID=b.userID GROUP BY userID;
[17 Apr 2006 9:41]
Georgi Kodinov
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: The behavior you are observing is due to the way MySQL resolves unqualified references in GROUP BY/ORDER BY/HAVING clauses. The test case boils down to the following two selects : This one throws an error : SELECT MIN(a.transactionID) AS transactionID FROM transaction AS a,user AS b WHERE a.userID=b.userID GROUP BY userID; And this one works (no error) : SELECT MIN(a.transactionID) AS transactionID, b.UserID FROM transaction AS a,user AS b WHERE a.userID=b.userID GROUP BY userID; As indicated in section 13.2.7 (http://dev.mysql.com/doc/refman/5.0/en/select.html) of the Reference Manual the select column aliases can be used in GROUP BY, ORDER BY, or HAVING clauses. Actually the MySQL name resolution for unqualified column references in GROUP BY, ORDER BY, or HAVING works as follows : 1. Searches the resulting column names(or aliases) in the select list 2. Searches the fields of the tables in the FROM clause. So in this particular case when it resolves the UserID in the ORDER BY clause of the second SELECT it finds a column of that name in the select list (witch is unambiguous).
[20 Apr 2006 12:24]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant product(s).