Description:
NOT DETERMINISTIC MODIFIES SQL DATA stored functions are evaluated more than once per row
when they appear in a DISTINCT or GROUP BY query:
SELECT DISTINCT f() -> N+1 evaluations
SELECT ... GROUP BY f() -> 2N+1 evaluations
SELECT DISTINCT b, f() -> 2N evaluations
MySQL 9.7.2 evaluates the function exactly once per row (N) in all three forms, so this is a
trunk-only regression.
The ORDER BY path is already correct on trunk (5 evaluations for 5 rows): it was fixed by
46096fc3590 (Bug#36578540, "Hypergraph sorts on side-effecting functions without materializing
first"). The DISTINCT / GROUP BY de-duplication path was not covered by that fix - this is the
sibling gap.
Both the old (non-hypergraph) optimizer and the hypergraph optimizer are affected on trunk, so the
regression sits in the common DISTINCT / GROUP BY materialization path rather than in a
hypergraph-specific code path.
Impact: functions with side effects (audit rows, counters, log writes) are executed 2N+1 times
instead of N, so audit / log / counter tables silently receive duplicated rows and inflated counts
- a data-integrity problem, not merely a performance issue.
How to repeat:
-- requires log_bin_trust_function_creators=1
SET GLOBAL log_bin_trust_function_creators = 1;
CREATE TABLE cnt (n INT);
CREATE TABLE t (a INT PRIMARY KEY, b INT);
INSERT INTO t VALUES (1,5),(2,3),(3,8),(4,1),(5,9);
DELIMITER $$
CREATE FUNCTION bump() RETURNS INT NOT DETERMINISTIC MODIFIES SQL DATA
BEGIN
INSERT INTO cnt VALUES (1);
RETURN 1;
END$$
DELIMITER ;
-- N = 5 rows; each statement should call bump() exactly 5 times
TRUNCATE cnt;
SELECT DISTINCT bump() FROM t;
SELECT COUNT(*) FROM cnt; -- ACTUAL: 6 EXPECTED: 5
TRUNCATE cnt;
SELECT COUNT(*) FROM (SELECT bump() FROM t GROUP BY bump()) d;
SELECT COUNT(*) FROM cnt; -- ACTUAL: 11 EXPECTED: 5
TRUNCATE cnt;
SELECT COUNT(*) FROM (SELECT DISTINCT b, bump() FROM t) d;
SELECT COUNT(*) FROM cnt; -- ACTUAL: 10 EXPECTED: 5
-- Control: the ORDER BY path is correct on trunk (fixed by 46096fc3590)
TRUNCATE cnt;
SELECT a FROM t ORDER BY bump(), a;
SELECT COUNT(*) FROM cnt; -- 5 (correct)
-- Row-count scaling of the DISTINCT form: 1 row -> 2 calls, 2 rows -> 3 calls,
-- 5 rows -> 6 calls (always N+1).
-- Reference: MySQL 9.7.2 official image returns 5 / 5 / 5 for the three forms above
-- and 5 for the ORDER BY control - i.e. trunk regressed.
Suggested fix:
46096fc3590 (Bug#36578540) fixed the double evaluation for the ORDER BY filesort path but not
for the DISTINCT / GROUP BY de-duplication path. The same "materialize before de-duplicating"
treatment needs to be applied to the DISTINCT and GROUP BY paths so that a side-effecting function
is evaluated exactly once per input row.
