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

Bug #12993	View column raname broken in subselect
Submitted:	5 Sep 2005 13:56	Modified:	14 Sep 2005 0:51
Reporter:	Mika Vesterholm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.12	OS:	Windows (WinXP)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

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.