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: | |
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
[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.