Bug #115842 The count distinct aggregation funciton of UUID returned an error value

• Submitted: 15 Aug 8:21
• Modified: 15 Aug 11:05
• Reporter: GONGTUI FU
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.39, 8.4.2, 9.0.1
• OS: Any
• CPU Architecture: Any
• Tags: regression, UUID COUNT

[15 Aug 8:21] GONGTUI FU

Description:
Hi, guys:
  The count distinct aggregation of the return value of the uuid function returns an error value.

How to repeat:

mysql> select uuid() guid from (select 1 union select 2) s;
+--------------------------------------+
| guid                                 |
+--------------------------------------+
| 4901eafc-5adb-11ef-b3ee-3cecef590fba |
| 4901eb4d-5adb-11ef-b3ee-3cecef590fba |
+--------------------------------------+
2 rows in set (0.00 sec)

The above two results are different.

mysql> select count( guid) from( select uuid() guid from (select 1 union select 2) s) ss;
+--------------+
| count( guid) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(distinct guid) from( select uuid() guid from (select 1 union select 2) s) ss;
+----------------------+
| count(distinct guid) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

The output should be 2.

[15 Aug 11:05] MySQL Verification Team

Hello GONGTUI FU,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[20 Aug 7:37] huahua xu

Add a helpful test case:

mysql> set optimizer_switch  = 'derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

mysql>  select count(distinct guid) from( select uuid() guid from (select 1 union select 2) s) ss;
+----------------------+
| count(distinct guid) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> set optimizer_switch  = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(distinct guid) from( select uuid() guid from (select 1 union select 2) s) ss;
+----------------------+
| count(distinct guid) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.00 sec)