Bug #84603 Multiple reselects of the same UUID() subquery column produces different values

• Submitted: 23 Jan 2017 1:18
• Modified: 1 Feb 2017 16:51
• Reporter: David Bruck
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 14.14
• OS: Windows (10)
• CPU Architecture: Any
• Tags: MySQL, subquery, UUID

[23 Jan 2017 1:18] David Bruck

Description:
If a subquery selects a column using UUID() as either the entire column or as an expression to another function and an outer query attempts to reference the value of the inner column more than once, the value of the inner column changes even for the same row. As expected, different rows have different UUID() values.

How to repeat:
SELECT CONCAT('A: ', A, ', ', B, '-----', B) C
FROM
(
  SELECT UUID() B
    ,A
  FROM
  (
    SELECT 1 A
    UNION ALL
    SELECT 2
  ) IQ
) OQ;
#Expected, there should be a unique value of B for each row, but not two unique values.

Suggested fix:
There should be a single unique value of each single execution of UUID().

[23 Jan 2017 1:41] David Bruck

Workaround by adding an otherwise unused variable:

SELECT CONCAT('A: ', A, ', ', B, '-----', B) C
FROM
(
    SELECT
        @unused := #otherwise useless variable
            UUID() B
        ,A
        FROM
        (
            SELECT 1 A
            UNION ALL
            SELECT 2
        ) IQ
    ) OQ;

[1 Feb 2017 16:51] MySQL Verification Team

Hi!

I fail to see where is a bug here !!!!

As our manual clearly explains:

"
A UUID is designed as a number that is globally unique in space and time. Two calls to
UUID()  are expected to generate two different values
"
That means that if you have a table t1 with 1000 rows, then the query:

SELEECT UUID() from t1;

will produce 1000 different values.

That  is exactly what happens in your nested query.