MySQL Bugs: #29392: MySQL 5.0.41 returning incorrect columns in query

Bug #29392	MySQL 5.0.41 returning incorrect columns in query
Submitted:	27 Jun 2007 16:01	Modified:	9 Jul 2007 0:54
Reporter:	Joe Lobraco	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.41, 5.1	OS:	Any
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	duplicate column on select from view

Description:
I am noticing a strange problem between versions of MySQL. My application was running fine on version 5.0.27 but when my hosting provider upgraded the MySql database to version 5.0.41 my app stopped working.

When running a select query that aliases the column names against a view, version 5.0.41 of MySQL duplicates one column while omitting another.

How to repeat:
The following select query works fine on 5.0.27 but produces 2 columns with the same name in version 5.0.41

select userrolepe0_.person_id as person4_1_, userrolepe0_.id as id1_, userrolepe0_.id as id48_0_, userrolepe0_.permission as permission48_0_, userrolepe0_.role as role48_0_, userrolepe0_.person_id as person4_48_0_ from user_role_permission_view userrolepe0_ where userrolepe0_.person_id=10;

When I run this query on version 5.0.27 using the mysql command line client I get the following (correct) result: 

+------------+------+---------+-----------------+-----------------+---------------+
| person4_1_ | id1_ | id48_0_ | permission48_0_ | role48_0_       | person4_48_0_ |
+------------+------+---------+-----------------+-----------------+---------------+
| 10         | 10_1 | 10_1    | NULL            | NUCANS_APP_USER |            10 |
+------------+------+---------+-----------------+-----------------+---------------+
1 row in set (0.01 sec) 

+------+---------------+---------+-----------------+-----------------+---------------+
| id1_ | person4_48_0_ | id48_0_ | permission48_0_ | role48_0_       | person4_48_0_ |
+------+---------------+---------+-----------------+-----------------+---------------+
| 10_1 |            10 |    10_1 |            NULL | NUCANS_APP_USER |            10 |
+------+---------------+---------+-----------------+-----------------+---------------+
1 row in set (0.01 sec)

If you notice in this result set instead of column person4_1_ I get a duplicate of column person4_48_0_.

The structure for the view that is being queried here is:

mysql> desc user_role_permission_view;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| id            | varbinary(39) | YES  |     | NULL    |       |
| person_id     | int(11)       | NO   |     | 0       |       |
| username      | varchar(40)   | YES  |     | NULL    |       |
| role_id       | int(11)       | NO   |     | 0       |       |
| role          | varchar(100)  | YES  |     | NULL    |       |
| permission_id | int(11)       | YES  |     | NULL    |       |
| permission    | varchar(100)  | YES  |     | NULL    |       |
| app           | varchar(40)   | NO   |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec) 

Suggested fix:
I have not been able to figure out a way to get this to work other than to downgrade the version of MySQL to version 5.0.27.

Thank you for the bug report. If possible for you to provide use a dump file
to populate the table so we can reproduce the behavior reported?. Thanks in
advance.

This is a dump from the user_role_permission_view on version 5.0.27

Attachment: user_role_permission_view_dump.sql (text/x-sql), 2.93 KiB.

Miguel,

I have uploaded a mysqldump of the table in question. 

One thing though, the table is actually a view. I'm not sure if that will matter. Please let me know if you need anything else.

Thanks,
Joe

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE for each table in the VIEW: cans_agency_personnel_profile, cans_app_user_role, cans_app_role, cans_app_role_permission, cans_app_permission, perm

Here is the results fo the SHOW CREATE TABLE

Attachment: create_tables.sql (text/x-sql), 4.23 KiB.

Sveta,

I have uploaded the requested information as a file called create_tables.sql.

I have also uploaded a file containing a mysqldump of the tables that make up the view. This file is called other_tables_dump.sql

Thanks,
Joe

Thank you for the feedback.

Verified as described. All versions are affected.

test case

Attachment: bug29392.test (application/octet-stream, text), 12.35 KiB.

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

ChangeSet@1.2519, 2007-07-04 21:12:07-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #29392.
  This bug may manifest itself for select queries over a multi-table view
  that includes an ORDER BY clause in its definition. If the select list of 
  the query contains references to the same view column with different
  aliases the names of the columns in the result output will be nevertheless
  the same, coinciding with one of the alias.
  
  The bug happened because the method Item_ref::get_tmp_table_item that
  was inherited by the class Item_direct_view_ref ignored the fact that
  the name of the view column reference must be inherited by the fields
  of the temporary table that was created in order to get the result rows
  sorted.

Pushed into 5.1.21-beta

Pushed into 5.0.46

Noted in 5.0.46, 5.1.21 changelogs.

Results for a select query that aliases the column names against a
view could duplicate one column while omitting another.  This bug
could occur for a query over a multiple-table view that includes
an ORDER BY clause in its definition.