prestodb/presto · error · SemanticException

MUST_BE_WINDOW_FUNCTION

MUST_BE_WINDOW_FUNCTION

Error message

Not a window function: %s

What it means

Every function used with an OVER clause must be either an AGGREGATE or a WINDOW kind function. After resolving the function handle, the analyzer checks the FunctionKind metadata; if it is neither (e.g. a scalar function), this MUST_BE_WINDOW_FUNCTION error is thrown. It usually means OVER was attached to a function that can't be windowed.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:4198

                List<FunctionCall> nestedWindowFunctions = extractWindowFunctions(toExtract.build());

                if (!nestedWindowFunctions.isEmpty()) {
                    throw new SemanticException(NESTED_WINDOW, node, "Cannot nest window functions inside window function '%s': %s",
                            windowFunction,
                            windowFunctions);
                }

                if (windowFunction.isDistinct()) {
                    throw new SemanticException(NOT_SUPPORTED, node, "DISTINCT in window function parameters not yet supported: %s", windowFunction);
                }

                if (window.getFrame().isPresent()) {
                    analyzeWindowFrame(window.getFrame().get());
                }

                FunctionKind kind = functionAndTypeResolver.getFunctionMetadata(analysis.getFunctionHandle(windowFunction)).getFunctionKind();
                if (kind != AGGREGATE && kind != WINDOW) {
                    throw new SemanticException(MUST_BE_WINDOW_FUNCTION, node, "Not a window function: %s", windowFunction.getName());
                }
            }

            return windowFunctions;
        }

        private void analyzeWindowFrame(WindowFrame frame)
        {
            FrameBound.Type startType = frame.getStart().getType();
            FrameBound.Type endType = frame.getEnd().orElseGet(() -> new FrameBound(CURRENT_ROW)).getType();

            if (startType == UNBOUNDED_FOLLOWING) {
                throw new SemanticException(INVALID_WINDOW_FRAME, frame, "Window frame start cannot be UNBOUNDED FOLLOWING");
            }
            if (endType == UNBOUNDED_PRECEDING) {
                throw new SemanticException(INVALID_WINDOW_FRAME, frame, "Window frame end cannot be UNBOUNDED PRECEDING");
            }
            if ((startType == CURRENT_ROW) && (endType == PRECEDING)) {

View on GitHub (pinned to 55bb57d202)

Solutions

1. Check the function name spelling and use a supported window function (row_number, rank, dense_rank, lag, lead, etc.).
2. Remove the OVER clause if the function is scalar and windowing is not actually needed.
3. Verify with `SHOW FUNCTIONS` that the intended function is of kind aggregate or window.
4. If a custom function is needed as a window function, implement/register it with WINDOW (or AGGREGATE) kind instead of SCALAR.

Example fix

-- before
SELECT abs(x) OVER (PARTITION BY y) FROM t;
-- after
SELECT abs(x) FROM t; -- or use a real window function:
SELECT row_number() OVER (PARTITION BY y ORDER BY x) FROM t;

-- Confirm the function is window-capable before attaching OVER:
SELECT function_name, function_kind FROM information_schema.functions WHERE function_name = 'your_fn';
-- only proceed if function_kind IN ('aggregate', 'window').

When it happens

Trigger: `SELECT abs(x) OVER (PARTITION BY y) FROM t` or any scalar/rank-like misuse — getFunctionKind(analysis.getFunctionHandle(windowFunction)) returns something other than AGGREGATE or WINDOW.

Common situations: Typos in window function names falling back to a scalar function of similar name (e.g. `rank()` misspelled); applying OVER to scalar helpers like concat/abs; custom SQL functions registered as SCALAR being used in analytic contexts; engine migrations where a UDF exists as scalar in Presto but as window function elsewhere.

Related errors

• WINDOW_FUNCTION_ORDERBY_LITERAL
• NESTED_WINDOW
• MUST_BE_AGGREGATE_OR_GROUP_BY
• NESTED_AGGREGATION
• NESTED_WINDOW

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/6cb46af6e4ca55f9. Report an issue: GitHub.