Hi Vladimir,
On Sun, 2007-03-18 at 00:45 +0300, 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.
>
I assume the bug report is this in mysql.bugs.com
Bug #27249 table_wild with alias: select t1.* as something
> 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.
>
You are correct, in standard SQL an unqualified asterisk would
be alone in the select list.
> 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.
>
I am not a lawyer, but: quoting is legal and normal behaviour,
we all do it.
> 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
> >
> >
>
--
Peter Gulutzan, Senior Software Architect
MySQL AB, www.mysql.com
Office: +1 780 472-6838
Mobile: +1 780 904-0297
VoIP: +1 408 213-6654
