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

[27 Jun 2007 16:01] Joe Lobraco
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.
[27 Jun 2007 16:45] Miguel Solorzano
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.
[27 Jun 2007 16:50] Joe Lobraco
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.

[27 Jun 2007 18:11] Joe Lobraco
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
[27 Jun 2007 19:01] Sveta Smirnova
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
[27 Jun 2007 19:24] Joe Lobraco
Here is the results fo the SHOW CREATE TABLE

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

[27 Jun 2007 19:29] Joe Lobraco
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
[28 Jun 2007 6:27] Sveta Smirnova
Thank you for the feedback.

Verified as described. All versions are affected.
[28 Jun 2007 6:28] Sveta Smirnova
test case

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

[5 Jul 2007 4:08] Bugs System
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.
[8 Jul 2007 17:28] Bugs System
Pushed into 5.1.21-beta
[8 Jul 2007 17:30] Bugs System
Pushed into 5.0.46
[9 Jul 2007 0:54] Paul Dubois
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.