How to call a function and dupe the return value in a SELECT query?

I’ve defined a function CREATE_UUID() to generate UUID. I want to INSERT INTO…SELECT to copy a table but with two new columns with the same value.

INSERT INTO Table (id, uuid1, uuid2)
SELECT ROWID, CREATE_UUID(), CREATE_UUID()
FROM AnotherTable

But doing so will call the function twice with different values. How should I modify this query so that it won’t have different values for two columns in the same row but does generate a new UUID for each row?

Answer

Variant 1.

INSERT INTO `Table` (id, uuid1, uuid2)
SELECT ROWID, generate_uuid.new_uuid, generate_uuid.new_uuid
FROM AnotherTable
CROSS JOIN ( SELECT CREATE_UUID() new_uuid ) generate_uuid

But if you insert a lot of rows then server may “optimize” the execution, and all rows may obtain the same UUID.

Variant 2.

INSERT INTO `Table` (id, uuid1, uuid2)
SELECT ROWID, new_uuid, new_uuid
FROM ( SELECT ROWID, CREATE_UUID() new_uuid 
       FROM AnotherTable ) source_data;

In this case server may, backward, call new generation for each inserted value.

Variant 3.

INSERT INTO `Table` (id, uuid1, uuid2)
SELECT ROWID, @new_uuid := CREATE_UUID(), @new_uuid
FROM AnotherTable;

Seems to be the most safe. But used user-defined variable assignment is deprecated.

This everything in one fiddle.