MySQL Bugs: #25106: A USING clause in combination with a VIEW results in column aliases ignored

Bug #25106	A USING clause in combination with a VIEW results in column aliases ignored
Submitted:	15 Dec 2006 21:31	Modified:	24 Jan 2007 21:25
Reporter:	Chris Dalla Piazza	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 5.0.27	OS:	Linux (Linux, Windows XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	Q1

Description:
I am using views to migrate my clients from one database table and column naming convention to a new one.  The views are created with the old table names and column names and point to the new table and column names so that older client versions can continue to use the database for backwards compatibility.

That is how I came upon this example.

How to repeat:
Execute the following in a test database:

CREATE TABLE tblTest (ID INTEGER, Name VARCHAR(50));
CREATE TABLE tblTest2 (Test_ID INTEGER);
CREATE VIEW test (Test_ID, Description) AS SELECT ID, Name FROM tblTest;

SELECT Description AS Name
FROM test
LEFT JOIN tblTest2
USING (Test_ID);

Suggested fix:
The result of the above query is a column named 'Description' rather than the alias 'Name' as it has been renamed by the query.

*****NOTE*****
This query returns the correct column name:
SELECT Description AS Name
FROM test
LEFT JOIN tblTest2
ON test.Test_ID = tblTest2.Test_ID;

It appears that the bug is specific to the USING clause in combination with a VIEW.

Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE tblTest (ID INTEGER, Name VARCHAR(50));
CRQuery OK, 0 rows affected (0.02 sec)
E
Amysql> CREATE TABLE tblTest2 (Test_ID INTEGER);
CQuery OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW test (Test_ID, Description) AS SELECT ID, Name FROM tblTest;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT Description AS Name
    -> FROM test
    -> LEFT JOIN tblTest2
    -> USING (Test_ID);
Empty set (0.03 sec)

mysql> create table tv1 SELECT Description AS Name FROM test LEFT JOIN tblTest2
 USING (Test_ID);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tv1;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Description | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> create table tv2 SELECT Description AS Name FROM test LEFT JOIN tblTest2
 ON test.Test_ID = tblTest2.Test_ID;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tv2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

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

ChangeSet@1.2367, 2007-01-08 17:20:52+02:00, gkodinov@macbook.local +3 -0
  BUG#25106: A USING clause in combination with a VIEW results in column
             aliases ignored
  When making a new Item on resolving column reference to a JOIN USING
  column the user defined alias was lost.
  Made the code to preserve the alias by setting it into the new Item
  before replacing.

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

ChangeSet@1.2367, 2007-01-11 19:10:01+02:00, gkodinov@macbook.gmz +3 -0
  BUG#25106: A USING clause in combination with a VIEW results in column
             aliases ignored
  When a column reference to a column in JOIN USING is resolved and a new 
  Item is created for this column the user defined name was lost.
  This fix preserves the alias by setting the name of the new Item to the
  original alias.

Fixed in 5.0.34, 5.1.15-beta

Noted in 5.0.34, 5.1.15 changelogs.