risingwavelabs/risingwave · error · BatchError

Scalar subquery produced more than one row.

Error message

Scalar subquery produced more than one row.

What it means

The `MaxOneRow` executor buffers the first row from its child and, if a second row arrives, raises this error. It exists solely as the runtime check for scalar subqueries: a scalar subquery used as an expression must return exactly one row, so returning more than one makes the query invalid. The deliberate two-phase buffering (yield only after the child is exhausted) prevents a parent like `LIMIT 1` from cancelling before the violation is detected.

Source

Thrown at src/batch/executors/src/executor/max_one_row.rs:70

    }

    fn identity(&self) -> &str {
        &self.identity
    }

    #[try_stream(boxed, ok = DataChunk, error = BatchError)]
    async fn execute(self: Box<Self>) {
        let data_types = self.child.schema().data_types();
        let mut result = None;

        #[for_await]
        for chunk in self.child.execute() {
            let chunk = chunk?;
            for row in chunk.rows() {
                if result.is_some() {
                    // `MaxOneRow` is currently only used for the runtime check of
                    // scalar subqueries, so we raise a precise error here.
                    bail!("Scalar subquery produced more than one row.");
                } else {
                    // We do not immediately yield the chunk here. Instead, we store
                    // it in `result` and only yield it when the child executor is
                    // exhausted, in case the parent executor cancels the execution
                    // after receiving the row (like `limit 1`).
                    result = Some(DataChunk::from_rows(&[row], &data_types));
                }
            }
        }

        if let Some(result) = result {
            yield result;
        }
    }
}

#[cfg(test)]
mod tests {

View on GitHub (pinned to 6469eb736d)

Solutions

1. Wrap the subquery in an aggregate to collapse it to one row: `(SELECT max(v) FROM t WHERE ...)`.
2. Add `LIMIT 1` with an ORDER BY if any single row is acceptable: `(SELECT v FROM t WHERE ... ORDER BY ts DESC LIMIT 1)`.
3. Tighten the subquery's WHERE predicate so it uniquely identifies one row (e.g. filter on a primary/unique key).

Example fix

-- before
SELECT (SELECT value FROM metrics WHERE tag = 'cpu');
-- after
SELECT (SELECT max(value) FROM metrics WHERE tag = 'cpu');

-- detect at planning time that the subquery may return multiple rows
SELECT count(*) FROM t WHERE k > 10;  -- if > 1, a scalar subquery on the same predicate will fail

// Rust caller wrapping query execution
match execute_query(sql).await {
    Err(e) if e.to_string().contains("Scalar subquery produced more than one row") => {
        // rewrite with aggregate/LIMIT or reject the query upstream
    }
    other => other?,
}

When it happens

Trigger: Running a query whose scalar subquery (used in SELECT, WHERE, etc.) matches multiple rows at runtime, e.g. `SELECT (SELECT v FROM t WHERE k > 10)` when several rows satisfy `k > 10`.

Common situations: Data grew over time so a subquery that used to return one row now returns many (e.g. `WHERE ts < now()` matched a single row yesterday but two rows today); missing or too-narrow predicates; missing aggregate like MAX/MIN around the subquery.

Related errors

• unspecified AsOf join inequality type
• LogStore row sequential scan should not have input executor!
• MergeSortExchangeExecutor should not have child!
• failed to get row stream from mysql query
• Row sequential scan should not have input executor!

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