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 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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.