Description:
Window aggregate functions with a frame that excludes the current row return the current row's value instead of NULL (COUNT returns 1 instead of 0) whenever the optimizer evaluates the query without windowing steps. In JOIN::make_tmp_tables_info (sql/sql_select.cc):
m_windowing_steps = m_windows.elements > 0 && !plan_is_const() &&
!implicit_grouping && !group_optimized_away;
With m_windowing_steps false, no window iterators or tmp tables are created and the window function is evaluated inline over the single row. The only mechanism that produces NULL for an empty frame is Window::m_do_copy_null, checked in Item_sum::wf_common_init, and its only writers are in the buffered windowing code (process_buffered_windowing_record), which never runs on these paths. The frame borders are therefore never consulted.
This affects all three conditions in the gate:
(1) const plans, e.g. an equality lookup on a unique or primary key, where adding a unique index flips the result of an identical query from correct to wrong.
(2) implicit grouping, i.e. a framed window function layered over an aggregate with no GROUP BY, which needs no index and affects tables of any size.
(3) GROUP BY optimized away, when the optimizer proves there is only one group.
All framed aggregates are affected (SUM, AVG, MIN, MAX, COUNT, STDDEV, VARIANCE, BIT_AND/OR/XOR), plus FIRST_VALUE and LAST_VALUE with excluding frames. Present since window functions were introduced in 8.0.2; reproduced on 8.0.46 and 8.4.11, and the code is unchanged on the 8.0, 8.4 and trunk branches.
Per the manual (aggregate function descriptions), aggregates used as window functions operate on the rows in the frame, and with no matching rows SUM/MIN/MAX/AVG return NULL and COUNT returns 0.
How to repeat:
-- Case 1: const plan via unique index (result flips when the index is added)
CREATE TABLE idxdemo(seq INT, val INT);
INSERT INTO idxdemo VALUES (1,-3);
SELECT SUM(val) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res
FROM idxdemo WHERE seq = 1;
-- returns NULL (correct)
ALTER TABLE idxdemo ADD UNIQUE INDEX ux_seq (seq);
SELECT SUM(val) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res
FROM idxdemo WHERE seq = 1;
-- returns -3, expected NULL; EXPLAIN shows access type "const"
-- Case 2: implicit grouping, no index involved, any table size
CREATE TABLE noidx(seq INT, val INT);
INSERT INTO noidx VALUES (1,-3),(2,10),(3,20);
SELECT SUM(SUM(val)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res
FROM noidx;
-- returns 27 (the grand total of all rows), expected NULL
-- Case 3: GROUP BY optimized away (constant grouping expression)
SELECT SUM(SUM(val)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res
FROM noidx GROUP BY 'x';
-- returns 27, expected NULL
-- COUNT and FIRST_VALUE variants of case 1 are wrong the same way:
-- COUNT returns 1 (expected 0), FIRST_VALUE returns -3 (expected NULL)
Suggested fix:
On the no-windowing-steps paths, arm the same empty-frame mechanism the buffered path uses (Window::m_do_copy_null) when the frame provably excludes the current row (to-border is N PRECEDING with N > 0, or from-border is M FOLLOWING with M > 0, offsets evaluated at execution time). The flag must be scoped to framing functions only, the way process_buffered_windowing_record scopes it around the CFT_WF_FRAMING copy: LEAD/LAG ignore frames but share wf_common_init, so an unscoped flag would break LAG(expr, 0) and default arguments.
Description: Window aggregate functions with a frame that excludes the current row return the current row's value instead of NULL (COUNT returns 1 instead of 0) whenever the optimizer evaluates the query without windowing steps. In JOIN::make_tmp_tables_info (sql/sql_select.cc): m_windowing_steps = m_windows.elements > 0 && !plan_is_const() && !implicit_grouping && !group_optimized_away; With m_windowing_steps false, no window iterators or tmp tables are created and the window function is evaluated inline over the single row. The only mechanism that produces NULL for an empty frame is Window::m_do_copy_null, checked in Item_sum::wf_common_init, and its only writers are in the buffered windowing code (process_buffered_windowing_record), which never runs on these paths. The frame borders are therefore never consulted. This affects all three conditions in the gate: (1) const plans, e.g. an equality lookup on a unique or primary key, where adding a unique index flips the result of an identical query from correct to wrong. (2) implicit grouping, i.e. a framed window function layered over an aggregate with no GROUP BY, which needs no index and affects tables of any size. (3) GROUP BY optimized away, when the optimizer proves there is only one group. All framed aggregates are affected (SUM, AVG, MIN, MAX, COUNT, STDDEV, VARIANCE, BIT_AND/OR/XOR), plus FIRST_VALUE and LAST_VALUE with excluding frames. Present since window functions were introduced in 8.0.2; reproduced on 8.0.46 and 8.4.11, and the code is unchanged on the 8.0, 8.4 and trunk branches. Per the manual (aggregate function descriptions), aggregates used as window functions operate on the rows in the frame, and with no matching rows SUM/MIN/MAX/AVG return NULL and COUNT returns 0. How to repeat: -- Case 1: const plan via unique index (result flips when the index is added) CREATE TABLE idxdemo(seq INT, val INT); INSERT INTO idxdemo VALUES (1,-3); SELECT SUM(val) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res FROM idxdemo WHERE seq = 1; -- returns NULL (correct) ALTER TABLE idxdemo ADD UNIQUE INDEX ux_seq (seq); SELECT SUM(val) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res FROM idxdemo WHERE seq = 1; -- returns -3, expected NULL; EXPLAIN shows access type "const" -- Case 2: implicit grouping, no index involved, any table size CREATE TABLE noidx(seq INT, val INT); INSERT INTO noidx VALUES (1,-3),(2,10),(3,20); SELECT SUM(SUM(val)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res FROM noidx; -- returns 27 (the grand total of all rows), expected NULL -- Case 3: GROUP BY optimized away (constant grouping expression) SELECT SUM(SUM(val)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS res FROM noidx GROUP BY 'x'; -- returns 27, expected NULL -- COUNT and FIRST_VALUE variants of case 1 are wrong the same way: -- COUNT returns 1 (expected 0), FIRST_VALUE returns -3 (expected NULL) Suggested fix: On the no-windowing-steps paths, arm the same empty-frame mechanism the buffered path uses (Window::m_do_copy_null) when the frame provably excludes the current row (to-border is N PRECEDING with N > 0, or from-border is M FOLLOWING with M > 0, offsets evaluated at execution time). The flag must be scoped to framing functions only, the way process_buffered_windowing_record scopes it around the CFT_WF_FRAMING copy: LEAD/LAG ignore frames but share wf_common_init, so an unscoped flag would break LAG(expr, 0) and default arguments.