Guys,
if we refer to the SQL:2003 Part 2, Foundation we would see that
it's subclause 7.12 defines the query specification in the
following manner
<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
<select list> ::=
<asterisk>
| <select sublist> [ { <comma> <select sublist> }... ]
<select sublist> ::=
<derived column>
| <qualified asterisk>
<qualified asterisk> ::=
<asterisked identifier chain> <period> <asterisk>
| <all fields reference>
<asterisked identifier chain> ::=
<asterisked identifier> [ { <period> <asterisked identifier>
}... ]
<asterisked identifier> ::= <identifier>
etc. A few paragraphs below we can find the following case study:
"a) If the <select list> “*” is simply contained in a <subquery>
that is immediately contained in an <exists predicate>, then the
<select list> is equivalent to a <value expression> that is an
arbitrary <literal>.
b) Otherwise, the <select list> “*” is equivalent to a <value
expression> sequence in which each <value expression> is a column
reference that references a column of T and each column of T is
referenced exactly once. The columns are referenced in the
ascending sequence of their ordinal position within T."
By the way, optimizer makes use of the first case in it's
Item_exists_subselect optimization effort.
The fist case in the bug report is the unqualified asterisk in
the head of select sublist. Probably, this should be explicitly
documented as MySQL feature due to somebody could rely on such a
behavior so far. Otherwise the parser should be modified to
reduce the only unqualified asterisk as select list and raise the
syntax error even if it is the first element in select sublist.
The second case must be recognized as syntactically incorrect
qualified asterisk in select sublist.
I guess the whole mess up is technically due to LR nature of
yacc, so the parser behavior is likely to be slightly clarified
to explicitly refuse unqualified asterisks in select sublist.
In the hope it helps.
Regards,
Vladimir
P.S. Please, let me know if the MySQL Internals mailing list
rules prohibit me from quoting documents that is not available to
the public for free. By now I'm doing it totally ignorant.
Ignorance is a bliss, you know... Anyways I gave full reference
to the source.
Chad MILLER пишет:
> On 16 Mar 2007, at 15:42, Martin Friebe wrote:
>> I believe to remember there have been feature requests, about the
>> placement options for the * in the select
>> ( http://bugs.mysql.com/bug.php?id=26066 )
>>
>> currently the * must be the first in the list.
>> select *, field from table => ok
>> select field, * from table => not ok
>>
>> This appears to be fixable in the parser with little effort.
>> So the questions are:
>> - Is it SQL standard conform?
>> - Is it a feature that would be wanted?
>>
>> If it is "yes", I can supply a patch.
>>
>> One more question, if "yes"
>>
>> Should "select *, * from table" be allowed?
>> My first thought would be "no", but then you can already do:
>> "select table.*, table.* from table from table"
>
> Hi Martin. Offhand, I don't know why this special case exists. Looking
> at the parser, I suspect that it's probably only a case of someone
> wishing to avoid shift/reduce conflicts and not thinking of these
> cases. I very much would like to see a patch, along with some test
> results that show that such a construction doesn't confuse the
> optimizer, which is the only potential problem that comes to mind.
>
> I also say that "SELECT *, *, *, * FROM tablename" should be allowed also.
>
> - chad
>
> --
> Chad Miller, Software Developer chad@stripped
> MySQL Inc., www.mysql.com
> Orlando, Florida, USA 13-20z, UTC-0500
> Office: +1 408 213 6740 sip:6740@stripped
>
>
