Using window functions in WHERE clauses is not supported

Error message

Using window functions in WHERE clauses is not supported

What it means

This is a compile-time panic enforced by diesel's QueryId trait impl for WhereClause. Window functions (OVER clauses) are only valid in SELECT/ORDER BY contexts; SQL forbids them in WHERE. Diesel evaluates `Expr::IS_WINDOW_FUNCTION` in a const block and fails compilation when a query tries to place a window function inside a WHERE clause.

Solutions

Wrap the window function in a subquery or CTE (WITH ...) and apply .filter() on the aliased result column in the outer query.
Replace the window-function filter with an equivalent aggregate/GROUP BY + HAVING formulation when possible.
Use diesel's `select_with`/raw SQL escape hatch (sql_query) for databases whose dialect allows the pattern, only if truly needed.
Check diesel documentation on window functions for supported placement contexts (SELECT list, ORDER BY).

Example fix

// before
users.filter(row_number().over(order_by(users::id)).eq(1))

// after
// filter via subquery/CTE on the window result instead
sql_query("SELECT * FROM (SELECT *, row_number() OVER (ORDER BY id) AS rn FROM users) t WHERE rn = 1")

Defensive patterns

Strategy: validation

Validate before calling

// Compile-time: this error IS the guard. Pattern-check before writing the query:
// any Expr with IS_WINDOW_FUNCTION (e.g. x.over(...)) must never be passed to .filter().

Type guard

// Cannot be caught at runtime; enforce via code review:
// assert no `.over(` expression appears inside `.filter(` in diesel query code.

Prevention

Treat .filter() as WHERE-only: never pass window functions (over()) into it.
Filter window results via subquery/CTE on the projected column.
Prefer GROUP BY + HAVING for per-group filtering needs.

When it happens

Trigger: Building a query where an expression marked IS_WINDOW_FUNCTION (e.g. row_number().over(...) or any .over() call) is passed to .filter() / into a WHERE clause, e.g. `users.filter(row_number().over(...).eq(1))` — the const assertion fires at compile time.

Common situations: Developers trying to select the first row per group filter on row_number(), rank(), dense_rank(), or lag/lead results inside .filter(). SQL requires wrapping the window expression in a subquery/CTE and filtering on the derived column instead.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of diesel-rs/diesel@6fa6ed01b2 (2026-09-07). Data as JSON: /api/errors/261911650dd7225d. Report an issue: GitHub.

Appendix: source

Thrown at diesel/src/query_builder/where_clause.rs:90

impl<DB> From<NoWhereClause> for BoxedCloneWhereClause<'_, DB> {
    fn from(_: NoWhereClause) -> Self {
        BoxedCloneWhereClause::None
    }
}

/// The `WHERE` clause of a query.
#[derive(Debug, Clone, Copy)]
pub struct WhereClause<Expr>(Expr);

impl<Expr: diesel::query_builder::QueryId> diesel::query_builder::QueryId for WhereClause<Expr> {
    type QueryId = WhereClause<<Expr as diesel::query_builder::QueryId>::QueryId>;
    const HAS_STATIC_QUERY_ID: bool =
        <Expr as diesel::query_builder::QueryId>::HAS_STATIC_QUERY_ID && true;

    const IS_WINDOW_FUNCTION: bool = const {
        if Expr::IS_WINDOW_FUNCTION {
            panic!("Using window functions in WHERE clauses is not supported");
        }
        false
    };
}

impl<DB, Expr> QueryFragment<DB> for WhereClause<Expr>
where
    DB: Backend + DieselReserveSpecialization,
    Expr: QueryFragment<DB>,
{
    fn walk_ast<'b>(&'b self, mut out: AstPass<'_, 'b, DB>) -> QueryResult<()> {
        out.push_sql(" WHERE ");
        self.0.walk_ast(out.reborrow())?;
        Ok(())
    }
}

impl<Expr, Predicate> WhereAnd<Predicate> for WhereClause<Expr>

View on GitHub (pinned to 6fa6ed01b2)