Bug #22534 multiple aliases in a SELECT from a view with a GROUP BY clause has issues
Submitted: 21 Sep 2006 0:18 Modified: 21 Sep 2006 17:41
Reporter: NOT_FOUND NOT_FOUND Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:any > 5 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[21 Sep 2006 0:18] NOT_FOUND NOT_FOUND
Description:
If you use multiple aliases for the same field in a SELECT from view and have a group by clause the results are wrong.  The returned results always use the last alias designated for a specified variable to define the headers of the returned results. 

Specifiaclly I am having issues with the results from a SELECT query that is used in the Special:Listusers function of the popular Mediawiki php product.  I have multiple mediawiki installations using the same database.  To simplify user management I have created views that point to a central user table.

I have included a quick test on how to repeat this error without the need for you to know my exact setup.  I have searched through the manual and I am having trouble deciding if it truly a bug or not.

How to repeat:
plynch@somedesk:~> mysql 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 247 to server version: 5.0.24a-standard

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

mysql> drop view user_view;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
plynch@somedesk:~> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 248 to server version: 5.0.24a-standard

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

mysql> CREATE TABLE `user` (
    ->   `user_id` int(5) unsigned NOT NULL auto_increment,
    ->   `user_name` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
    ->   `user_real_name` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
    ->    PRIMARY KEY  (`user_id`),
    ->    UNIQUE KEY `user_name` (`user_name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql>
mysql> INSERT INTO `user` (`user_id`, `user_name`, `user_real_name`) VALUES
    -> (1, 'user1', 'lary'),
    -> (2, 'user2', 'moe'),
    -> (3, 'user3', 'curly');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE ALGORITHM=undefined VIEW `user_view` AS
    -> select `user`.`user_id` AS `user_id`,
    -> `user`.`user_name` AS `user_name`,
    -> `user`.`user_real_name` AS `user_real_name` from `user`;
Query OK, 0 rows affected (0.00 sec)

mysql> select user_id, user_name AS title, user_name AS value, user_real_name from user_view;
+---------+-------+-------+----------------+
| user_id | title | value | user_real_name |
+---------+-------+-------+----------------+
|       1 | user1 | user1 | lary           |
|       2 | user2 | user2 | moe            |
|       3 | user3 | user3 | curly          |
+---------+-------+-------+----------------+
3 rows in set (0.00 sec)

mysql> select user_id, user_name AS title, user_name AS value, user_real_name from user_view group by user_name;
+---------+-------+-------+----------------+
| user_id | value | value | user_real_name |
+---------+-------+-------+----------------+
|       1 | user1 | user1 | lary           |
|       2 | user2 | user2 | moe            |
|       3 | user3 | user3 | curly          |
+---------+-------+-------+----------------+
3 rows in set (0.00 sec)

Suggested fix:
I would suggest that the results use the same alias monikers that I specify on the SELECT statement.
[21 Sep 2006 2:48] MySQL Verification Team
Thank you for the bug report. I was unable to repeat with server built from
the current source tree:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.26-debug

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

mysql> CREATE TABLE `user` (
    -> `user_id` int(5) unsigned NOT NULL auto_increment,
    -> `user_name` varchar(255) character set latin1 collate latin1_bin NOT
    -> NULL default '',
    -> `user_real_name` varchar(255) character set latin1 collate latin1_bin
    -> NOT NULL default '',
    -> PRIMARY KEY  (`user_id`),
    ->   UNIQUE KEY `user_name` (`user_name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO `user` (`user_id`, `user_name`, `user_real_name`) VALUES
    -> (1, 'user1', 'lary'),
    -> (2, 'user2', 'moe'),
    -> (3, 'user3', 'curly');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE ALGORITHM=undefined VIEW `user_view` AS
    -> select `user`.`user_id` AS `user_id`,
    -> `user`.`user_name` AS `user_name`,
    -> `user`.`user_real_name` AS `user_real_name` from `user`;
Query OK, 0 rows affected (0.00 sec)

mysql> select user_id, user_name AS title, user_name AS value, user_real_name
    -> from user_view;
+---------+-------+-------+----------------+
| user_id | title | value | user_real_name |
+---------+-------+-------+----------------+
|       1 | user1 | user1 | lary           | 
|       2 | user2 | user2 | moe            | 
|       3 | user3 | user3 | curly          | 
+---------+-------+-------+----------------+
3 rows in set (0.01 sec)

mysql> select user_id, user_name AS title, user_name AS value, user_real_name
    -> from user_view group by user_name;
+---------+-------+-------+----------------+
| user_id | title | value | user_real_name |
+---------+-------+-------+----------------+
|       1 | user1 | user1 | lary           | 
|       2 | user2 | user2 | moe            | 
|       3 | user3 | user3 | curly          | 
+---------+-------+-------+----------------+
3 rows in set (0.00 sec)

mysql>
[21 Sep 2006 3:46] MySQL Verification Team
I was able to repeat with version 5.0.24 Windows server, then I will test
with version 5.0.25a to see if this bug report can be closed as fixed on
release or source.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24-community-nt

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

mysql> CREATE TABLE `user` (
    -> `user_id` int(5) unsigned NOT NULL auto_increment,
    -> `user_name` varchar(255) character set latin1 collate latin1_bin NOT
    -> NULL default '',
    -> `user_real_name` varchar(255) character set latin1 collate latin1_bin
    -> NOT NULL default '',
    -> PRIMARY KEY  (`user_id`),
    ->   UNIQUE KEY `user_name` (`user_name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO `user` (`user_id`, `user_name`, `user_real_name`) VALUES
    -> (1, 'user1', 'lary'),
    -> (2, 'user2', 'moe'),
    -> (3, 'user3', 'curly');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE ALGORITHM=undefined VIEW `user_view` AS
    -> select `user`.`user_id` AS `user_id`,
    -> `user`.`user_name` AS `user_name`,
    -> `user`.`user_real_name` AS `user_real_name` from `user`;
Query OK, 0 rows affected (0.05 sec)

mysql> select user_id, user_name AS title, user_name AS value, user_real_name
    -> from user_view;
+---------+-------+-------+----------------+
| user_id | title | value | user_real_name |
+---------+-------+-------+----------------+
|       1 | user1 | user1 | lary           |
|       2 | user2 | user2 | moe            |
|       3 | user3 | user3 | curly          |
+---------+-------+-------+----------------+
3 rows in set (0.03 sec)

mysql> select user_id, user_name AS title, user_name AS value, user_real_name
    -> from user_view group by user_name;
+---------+-------+-------+----------------+
| user_id | value | value | user_real_name |
+---------+-------+-------+----------------+
|       1 | user1 | user1 | lary           |
|       2 | user2 | user2 | moe            |
|       3 | user3 | user3 | curly          |
+---------+-------+-------+----------------+
3 rows in set (0.00 sec)

mysql>
[21 Sep 2006 17:41] NOT_FOUND NOT_FOUND
I can repeat this bug in 5.0.22, 5.0.24a and the 5.1.11beta.  I can still repeat this error in the 5.1.11-0 version as well.  Why is this bug switched to a "can't repeat" status?  Is there a version that I can download that has this problem corrected?  Thanks for your efforts.  I appreciate the help.
[6 Oct 2006 12:53] Sergei Golubchik
according to Miguel's comments, he was able to repeat the bug in 5.0.24, but not in 5.0.26 - the next version that will be released. So the bug is closed, it's not repeatable in the latest source tree, the fix will be present in the next release.