Bug #121145 COUNT(DISTINCT UUID()) / RAND() returns 1 under implicit grouping

• Submitted: 20 Aug 11:33
• Modified: 21 Aug 6:43
• Reporter: mu mu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[20 Aug 11:33] mu mu

Description:
For an implicitly grouped query (aggregate, no GROUP BY), COUNT(DISTINCT non-deterministic expression) always returns 1, even though the expression yields a different value per row.

The same count is correct when:

1.rewritten as SELECT COUNT(*) FROM (SELECT DISTINCT UUID() …) d, or
2.written as COUNT(DISTINCT UUID(), col), or
3.run with an explicit GROUP BY.
MariaDB does not show this bug.
SUM(DISTINCT RAND()) is also unaffected (different aggregator path).

How to repeat:
CREATE TABLE t(a INT);
INSERT INTO t VALUES (1),(2),(3),(4),(5);

SELECT COUNT(DISTINCT UUID()) FROM t;
-- got: 1
-- expected: 5

SELECT COUNT(DISTINCT RAND()) FROM t;
-- got: 1
-- expected: 5

-- controls (correct)
SELECT COUNT(*) FROM (SELECT DISTINCT UUID() AS u FROM t) d;  -- 5
SELECT COUNT(DISTINCT UUID(), a) FROM t;                     -- 5

[21 Aug 6:43] Roy Lyseng

Thank you for the bug report.
Verified as described.