What causes "Window function %s requires an OVER clause" in prestodb/presto?

Add an OVER clause with the appropriate partitioning/ordering, e.g. `row_number() OVER (PARTITION BY x ORDER BY y)`

prestodb/presto · error · SemanticException

WINDOW_REQUIRES_OVER

WINDOW_REQUIRES_OVER

Error message

Window function %s requires an OVER clause

What it means

Presto's window functions (rank, row_number, dense_rank, ntile, etc.) are only defined in the context of a window specification. When function analysis finds a WINDOW-kind function invoked without an OVER clause, it throws this semantic error.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/WindowFunctionValidator.java:41

class WindowFunctionValidator
        extends DefaultExpressionTraversalVisitor<Void, Analysis>
{
    private final FunctionAndTypeResolver functionAndTypeResolver;

    public WindowFunctionValidator(FunctionAndTypeResolver functionAndTypeResolver)
    {
        this.functionAndTypeResolver = requireNonNull(functionAndTypeResolver, "functionManager is null");
    }

    @Override
    protected Void visitFunctionCall(FunctionCall functionCall, Analysis analysis)
    {
        requireNonNull(analysis, "analysis is null");

        FunctionMetadata functionMetadata = functionAndTypeResolver.getFunctionMetadata(analysis.getFunctionHandle(functionCall));
        if (functionMetadata != null && functionMetadata.getFunctionKind() == WINDOW && !functionCall.getWindow().isPresent()) {
            throw new SemanticException(WINDOW_REQUIRES_OVER, functionCall, "Window function %s requires an OVER clause", functionMetadata.getName());
        }
        return super.visitFunctionCall(functionCall, analysis);
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

Add an OVER clause with the appropriate partitioning/ordering, e.g. `row_number() OVER (PARTITION BY x ORDER BY y)`
Use `OVER ()` if the function should run over the entire result set
Replace the window function with an aggregate + GROUP BY if no windowing is actually needed

Example fix

// before
SELECT name, row_number() FROM employees;
// after
SELECT name, row_number() OVER (ORDER BY salary) FROM employees;

Defensive patterns

Strategy: validation

Validate before calling

// Check that every window function call in the SQL text carries an OVER clause
for (FunctionCall call : extractFunctionCalls(parsedSql)) {
    if (isWindowFunction(call.getName()) && !call.getWindow().isPresent()) {
        throw new IllegalArgumentException(call.getName() + " requires OVER");
    }
}

Type guard

boolean needsOver(String functionName) {
    return Set.of("row_number","rank","dense_rank","ntile","lag","lead","first_value","last_value","nth_value").contains(functionName.toLowerCase());
}

Try / catch

try { executeQuery(sql); } catch (SemanticException e) { if (e.getCode().equals(WINDOW_REQUIRES_OVER)) { sql = addOverClause(sql); } else throw e; }

Prevention

Memorize the Presto window-function list (all require OVER)
When generating SQL, always append an OVER spec alongside window functions
Use OVER () when windowing defaults are intended
Run queries through a SQL linter that validates window syntax

When it happens

Trigger: `SELECT row_number() FROM t` — calling a window function such as row_number(), rank(), dense_rank(), lag(), lead(), first_value() without `OVER (...)`.

Common situations: Queries copied from engines with different syntax; forgetting the OVER clause after refactoring; hand-written SQL where the OVER clause was accidentally deleted; generated SQL that appends window functions without window specs.

Related errors

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