Well, just out of interest
I only have a draft of the SQL:2003, not the final version, but it says
the following.
> ISO/IEC 9075-1:2003 (E)
> 6.2 Notation provided in this International Standard
> Symbol / Meaning
> |
> The alternative operator. The vertical bar indicates that the portion
of the formula following the bar is an
alternative to the portion preceding the bar. If the vertical bar
appears at a position where it is *not enclosed*
in braces or square brackets, it specifies a *complete alternative* for
the element defined by the production
rule. If the vertical bar appears in a portion of a formula enclosed in
braces or square brackets, it specifies
alternatives for the contents of the innermost pair of such braces or
brackets.
Looking at:
<select list> ::= <asterisk> | <select sublist> [ { <comma>
<select
sublist> }... ]
I would read the definition as:
<select list> ::=
<asterisk> /* and nothing else, but the asterisk */
|
{ <select sublist> [ { <comma> <select sublist> }... ] } /* select
sublist, with no unqualified asterisk */
mysql seems to read it like this
<select list> ::= { <asterisk> | <select sublist> } [ { <comma>
<select
sublist> }... ]
Anyway this is probably just of theoretical interest, since changing it
would break way to many existing applications.
I also wasn't able to find any documentation an the asterisk at all?
Best Regards
Martin
Vladimir Shebordaev wrote:
> 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
>>
>>
>
>
