MySQL Bugs: #26066: select-statement, mixed star and named fields in

Bug #26066	select-statement, mixed star and named fields in
Submitted:	5 Feb 2007 2:27	Modified:	18 Mar 2007 17:58
Reporter:	Gerald Schade	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	5.1.12	OS:	Windows (Windows / Linux)
Assigned to:		CPU Architecture:	Any
Tags:	field list, select-statement, star

Description:
It is possible to define a field list like:
a)   "select *, f1, f2 from t1 ..."
But trying to define a field list like:
b)   "select f1, f2, * from t1 ..."
leads to an error in mysql (different in other sql dialects).
b) could be more useful then a) because known fields may be more important.
With kind regards, G.Schade

How to repeat:
()

Suggested fix:
perhaps enlarge syntax, if possible

This is not a bug. 

SQL:2003 explicitely states that the only one <asterisk> is allowed in <select_list> of the <query specification>. See SQL/Foundation, subclause 7.12.

I mean, the Standard provides for either the only unqualified asterisk in select list or numerous qualified asterisks in select sublist. So, the first case probably needs to be explicitely documented as MySQL feature. 

Otherwise the parser should be modified to conform to the SQL standard.

Bug #27235 is a duplicate of this one. As for this feature request, "Won't fix" looks like the most appropriate status.

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