MySQL Bugs: #41768: Query with SELECT *, GROUP BY and AGGREGATE

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:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
A query of this form is valid:
SELECT *, aggregate(some_column) from some_table

but this is not:
SELECT aggregate(some_column), * from some_table

.. is this really a necessary (and documented) restriction?

How to repeat:
I have a simple order database with an 'order_details' table

-- this is valid
SELECT *, SUM(amount_total) AS order_amount_this_month FROM order_details GROUP BY order_id;
-- (a practical query could go like next .. but the above is enough for discussion!)
SELECT *, SUM(amount) AS order_amount_this_month FROM order_details WHERE MONTH(date_of_purchase) = MONTH(now()) and YEAR(date_of_purchase) = YEAR(now()) GROUP BY order_id ORDER BY order_amount_this_month desc;

-- this gives syntax error 1064
SELECT SUM(amount_total), * FROM order_details GROUP BY order_id;

-- however this is OK
SELECT SUM(amount_total), someothercolumn, somemoreothercolumn [,etc] FROM order_details GROUP BY order_id;

Suggested fix:
Obviously the aggregate is the most interesting part of the result here. It would be nice to be able to have it returned as first column without specifying/knowing/looking-up individual column names in the columns list of the query.

The query is of course non-optimal (should rather be considered an ad-hoc query than a tuned query), but that is *not* the discussion!

I tried to search in docs, but I may have missed (that has happened a few times before :-) )

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

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!

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;

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 ...