Bug #101820 UUID() wrong evaluation.
Submitted: 1 Dec 2020 15:23 Modified: 2 Dec 2020 6:18
Reporter: diego dupin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:56, 5.7, 5.6.50, 5.7.32 OS:Any
Assigned to: 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