Bug #12891 UNION doesn't return DISTINCT result for multi-byte characters

• Submitted: 30 Aug 2005 20:51
• Modified: 14 Sep 2005 0:33
• Reporter: Shuichi Tamagawa
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.12-bk Aug 29.
• OS: Linux (SuSE Linux 9.2)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[30 Aug 2005 20:51] Shuichi Tamagawa

Description:
UNION doesn't return DISTINCT result for multi-byte characters.

How to repeat:
mysql> create table t1(a char(1)) default character set = utf8;
Query OK, 0 rows affected (0.13 sec)

mysql> create table t2(a char(1)) default character set = utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('a'),('a'),(0xE38182),(0xE38182);
/* 0xE38182 is HIRAGANA LETTER A */
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1 values('i'),('i'),(0xE38184),(0xE38184);
/* 0xE38184 is HIRAGANA LETTER I */
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1 union distinct select * from t2;
+------+
| a    |
+------+
| a    |
| あ  |
| あ  |
| i    |
| い  |
| い  |
+------+
6 rows in set (0.01 sec)

mysql> select * from t1 union select * from t2;
+------+
| a    |
+------+
| a    |
| あ  |
| あ  |
| i    |
| い  |
| い  |
+------+
6 rows in set (0.00 sec)

mysql> select * from t1 union all select * from t2;
+------+
| a    |
+------+
| a    |
| a    |
| あ  |
| あ  |
| i    |
| i    |
| い  |
| い  |
+------+
8 rows in set (0.01 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.12-beta-debug |
+-------------------+
1 row in set (0.00 sec)

Suggested fix:
Should be as follows

mysql> select * from t1 union select * from t2;
+------+
| a    |
+------+
| a    |
| あ  |
| i    |
| い  |
+------+

[30 Aug 2005 21:17] Shuichi Tamagawa

This is fine with 4.1.13

[1 Sep 2005 14:35] 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/internals/29175

[12 Sep 2005 11:06] Alexander Barkov

Fixed in 5.0.13

[14 Sep 2005 0:33] Paul DuBois

Noted in 5.0.13 changelog.