Bug #97217 UUID() generates duplicate GUID inside COALESCE

• Submitted: 14 Oct 2019 13:43
• Modified: 14 Oct 2019 14:55
• Reporter: Eugen Vasilyeu
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• OS: Any
• 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