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

[11 Jul 2005 15:34] Gleb Paharenko
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.
[13 Jul 2005 10:08] Richard Cyganiak
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.
[10 Aug 2005 13:43] 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/internals/28108
[15 Aug 2005 10:07] Evgeny Potemkin
Fixed in 5.0.12, cset 1.1918.3.1
[17 Aug 2005 18:24] Paul Dubois
Noted in 5.0.12 changelog.