Bug #101820 UUID() wrong evaluation.

• Submitted: 1 Dec 2020 15:23
• Modified: 2 Dec 2020 6:18
• Reporter: diego dupin (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 56, 5.7, 5.6.50, 5.7.32
• OS: Any
• CPU Architecture: Any

[1 Dec 2020 15:23] diego dupin

Description:
UUID() is wrongly evaluate depending on collation. 

How to repeat:
DROP TABLE  if EXISTS testUUID;
create table testUUID (ID int, new_ID varchar(100), new_ID2 varchar(100), new_ID3 BINARY(16)) CHARACTER SET UTF8MB4 COLLATE utf8mb4_general_ci;
insert into testUUID (id) values (1),(2),(3);

SET NAMES 'utf8' COLLATE 'utf8_general_ci';

UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(UUID(), '-', ''));

SELECT new_ID, new_ID2 FROM testUUID; 
/* good !
new_ID;new_ID2
ac65af1e-33e7-11eb-9b97-c8348e0fed44;ac65af3133e711eb9b97c8348e0fed44
ac65b27d-33e7-11eb-9b97-c8348e0fed44;ac65b28733e711eb9b97c8348e0fed44
ac65b300-33e7-11eb-9b97-c8348e0fed44;ac65b30433e711eb9b97c8348e0fed44

*/
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';
UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(UUID(), '-', ''));
SELECT new_ID, new_ID2 FROM testUUID; 
/* BAD !!!! new_ID2 have all the same UUID value !
new_ID;new_ID2
ac6aa6ae-33e7-11eb-9b97-c8348e0fed44;ac6aa54a33e711eb9b97c8348e0fed44
ac6aa7be-33e7-11eb-9b97-c8348e0fed44;ac6aa54a33e711eb9b97c8348e0fed44
ac6aa7e9-33e7-11eb-9b97-c8348e0fed44;ac6aa54a33e711eb9b97c8348e0fed44
*/

[1 Dec 2020 16:41] Rick James

Seems to be fixed in 8.0 (and MariaDB 10.1).

[2 Dec 2020 6:18] MySQL Verification Team

Hello diego dupin,

Thank you for the report and test case.
Observed that 5.6.50 and 5.7.32 are affected.

regards,
Umesh