risingwavelabs/risingwave · error

Scalar subquery might produce more than one row.

Error message

Scalar subquery might produce more than one row.

What it means

After subquery unnesting, the optimizer inserts a MaxOneRow operator only when a scalar subquery is guaranteed to return at most one row. Since MaxOneRow is not supported in streaming mode, gen_optimized_logical_plan_for_stream raises this precise error whenever the plan still contains a scalar subquery that might return multiple rows.

Source

Thrown at src/frontend/src/optimizer/logical_optimization.rs:754

                ctx.trace(plan.explain_to_string());
            }
        }
        plan = plan.optimize_by_rules(&SET_OPERATION_MERGE)?;
        plan = plan.optimize_by_rules(&SET_OPERATION_TO_JOIN)?;
        // Convert `generate_series` ends with `now()` to a `Now` source. Only for streaming mode.
        // Should be applied before converting table function to project set.
        plan = plan.optimize_by_rules(&STREAM_GENERATE_SERIES_WITH_NOW)?;
        // In order to unnest a table function, we need to convert it into a `project_set` first.
        plan = plan.optimize_by_rules(&TABLE_FUNCTION_CONVERT)?;

        plan = plan.optimize_by_rules(&CORRELATED_TOP_N_TO_VECTOR_SEARCH_FOR_STREAM)?;

        plan = Self::subquery_unnesting(plan, enable_share_plan, explain_trace, &ctx)?;
        if has_logical_max_one_row(plan.clone()) {
            // `MaxOneRow` is currently only used for the runtime check of
            // scalar subqueries, while it's not supported in streaming mode, so
            // we raise a precise error here.
            bail!("Scalar subquery might produce more than one row.");
        }

        // Same to batch plan optimization, this rule shall be applied before
        // predicate push down
        plan = plan.optimize_by_rules(&LOGICAL_FILTER_EXPRESSION_SIMPLIFY)?;

        // Predicate Push-down
        plan = Self::predicate_pushdown(plan, explain_trace, &ctx);

        if plan.ctx().session_ctx().config().enable_join_ordering() {
            // Merge inner joins and intermediate filters into multijoin
            // This rule assumes that filters have already been pushed down near to
            // their relevant joins.
            plan = plan.optimize_by_rules(&TO_MULTI_JOIN)?;

            // Reorder multijoin into join tree.
            if plan
                .ctx()

View on GitHub (pinned to 6469eb736d)

Solutions

1. Make the scalar subquery provably single-row: correlate it on a unique/primary key.
2. Rewrite the subquery to use an aggregate (e.g. MAX/MIN) so it always returns one row.
3. Add LIMIT 1 inside the scalar subquery if any single row is acceptable.
4. Rewrite as a JOIN with the subquery unnested instead of a scalar subquery.

Example fix

// before
CREATE MATERIALIZED VIEW mv AS
SELECT v, (SELECT w FROM t WHERE t.k = s.k) AS w FROM s;
-- after (aggregate guarantees one row)
CREATE MATERIALIZED VIEW mv AS
SELECT s.v, (SELECT max(t.w) FROM t WHERE t.k = s.k) AS w FROM s;

-- avoid uncorrelated/multi-row scalar subqueries in MV definitions
-- safe pattern: subquery correlated on a unique key, or wrapped in MAX()/LIMIT 1

match create_mv_result {
    Err(e) if e.to_string().contains("might produce more than one row") => {
        eprintln!("Make the scalar subquery single-row (aggregate, LIMIT 1, or unique-key correlation)");
    }
    other => other?,
}

When it happens

Trigger: Creating a streaming MV or sink whose query uses a scalar subquery (e.g. in SELECT or WHERE) that is not guaranteed to return exactly one row, such as `SELECT (SELECT x FROM t WHERE ...)`. Batch plans are fine; only streaming plans hit this.

Common situations: Converting a batch-style SQL query with scalar subqueries into an MV, forgetting that streaming requires the subquery to provably return at most one row (e.g. correlated on a unique key or with an aggregate/LIMIT).

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

• subquery must return only one column
• More than one row returned by {0} used as an expression
• {} does not support struct, array, map, vector, jsonb for co
• unimplemented
• Expected at most 1 clean_watermark_index per table, got {:?}

AI-assisted analysis of risingwavelabs/risingwave@6469eb736d (2026-09-11). Data as JSON: /api/errors/2bdc9c6d6dcf7210. Report an issue: GitHub.