List:Internals« Previous MessageNext Message »
From:Peter Gulutzan Date:March 21 2007 5:23pm
Subject:Re: select *, more_fields
View as plain text  
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


Thread
select *, more_fieldsMartin Friebe16 Mar
  • Re: select *, more_fieldsChad MILLER17 Mar
    • Re: select *, more_fieldsMartin Friebe17 Mar
      • engine agnostic test [Re: select *, more_fields]Martin Friebe17 Mar
    • Re: select *, more_fieldsVladimir Shebordaev17 Mar
      • Re: select *, more_fieldsMartin Friebe18 Mar
      • Re: select *, more_fieldsPeter Gulutzan21 Mar
        • draft of sql2003 / quoting [Re: select *, more_fields]Martin Friebe21 Mar