MySQL Bugs: #11864: non unique names are allowed in subquery

Bug #11864	non unique names are allowed in subquery
Submitted:	11 Jul 2005 15:34	Modified:	17 Aug 2005 18:24
Reporter:	Gleb Paharenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.7/5.0.10 BK	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
First mentioned at:
  http://lists.mysql.com/mysql/186368
According to 
  http://dev.mysq.com/doc/mysql/en/unnamed-views.html

Any columns in the subquery select list must have unique names.
However, such subqueries are allowed:
  mysql> select * from (select * from tb1,tb2) as foo;
+------+------+
| a    | a    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

  

How to repeat:
CREATE TABLE `tb1` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `tb2` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into tb1 set a=1;
insert into tb2 set a=2;
select * from (select * from tb1,tb2) as foo;
+------+------+
| a    | a    |
+------+------+
|    1 |    2 |
+------+------+

Suggested fix:
Fix this or change the documentation.

As noted in the original email, using the ambiguous name sometimes causes an error and sometimes refers to the first column.

No error: SELECT A FROM (SELECT * FROM Tbl1, Tbl2) AS foo;
Error: SELECT * FROM (SELECT * FROM Tbl1, Tbl2) AS foo ORDER BY A;

This seems like a bug to me even if ambiguous names are allowed in principle.

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/internals/28108

Fixed in 5.0.12, cset 1.1918.3.1

Noted in 5.0.12 changelog.