MySQL Bugs: #40002: Wrong result using group by

Bug #40002	Wrong result using group by
Submitted:	13 Oct 2008 14:18	Modified:	22 Oct 2008 9:30
Reporter:	Andre Timmer	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Resulting column in group by is empty but should be filled.

How to repeat:
create table profile2 (
  context varchar(200) NOT NULL
, name    varchar(133) NOT NULL
, value   longtext
, constraint uk UNIQUE KEY (context, name)
) engine myisam;

insert into profile2 values ('cn=jane,ou=org,c=nl' , 'LoginDateTime', '200809011200');
insert into profile2 values ('cn=chris,ou=org,c=nl', 'LoginDateTime', '200809011215');
insert into profile2 values ('cn=blue,ou=org,c=nl' , 'LoginDateTime', '200809011400');
insert into profile2 values ('cn=lisa,ou=org,c=nl' , 'LoginDateTime', '200809011405');
insert into profile2 values ('cn=rene,ou=org,c=nl' , 'LoginDateTime', '200809011600');
insert into profile2 values ('cn=frans,ou=org,c=nl', 'LoginDateTime', '200809011710');
insert into profile2 values ('cn=bob,ou=org,c=nl'  , 'LoginDateTime', '200809011905');
insert into profile2 values ('cn=marie,ou=org,c=nl', 'LoginDateTime', '200809022000');
commit;

select context
	  -- concat space is needed to solve serious performance bug in MySQL 5.0.18
	  -- second concat space is needed else value is not correct
,      substr(value, 1, 6) yyyymm
,      count(*) aantal
from   profile2
where  name = 'LoginDateTime'
group by 1,2;

== Result ==
+----------------------+--------+--------+
| context              | yyyymm | aantal |
+----------------------+--------+--------+
| cn=blue,ou=org,c=nl  |        |      1 |
| cn=bob,ou=org,c=nl   |        |      1 |
| cn=chris,ou=org,c=nl |        |      1 |
| cn=frans,ou=org,c=nl |        |      1 |
| cn=jane,ou=org,c=nl  |        |      1 |
| cn=lisa,ou=org,c=nl  |        |      1 |
| cn=marie,ou=org,c=nl |        |      1 |
| cn=rene,ou=org,c=nl  |        |      1 |
+----------------------+--------+--------+

Column yyymm should be filled!

== Remark ==
Select below produces the correct result:

select context
,      concat(substr(concat(value, ' '), 1, 6),' ') yyyymm
,      count(*) aantal
from   profile2
where  name = 'LoginDateTime'
group by 1,2;

Suggested fix:
.

You can skip the "--" comments.

Thank you for a problem report. Sorry, but I can not repeat the behaviour described with a newer version of MySQL server:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> create table profile2 (
    ->   context varchar(200) NOT NULL
    -> , name    varchar(133) NOT NULL
    -> , value   longtext
    -> , constraint uk UNIQUE KEY (context, name)
    -> ) engine myisam;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> insert into profile2 values ('cn=jane,ou=org,c=nl' , 'LoginDateTime', '20
0809011200');
Query OK, 1 row affected (0.08 sec)

mysql> insert into profile2 values ('cn=chris,ou=org,c=nl', 'LoginDateTime', '20
0809011215');
Query OK, 1 row affected (0.00 sec)

mysql> insert into profile2 values ('cn=blue,ou=org,c=nl' , 'LoginDateTime', '20
0809011400');
Query OK, 1 row affected (0.00 sec)

mysql> insert into profile2 values ('cn=lisa,ou=org,c=nl' , 'LoginDateTime', '20
0809011405');
Query OK, 1 row affected (0.00 sec)

mysql> insert into profile2 values ('cn=rene,ou=org,c=nl' , 'LoginDateTime', '20
0809011600');
Query OK, 1 row affected (0.00 sec)

mysql> insert into profile2 values ('cn=frans,ou=org,c=nl', 'LoginDateTime', '20
0809011710');
Query OK, 1 row affected (0.00 sec)

mysql> insert into profile2 values ('cn=bob,ou=org,c=nl'  , 'LoginDateTime', '20
0809011905');
Query OK, 1 row affected (0.00 sec)

mysql> insert into profile2 values ('cn=marie,ou=org,c=nl', 'LoginDateTime', '20
0809022000');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select context
    ->    -- concat space is needed to solve serious performance bug in MySQL 5.
0.18
    ->    -- second concat space is needed else value is not correct
    -> ,      substr(value, 1, 6) yyyymm
    -> ,      count(*) aantal
    -> from   profile2
    -> where  name = 'LoginDateTime'
    -> group by 1,2;
+----------------------+--------+--------+
| context              | yyyymm | aantal |
+----------------------+--------+--------+
| cn=blue,ou=org,c=nl  | 200809 |      1 |
| cn=bob,ou=org,c=nl   | 200809 |      1 |
| cn=chris,ou=org,c=nl | 200809 |      1 |
| cn=frans,ou=org,c=nl | 200809 |      1 |
| cn=jane,ou=org,c=nl  | 200809 |      1 |
| cn=lisa,ou=org,c=nl  | 200809 |      1 |
| cn=marie,ou=org,c=nl | 200809 |      1 |
| cn=rene,ou=org,c=nl  | 200809 |      1 |
+----------------------+--------+--------+
8 rows in set (0.33 sec)

So this is already fixed then. Excellent.