MySQL Bugs: #9213: GROUP BY query on utf-8 key returns wrong results (empty set)

Bug #9213	GROUP BY query on utf-8 key returns wrong results (empty set)
Submitted:	16 Mar 2005 2:34	Modified:	11 Apr 2005 2:21
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Any (any)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
Given a certain table, I have:

mysql> select c from t where a = 4 and b = 'L:\\' group by c;
Empty set (0.00 sec)

mysql> select c from t where a = 4 and b = 'L:\\';
+-------+
| c     |
+-------+
| log   |
| other |
| other |
| other |
| other |
+-------+
5 rows in set (0.01 sec)

How to repeat:
Untar the attached file '4830.tar.gz' on a recent 4.1 server.  I used 4.1.11-pre (recent BK sources) on FreeBSD; user is on Windows.

Then run:

-- This returns Empty set
select c from t where a = 4 and b = 'L:\\' group by c;

-- But this returns 5 rows (two distinct values)
select c from t where a = 4 and b = 'L:\\';

Other things to try:

-- These both return the correct results
select c from t use index (primary) where a = 4 and b = 'L:\\' group by c;
select c from t use index (a) where a = 4 and b = 'L:\\' group by c;

-- Try the following statements in the order shown
delete from t where c > 'other';
-- Empty set
select c from t where a = 4 and b = 'L:\\' group by c;

analyze table t;
-- Empty set
select c from t where a = 4 and b = 'L:\\' group by c;
-- key: PRIMARY;  key_len: 604
explain extended select c from t where a = 4 and b = 'L:\\' group by c\G

optimize table c;
-- Returns correct results
select c from t where a = 4 and b = 'L:\\' group by c;
-- key: PRIMARY;  key_len: 400
explain extended select c from t where a = 4 and b = 'L:\\' group by c\G

tar zxf 4830.tar.gz; mv t.{MYI,MYD,frm} data/test; mysql test; ...

Attachment: 4830.tar.gz (application/gzip, text), 8.55 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23404

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23469

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23470

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23473

Fixed in 4.1.11.

Note for the docs team:
GROUP BY field1, field2 ... query may* return return wrong results if the first** table in the join has several indexes that cover field1, field2... 
(and the covered fields do not need to be in utf8 for this to happen)

* depending on index statistics 
** first wrt. explain output. 

The fix was pushed into 4.1.11 tree

Noted in 4.1.11 changelog.