MySQL Bugs: #12993: View column raname broken in subselect

Bug #12993	View column raname broken in subselect
Submitted:	5 Sep 2005 15:56	Modified:	14 Sep 2005 2:51
Reporter:	Mika Vesterholm
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.12	OS:	Microsoft Windows (WinXP)
Assigned to:	Evgeny Potemkin	Target Version:

Description:

Following SQL was working in 5.0.(7?) but stopped working when i updated to 5.0.12.

select * from (
select COLA as COLB from SOMEVIEW ) as v where v.COLB = 'AR';

This gives now error Unknown column 'v.COLB' in 'where clause'

How to repeat:
Create a table SOMETABLE with column COLA. Create view SOMEVIEW which is just select *
from SOMETABLE. Try to run

select * from (
select COLA as COLB from SOMEVIEW ) as v where v.COLB = 'AR';

If you replace the SOMEVIEW with SOMETABLE, the SQL works fine.

Thank you for the bug report.

mysql> create table SOMETAB (COLA char(20));
Query OK, 0 rows affected (0.82 sec)

mysql> create view SOMEVIEW as select * from SOMETAB;
Query OK, 0 rows affected (0.18 sec)

mysql> select * from (select COLA as COLB from SOMEVIEW) as v where v.COLB = 'AR';
ERROR 1054 (42S22): Unknown column 'v.COLB' in 'where clause'
mysql> select * from (select COLA as COLB from SOMETAB) as v where v.COLB = 'AR';
Empty set (0.01 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.03 sec)

It looks very similar to some other bug vith nested (implicit) view, but I can't find it
really.

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/29626

When view column aliased in subselect alias is set on ref which represents
  field. When tmp table is created for subselect, it takes name of original field
  not ref. Because of this alias on view column in subselect is lost. Which
  results in reported error.

Fixed in 5.0.13, cset 1.1935.67.1

Noted in 5.0.13 changelog.