Bug #41768 | Query with SELECT *, GROUP BY and AGGREGATE | ||
---|---|---|---|
Submitted: | 27 Dec 2008 21:42 | Modified: | 12 Jan 2009 19:15 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | any | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[27 Dec 2008 21:42]
Peter Laursen
[27 Dec 2008 23:09]
Peter Laursen
I post a simplified table example. This should be enough (omitting JOINs, ORDER BY and WHERE in the real world example) CREATE TABLE `order_details` ( `order_id` int(11) NOT NULL auto_increment, `customer_id` int(11), `email` varchar(250), `product_id` int(11), `amount` int(11), PRIMARY KEY (`order_id`)); SELECT SUM(amount), * FROM order_details GROUP BY order_id; -- error SELECT *, SUM(amount) FROM order_details GROUP BY order_id; -- success SELECT SUM(amount), email FROM order_details GROUP BY order_id; -- success
[27 Dec 2008 23:56]
Peter Laursen
ok .. my simplified example should rather be like (for the table example to make sense in the context) CREATE TABLE `order_details` ( `order_id` int(11) NOT NULL, `customer_id` int(11) default NULL, `email` varchar(250) default NULL, `product_id` int(11) NOT NULL, `amount_total` int(11) default NULL, PRIMARY KEY (`order_id`,`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 .. but for discussion of the statement syntax it does not really matter! The same statements fails/succeeds with this example!
[29 Dec 2008 6:30]
Sveta Smirnova
Thank you for the report. Verified as described: there is no documentation about this limitation. Workaround: SELECT SUM(amount), order_details.* FROM order_details GROUP BY order_id;
[12 Jan 2009 19:15]
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 products. Duplicate of #26066, but I have updated http://dev.mysql.com/doc/refman/5.1/en/select.html to reflect the behavior: A select list consisting only of a single unqualified * can be used as shorthand to select all columns from all tables: SELECT * FROM t1 INNER JOIN t2 ... tbl_name.* can be used as a qualified shorthand to select all columns from the named table: SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ... Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference SELECT AVG(score), t1.* FROM t1 ...