Bug #27249 table_wild with alias: select t1.* as something
Submitted: 18 Mar 2007 16:45 Modified: 6 Mar 2010 19:57
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S4 (Feature request)
Version:5.0.38 4.1.23 OS:Any (*)
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: alias, Contribution, wildcard
Triage: D5 (Feature request)

[18 Mar 2007 16:45] Martin Friebe
Description:
you can specify an alias for a table_willcard:

 select t1.* as 'foo' from t1;

It is ignored, but it should not be allowed. the following seems to be from sql99 (and is the only thing I could find) / not verified

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

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

<derived column> ::= <value expression> [ <as clause> ]

<qualified asterisk> ::=
		<asterisked identifier chain> <period> <asterisk>
	|	<all fields reference> 
-- end quote

but according to this the qualified asterisk, should not have an alias

How to repeat:
drop table if exists t1;
create table t1 (a int);

select t1.* as 'foo' from t1;

Suggested fix:
see patch (includes patch to existing alias test)
[18 Mar 2007 16:45] Martin Friebe
patch and test

Attachment: table_wild.patch (text/x-patch), 4.27 KiB.

[18 Mar 2007 17:10] Valeriy Kravchuk
Thank you for a bug report and patch. Bug verified just as described, also - with 5.0.37.

Draft version of SQL:2003 standard has the same definishion of <qualified asterisk> as you quoted.
[21 Mar 2007 17:43] Peter Gulutzan
Since MySQL's behaviour doesn't actually contradict the documentation
(the reference manual is silent), I mark this as a feature request.

That does not affect whether a patch can go in the community tree, though.
[24 Mar 2007 15:57] Martin Friebe
corrected spaces, added tests

Attachment: table_wild.patch (text/x-patch), 18.65 KiB.

[6 Sep 2007 16:49] Konstantin Osipov
5.2 only.
[8 Oct 2007 11:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35093

ChangeSet@1.2610, 2007-10-08 08:47:51-03:00, davi@moksha.local +3 -0
  Bug#27249 table_wild with alias: select t1.* as something
  
  Aliases to table wildcards are silently ignored, but they should
  not be allowed as it is non-standard and currently useless. There
  is not point in having a alias to a wildcard of column names.
  
  The solution is to rewrite the select_item rule so that aliases
  for table wildcards are not accepted.
  
  Contribution by Martin Friebe
[9 Oct 2007 23:31] Marc Alff
This report could be arguably qualified either

- as a feature / not a bug:
MySQL has the right to support non standard extensions,
like "select t1.* as alias"

- as a bug:
Clearly this was not intended, has no meaning,
and is an "accidental" syntax allowed

Considering that the said syntax has no harmful effect (the alias is ignored)
in 5.1 or earlier, this fix will go into the 5.2 code base only.

The technical change itself is trivial and low risk,
and could be back ported any time (but there is no real need for it).

Thanks to Martin Friebe for spotting this, and for the solution.
[19 Oct 2007 19:25] Marc Alff
Pushed in 5.2.6
[24 Oct 2007 18:52] Paul Dubois
Noted in 5.2.6 changelog.

Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no
longer accepted and result in an error. Previously, such aliases were
ignored silently.
[29 Oct 2007 17:36] Paul Dubois
5.2.6 changes will appear in 6.0.3 instead.
[10 Nov 2009 20:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90007

2923 Davi Arnaut	2009-11-10
      Backport of Bug#27249 to mysql-next-mr
      ------------------------------------------------------------
      revno: 2476.784.4
      revision-id: sp1r-davi@moksha.local-20071008114751-46069
      parent: sp1r-davi@moksha.local-20071003002731-48537
      committer: davi@moksha.local
      timestamp: Mon 2007-10-08 08:47:51 -0300
      message:
        Bug#27249 table_wild with alias: select t1.* as something
      
        Aliases to table wildcards are silently ignored, but they should
        not be allowed as it is non-standard and currently useless. There
        is not point in having a alias to a wildcard of column names.
      
        The solution is to rewrite the select_item rule so that aliases
        for table wildcards are not accepted.
      
        Contribution by Martin Friebe
     @ mysql-test/r/alias.result
        Add test case result for Bug#27249
     @ mysql-test/t/alias.test
        Add test case for Bug#27249
     @ sql/sql_yacc.yy
        Split up select_item rule so that aliases for table wildcards
        are not accepted by the parser.
[10 Nov 2009 20:45] Davi Arnaut
Queued to mysql-next-mr-runtime
[20 Nov 2009 12:54] Bugs System
Pushed into 5.6.0-beta (revid:davi.arnaut@sun.com-20091119234808-xbjpkwaxjt5x5c0b) (version source revid:davi.arnaut@sun.com-20090626124624-m4wolyo5193j4cu7) (merge vers: 5.4.4-alpha) (pib:13)
[20 Nov 2009 12:57] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:davi.arnaut@sun.com-20090626124624-m4wolyo5193j4cu7) (merge vers: 5.4.4-alpha) (pib:13)
[22 Nov 2009 0:10] Paul Dubois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 11:01] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:davi.arnaut@sun.com-20090626124624-m4wolyo5193j4cu7) (merge vers: 5.4.4-alpha) (pib:16)
[6 Mar 2010 19:57] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.