Bug #109206 Support unqualified asterisk anywhere in a SELECT list
Submitted: 25 Nov 2022 13:32 Modified: 25 Nov 2022 14:43
Reporter: Lukas Eder Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[25 Nov 2022 13:32] Lukas Eder
Description:
I would expect an unqualified asterisk to be available anywhere within a SELECT list, but it can only be prepended.

How to repeat:
This works:

SELECT *, 'a' FROM (SELECT 1 x) t;

Producing:

|x  |a  |
|---|---|
|1  |a  |

This doesn't work:

SELECT 'a', * FROM (SELECT 1 x) t;

Producing:

> SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM (SELECT 1 x) t
[25 Nov 2022 14:31] MySQL Verification Team
Hi Mr. Eder,

Thank you for your bug report.

We searched for anything in SQL standard from 2016 and could not find a reason for this behaviour.

This is now a verified bug.
[25 Nov 2022 14:43] Lukas Eder
The possibility to combine unqualified asterisks with other items in the ISO/IEC 9075-2:2016(E) 7.16 <query specification> is an extension to the standard by MySQL (and many others, such as e.g. PostgreSQL, SQL Server). The way I read the standard, <asterisk> and <select sublist> are mutually exclusive.

In any case, as you say, there's no reason to extend the standard only to support leading asterisks, but not trailing ones.
[25 Nov 2022 18:57] Roy Lyseng
The reference manual documents this restriction, although maybe a bit imprecise:

"Use of an unqualified * with other items in the select list may produce a parse error."

The syntax indeed allows an asterisk at the start of the select list, but not in any other position. So, this is not a bug but may qualify as a feature request.
[28 Nov 2022 12:50] MySQL Verification Team
Thank you, Roy .....

Feature request is a correct category ......