Bug #97217 UUID() generates duplicate GUID inside COALESCE
Submitted: 14 Oct 2019 13:43 Modified: 14 Oct 2019 14:55
Reporter: Eugen Vasilyeu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[14 Oct 2019 13:43] Eugen Vasilyeu
Description:
MySQL 5.6; 5.7; 8.0 versions generate the same GUID in SELECT statement wrapped to COALESCE operator.

How to repeat:
1. Run statement:
START TRANSACTION;
CREATE TABLE `GuidDuplicate` (
    `GuidColumn` char(36) CHARACTER SET ascii DEFAULT NULL);

INSERT INTO GuidDuplicate
VALUES(NULL);

INSERT INTO GuidDuplicate
VALUES(NULL);
COMMIT;

SELECT COALESCE(`GuidColumn`, UUID()) AS `GuidColumn`
FROM GuidDuplicate;

2. See that it returns identically records for GUID field, but we expected unic:
+------------------------------------+
|GuidColumn                          |
+------------------------------------+
|bfb50f51-ee87-11e9-ae92-02c518472342|
|bfb50f51-ee87-11e9-ae92-02c518472342|
+------------------------------------+

WORKAROUND
Use a variable:

SELECT COALESCE(`GuidColumn`, @param:= UUID()) AS `GuidColumn`
FROM GuidDuplicate;
[14 Oct 2019 14:55] MySQL Verification Team
Hi Mr. Vasilyev,

Thank you for your bug report.

I repeated your test case.

This is a very small bug, but it is still a bug.

Verified as reported