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 Email Updates:
Status: Closed Impact on me:
None 
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.