Bug #36772 When using UTF8, CONVERT with GROUP BY returns truncated results
Submitted: 16 May 2008 23:04 Modified: 8 Dec 2008 16:50
Reporter: Gregory Golberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.0BK/5.1BK/6.0BK OS:Any (Ubuntu (5.0.38-Ubuntu_0ubuntu1.4-log) and WinXP (5.0.45-community-nt))
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: charset, convert, GROUP BY, utf8

[16 May 2008 23:04] Gregory Golberg 
Description:
When using UTF8, CONVERT with GROUP BY returns truncated results (except when doing a GROUP BY on a UNIQUE index). See below for the exhaustive test case.

This may be related to bug #9213 (

How to repeat:
mysql> DROP TABLE IF EXISTS `bug`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  
mysql> CREATE TABLE `bug` (
    -> `order_id` INT(11) NOT NULL,
    -> `order_placed` DATETIME NOT NULL,
    -> `order_filled` DATETIME default NULL,
    -> `part_number` INT(11) NOT NULL,
    -> `customer_number` INT(11) NOT NULL,
    -> `quantity` INT(11) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `bug` VALUES
    -> (70000,'2008-04-21 17:56:52',NULL,1092,43250,1),
    -> (70001,'2008-04-21 17:56:52',NULL,1085,41974,1),
    -> (70002,'2008-04-21 17:56:52',NULL,1065,48790,7);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY part_number;
+------------------------+---------------------------+
| CONVERT(order_id,CHAR) | CONVERT(part_number,CHAR) |
+------------------------+---------------------------+
| 70002                  | 1065                      | 
| 70001                  | 1085                      | 
| 70000                  | 1092                      | 
+------------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY part_number;
+------------------------+---------------------------+
| CONVERT(order_id,CHAR) | CONVERT(part_number,CHAR) |
+------------------------+---------------------------+
| 700                    | 106                       | 
| 700                    | 108                       | 
| 700                    | 109                       | 
+------------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug ;
+------------------------+---------------------------+
| CONVERT(order_id,CHAR) | CONVERT(part_number,CHAR) |
+------------------------+---------------------------+
| 70000                  | 1092                      |
| 70001                  | 1085                      |
| 70002                  | 1065                      |
+------------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE bug ADD UNIQUE (part_number);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY part_number;
+------------------------+---------------------------+
| CONVERT(order_id,CHAR) | CONVERT(part_number,CHAR) |
+------------------------+---------------------------+
| 70000                  | 1092                      | 
| 70001                  | 1085                      | 
| 70002                  | 1065                      | 
+------------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> DROP INDEX part_number ON bug;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY part_number;
+------------------------+---------------------------+
| CONVERT(order_id,CHAR) | CONVERT(part_number,CHAR) |
+------------------------+---------------------------+
| 700                    | 106                       | 
| 700                    | 108                       | 
| 700                    | 109                       | 
+------------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE bug ADD INDEX (part_number);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) from bug GROUP BY part_number;
+------------------------+---------------------------+
| CONVERT(order_id,CHAR) | CONVERT(part_number,CHAR) |
+------------------------+---------------------------+
| 700                    | 106                       | 
| 700                    | 108                       | 
| 700                    | 109                       | 
+------------------------+---------------------------+
3 rows in set (0.00 sec)
[16 May 2008 23:05] Gregory Golberg 
Output of SHOW VARIABLES

Attachment: show-variables.txt (text/plain), 15.97 KiB.
[17 May 2008 0:06] Gregory Golberg 
Also occurs with Ubuntu ( 5.0.38-Ubuntu_0ubuntu1.4-log).
[18 May 2008 0:40] MySQL Verification Team 
Thank you for the bug report. Verified as described.

DROP TABLE IF EXISTS `bug`;
CREATE TABLE `bug` (
`order_id` INT(11) NOT NULL,
`order_placed` DATETIME NOT NULL,
`order_filled` DATETIME default NULL,
`part_number` INT(11) NOT NULL,
`customer_number` INT(11) NOT NULL,
`quantity` INT(11) NOT NULL
);
INSERT INTO `bug` VALUES
(70000,'2008-04-21 17:56:52',NULL,1092,43250,1),
(70001,'2008-04-21 17:56:52',NULL,1085,41974,1),
(70002,'2008-04-21 17:56:52',NULL,1065,48790,7);
SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY
part_number;
SET NAMES utf8;
SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY
part_number;
SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug ;
ALTER TABLE bug ADD UNIQUE (part_number);
SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY
part_number;
DROP INDEX part_number ON bug;
SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) FROM bug GROUP BY
part_number;
ALTER TABLE bug ADD INDEX (part_number);
SELECT CONVERT(order_id,CHAR), CONVERT(part_number,CHAR) from bug GROUP BY
part_number;
[13 Nov 2008 9:00] Bugs System 
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/commits/58607

2712 Ramil Kalimullin	2008-11-13
      Fix for bug#36772: When using UTF8, CONVERT with GROUP BY
      returns truncated results
      
      Problem: performig conversion from {INT, DECIMAL, REAL} to CHAR
      we incorrectly set its max length in some cases that may lead
      to truncated results returned.
      
      Fix: properly set CONVERT({INT, DECIMAL, REAL}, CHAR) result's
      max length.
[21 Nov 2008 6:46] Alexander Barkov 
The patch looks fine. Ok to push.
[21 Nov 2008 9:49] Bugs System 
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/commits/59486

2720 Ramil Kalimullin	2008-11-21
      Fix for bug#36772: When using UTF8, CONVERT with GROUP BY
      returns truncated results
      
      Problem: performig conversion from {INT, DECIMAL, REAL} to CHAR
      we incorrectly set its max length in some cases that may lead
      to truncated results returned.
      
      Fix: properly set CONVERT({INT, DECIMAL, REAL}, CHAR) result's
      max length.
[2 Dec 2008 13:01] Bugs System 
Pushed into 5.0.74  (revid:ramil@mysql.com-20081121094822-9i4psfu273nbv59k) (version source revid:ramil@mysql.com-20081121094822-9i4psfu273nbv59k) (pib:5)
[3 Dec 2008 20:38] Paul DuBois 
Noted in 5.0.74 changelog.

Use of CONVERT() with GROUP BY to convert numeric values to CHAR
could return truncated results.

Resetting report to NDI pending push into 5.1.x, 6.0.x.
[8 Dec 2008 10:21] Bugs System 
Pushed into 5.1.31  (revid:ramil@mysql.com-20081121094822-9i4psfu273nbv59k) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)
[8 Dec 2008 11:32] Bugs System 
Pushed into 6.0.9-alpha  (revid:ramil@mysql.com-20081121094822-9i4psfu273nbv59k) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)
[8 Dec 2008 16:50] Paul DuBois 
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:22] Bugs System 
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:00] Bugs System 
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:06] Bugs System 
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)