Bug #9430 can't group by view fields
Submitted: 28 Mar 2005 7:39 Modified: 28 Apr 2005 13:51
Reporter: yan chang Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-beta OS:Microsoft Windows (windows 2000 server)
Assigned to: CPU Architecture:Any

[28 Mar 2005 7:39] yan chang
Description:
can't group by view fields

I have a view,like this:

mysql> desc vd_username;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| user_id          | decimal(6,0) | NO   |     | 0       |       |
| user_id2         | char(5)      | NO   |     |         |       |
| category_id      | decimal(6,0) | NO   |     | 0       |       |
| user_name        | varchar(20)  | NO   |     |         |       |
| computation_id   | decimal(6,0) | YES  |     | NULL    |       |
| computation_name | varchar(10)  | NO   |     |         |       |
| category_name    | varchar(20)  | NO   |     |         |       |
+------------------+--------------+------+-----+---------+-------+
7 rows in set (0.15 sec)

when do it:

mysql> select count(*) from  vd_username group by user_name;
ERROR 1062 (23000): Duplicate entry 'UserName' for key 1
mysql>

if count is 1 or 0,no such error.

How to repeat:
1 create a view like this
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| user_id          | decimal(6,0) | NO   |     | 0       |       |
| user_id2         | char(5)      | NO   |     |         |       |
| category_id      | decimal(6,0) | NO   |     | 0       |       |
| user_name        | varchar(20)  | NO   |     |         |       |
| computation_id   | decimal(6,0) | YES  |     | NULL    |       |
| computation_name | varchar(10)  | NO   |     |         |       |
| category_name    | varchar(20)  | NO   |     |         |       |
+------------------+--------------+------+-----+---------+-------+

2 insert 10 data.
3 select count(*) from  vd_username group by user_name;
[28 Mar 2005 13:51] MySQL Verification Team
I wans't able to repeat:

mysql> select count(*) from tbl_v group by user_name;
+----------+
| count(*) |
+----------+
|        4 |
|        1 |
|        8 |
+----------+
3 rows in set (0.01 sec)

could you please provide the complete test case not just describing
the view.
[28 Apr 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".