MySQL Bugs: #11399: Use an alias in a select statement on a view

Bug #11399	Use an alias in a select statement on a view
Submitted:	16 Jun 2005 21:40	Modified:	25 Jul 2005 20:48
Reporter:	ApplyCation Software	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	Mysql 5.0.7/5.0.9	OS:	Windows (Windows XP Home (English)/Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Mysql 5.0.7
Windows XP
MySql Administrator 1.0.21

I simply cannot use an alias in a select statement that's involving a view! I've a view called test

On a view the select statement:
select column1 as 'another_name' from test

returns results but the column name doesn't change to 'another_name'

Using a table called test it will not fail. The column alias will be used i.e 'another column' 

I 've tried to query this on Mysql Administrator and Mysql Control Center.

How to repeat:
By mail

Suggested fix:
I don't know

Sorry...I was not able to reproduce the bug. MySQL administrator returns right name of view field. Please look at attached gif file.

screen copy

Attachment: 11399.gif (image/gif, text), 21.42 KiB.

Yes, that's working fine, but what I mean is after creating a view with 2 columns:
column1 and column2 (see your GIF). From that point try to do a select statement (for example in your query browser?) on that view.  So I type

SELECT
  column1 as "Another_name"
 ,column2 as "just_antoher_name"
FROM
  test

Now, I expect two columns as a result of this select statement:

Another_name     just_another_name
-------------      ------------------
bla......              bla......

But this is what I get:

column1            column2
--------------   ------------------
bla......             bla.......

So, the statement seems to ignore the alias.

Greetings
Romke

I have the same problem - also in 5.07 - on Windows XP Home.  As a workaround, if I wrap the Select column in a function (i.e. Trim(column1) AS Another_Name) is seems to give me results.

Could you please try your example query on console client - mysql.exe ? Really I was not reproduce the bug.

I tried on console client with the same result - I logged to an external file and the column name is still the original name not the new name.

To recap the problem:

Create a view called test

Run a select statement against the view using AS on a column

select column1 as another_name from test

The result set is returned but the COLUMN NAME is still the original column name (column1) not what it should be (another_name)

This is with 5.0.7 beta on Windows XP

Hi !

I don't have any idea about the bug. Could you please update your version of mysql and test again ?

I get this problem as well. The results are exactly as described above. "AS" does not work in views as it does in tables.  

I created a view, viewname, with a query as simple as 
"SELECT ID FROM TABLENAME"

And ran a simple query on the view:
"SELECT ID AS NEW_ID_NAME FROM VIEWNAME"

The result is a column of values called "ID", when I expected a column of values called "NEW_ID_NAME"

The problem appears to be that in any SELECT statement from a view that uses a
column name alias, the returned result set ignores the ALIAS.  I am still getting this problem with an Installation from the Development Source Tree downloaded on 
Tuesday 28th June

this can be shown with the following code:

1. Create a new table
        Create Table: CREATE TABLE `t1` (
          `t1_id` int(10) unsigned NOT NULL auto_increment,
          `t1_common` int(10) default NULL,
          PRIMARY KEY  (`t1_id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1

2. Populate it
        INSERT INTO `t1` VALUES (1,20),(2,30),(3,40);

3. Create a second table
        Create Table: CREATE TABLE `t2` (
          `t2_id` int(10) unsigned NOT NULL auto_increment,
          `t2_common` int(10) default NULL,
          `t2_text` varchar(255) default NULL,
          PRIMARY KEY  (`t2_id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1

4. Populate it
        INSERT INTO `t2` VALUES (1,20,'twenty'),(2,30,'thirty'),(3,40,'forty');

5. Create a view on the data joining the 2 tables
        CREATE VIEW `vw_t1_t2` AS 
            select `t1`.`t1_id` ,
        	       `t1`.`t1_common`,
        	       `t2`.`t2_id`,
        	       `t2`.`t2_common`,
        	       `t2`.`t2_text`
        	from `t1` join `t2` 
          where (`t1`.`t1_common` = `t2`.`t2_common`)
        
6. run a SELECT statement using an ALIAS
        select t1_id,t1_common,t2_id,t2_common,t2_text as `changed_name` from vw_t1_t2;
        
		You would expect this to give the result set (note the last field name)
                +-------+-----------+-------+-----------+--------------+
                | t1_id | t1_common | t2_id | t2_common | changed_name |
                +-------+-----------+-------+-----------+--------------+
                |     1 |        20 |     1 |        20 | twenty       |
                |     2 |        30 |     2 |        30 | thirty       |
                |     3 |        40 |     3 |        40 | forty        |
                +-------+-----------+-------+-----------+--------------+

        But instead it returns (note the last field name)
                +-------+-----------+-------+-----------+---------+
                | t1_id | t1_common | t2_id | t2_common | t2_text |
                +-------+-----------+-------+-----------+---------+
                |     1 |        20 |     1 |        20 | twenty  |
                |     2 |        30 |     2 |        30 | thirty  |
                |     3 |        40 |     3 |        40 | forty   |
                +-------+-----------+-------+-----------+---------+

7. If you run the SELECT statement on the two tables seperately:
            select `t1`.`t1_id` ,
        	       `t1`.`t1_common`,
        		   `t2`.`t2_id`,
        		   `t2`.`t2_common`,
        		   `t2`.`t2_text` AS `changed_name`
        	  from `t1` join `t2` 
        	 where (`t1`.`t1_common` = `t2`.`t2_common`)
        it gives the expected result
                +-------+-----------+-------+-----------+--------------+
                | t1_id | t1_common | t2_id | t2_common | changed_name |
                +-------+-----------+-------+-----------+--------------+
                |     1 |        20 |     1 |        20 | twenty       |
                |     2 |        30 |     2 |        30 | thirty       |
                |     3 |        40 |     3 |        40 | forty        |
                +-------+-----------+-------+-----------+--------------+	 
	 

Hope this Helps ;-])

Thank you for the bug report.

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

Fixed in 5.0.10, cset  1.1876.3.1

New item created in find_field_in_table() to fix view's item, was created 
without taking into account original item's alias.

Documented in 5.0.10 changelog:

<listitem><para>Aliasing the column names in a <literal>VIEW</literal> did not work when executing a <literal>SELECT</literal> query on the <literal>VIEW</literal>. (Bug #11399)</para></listitem>