Bug #89474 UUID() used in string functions not unique if charset is 'utf8mb4'

• Submitted: 30 Jan 2018 16:59
• Modified: 6 Oct 2020 12:54
• Reporter: EGOR Upatov
• Status: Not a Bug
• Category: MySQL Server: Charsets
• Severity: S4 (Feature request)
• Version: 5.6+
• OS: Any
• CPU Architecture: Any
• Tags: utf8mb4, UUID

[30 Jan 2018 16:59] EGOR Upatov

Description:
if use call to UUID() function inside any string function (like CONCAT or REPLACE) with connection charset = 'utf8mb4' then value of uuid is not unique for each row.

How to repeat:
-- 1) Create table and fill it with more then one row

mysql> CREATE TABLE a ( `value` INT NOT NULL );
Query OK, 0 rows affected (0,15 sec)

mysql> INSERT INTO a VALUES (1), (2);
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 2) Reproduce

mysql> SET NAMES 'utf8mb4';
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT UUID() AS unique_column, REPLACE(UUID(), '-', '') AS non_unique_column FROM a;
+--------------------------------------+----------------------------------+
| unique_column                        | non_unique_column                |
+--------------------------------------+----------------------------------+
| def08589-05dd-11e8-84cd-005056aa737d | def0824905dd11e884cd005056aa737d |
| def085dd-05dd-11e8-84cd-005056aa737d | def0824905dd11e884cd005056aa737d |
+--------------------------------------+----------------------------------+
2 rows in set (0,00 sec)

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

mysql> SELECT UUID() AS unique_column, REPLACE(UUID(), '-', '') AS non_unique_column FROM a;
+--------------------------------------+----------------------------------+
| unique_column                        | non_unique_column                |
+--------------------------------------+----------------------------------+
| ec088869-05dd-11e8-84cd-005056aa737d | ec08887e05dd11e884cd005056aa737d |
| ec0888ca-05dd-11e8-84cd-005056aa737d | ec0888cc05dd11e884cd005056aa737d |
+--------------------------------------+----------------------------------+
 
-- 3) Summary
As you can see using REPLACE make UUID return duplicate, but without REPLACE UUID still returns unique values

Suggested fix:
Make UUID() call inside string functions not charset sensetive

[6 Oct 2020 12:54] MySQL Verification Team

Hi Mr. Upatov,

Thank you for your feature request.

However, I have tried latest releases of both 5.7 and 8.0 and all is just fine:

SELECT UUID() AS unique_column, REPLACE(UUID(), '-', '') AS non_unique_column FROM a;
+--------------------------------------+----------------------------------+
| unique_column                        | non_unique_column                |
+--------------------------------------+----------------------------------+
| c23dda18-07d2-11eb-bafd-0226b725957a | c23ddaa407d211ebbafd0226b725957a |
| c23ddc02-07d2-11eb-bafd-0226b725957a | c23ddc1607d211ebbafd0226b725957a |
+--------------------------------------+----------------------------------+
2 rows in set (0.00 sec)

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

mysql> SELECT UUID() AS unique_column, REPLACE(UUID(), '-', '') AS non_unique_column FROM a;
+--------------------------------------+----------------------------------+
| unique_column                        | non_unique_column                |
+--------------------------------------+----------------------------------+
| ce6ce41e-07d2-11eb-bafd-0226b725957a | ce6ce43207d211ebbafd0226b725957a |
| ce6ce5d6-07d2-11eb-bafd-0226b725957a | ce6ce5e007d211ebbafd0226b725957a |
+--------------------------------------+----------------------------------+

Not a bug !!!!