`You have an error in your SQL syntax;` when using * in query-CodePudding

I have query and it works

select *, id, from_unixtime(start_timestamp) from log limit 1;

This also works fine:

select * from log limit 1;

Why I get error if move * to the end?

select id, from_unixtime(start_timestamp), * from log limit 1;

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 log limit 1' at line 1

CodePudding user response：

The reason is that they implemented a short-cut in the SQL parser.

The SQL-2003 specification shows the grammar rules for SELECT as follows:

<query specification> ::=
    SELECT [ <set quantifier> ] <select list> <table expression>

<select list> ::=
    <asterisk>
  | <select sublist> [ { <comma> <select sublist> }... ]

<select sublist> ::=
    <derived column>
  | <qualified asterisk>

That shows that SELECT * is mutually exclusive with a list of comma-separated columns or qualified asterisk. If you use an unqualified * then that must be the whole select-list. To implement this grammar, they need both a <select list> rule and a <select sublist> rule.

But MySQL's SQL parser Yacc file shows:

query_specification:
      SELECT_SYM
      select_options
      select_item_list
      ...

select_item_list:
      select_item_list ',' select_item
    | select_item
    | '*'

They've basically merged the rules for <select list> and <select sublist> into one recursive rule. So their rule for select_item_list allows SELECT *, <select_item>, <select_item>, ... whereas by the SQL-2003 standard, it should not allow that.

As a consequence of this short-cut in their grammar, they do not allow the * wildcard to be the trailing item in the select_item_list.

If MySQL were to "fix" this issue now, to make it comply with the standard syntax rules, it would undoubtedly break thousands of legacy apps that depend on the nonstandard syntax.

You can make your code comply with the standard syntax by avoiding SELECT *, ... syntax. Only use SELECT * FROM .... You may also legally use qualified asterisk, like SELECT t1.*, ....