{"record":{"id":"517871e5b5debe1a","repo":"risingwavelabs/risingwave","slug":"scalar-subquery-produced-more-than-one-row","errorCode":null,"errorMessage":"Scalar subquery produced more than one row.","messagePattern":"Scalar subquery produced more than one row\\.","errorType":"exception","errorClass":"BatchError","httpStatus":null,"severity":"error","filePath":"src/batch/executors/src/executor/max_one_row.rs","lineNumber":70,"sourceCode":"    }\n\n    fn identity(&self) -> &str {\n        &self.identity\n    }\n\n    #[try_stream(boxed, ok = DataChunk, error = BatchError)]\n    async fn execute(self: Box<Self>) {\n        let data_types = self.child.schema().data_types();\n        let mut result = None;\n\n        #[for_await]\n        for chunk in self.child.execute() {\n            let chunk = chunk?;\n            for row in chunk.rows() {\n                if result.is_some() {\n                    // `MaxOneRow` is currently only used for the runtime check of\n                    // scalar subqueries, so we raise a precise error here.\n                    bail!(\"Scalar subquery produced more than one row.\");\n                } else {\n                    // We do not immediately yield the chunk here. Instead, we store\n                    // it in `result` and only yield it when the child executor is\n                    // exhausted, in case the parent executor cancels the execution\n                    // after receiving the row (like `limit 1`).\n                    result = Some(DataChunk::from_rows(&[row], &data_types));\n                }\n            }\n        }\n\n        if let Some(result) = result {\n            yield result;\n        }\n    }\n}\n\n#[cfg(test)]\nmod tests {","sourceCodeStart":52,"sourceCodeEnd":88,"githubUrl":"https://github.com/risingwavelabs/risingwave/blob/6469eb736d691e8e9b8a419a57edd6429ca77417/src/batch/executors/src/executor/max_one_row.rs#L52-L88","documentation":"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.","triggerScenarios":"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`.","commonSituations":"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.","solutions":["Wrap the subquery in an aggregate to collapse it to one row: `(SELECT max(v) FROM t WHERE ...)`.","Add `LIMIT 1` with an ORDER BY if any single row is acceptable: `(SELECT v FROM t WHERE ... ORDER BY ts DESC LIMIT 1)`.","Tighten the subquery's WHERE predicate so it uniquely identifies one row (e.g. filter on a primary/unique key)."],"exampleFix":"-- before\nSELECT (SELECT value FROM metrics WHERE tag = 'cpu');\n-- after\nSELECT (SELECT max(value) FROM metrics WHERE tag = 'cpu');","handlingStrategy":"try-catch","validationCode":"-- detect at planning time that the subquery may return multiple rows\nSELECT count(*) FROM t WHERE k > 10;  -- if > 1, a scalar subquery on the same predicate will fail","typeGuard":null,"tryCatchPattern":"// Rust caller wrapping query execution\nmatch execute_query(sql).await {\n    Err(e) if e.to_string().contains(\"Scalar subquery produced more than one row\") => {\n        // rewrite with aggregate/LIMIT or reject the query upstream\n    }\n    other => other?,\n}","preventionTips":["Always wrap scalar subqueries in an aggregate (min/max/avg) unless the predicate guarantees a single row.","Prefer filtering subqueries on a unique/primary key.","Remember data changes over time: a predicate returning one row today may return many tomorrow.","Use ORDER BY ... LIMIT 1 when any single row is acceptable."],"tags":["batch-executor","subquery","scalar-subquery","runtime-check"],"backgroundTag":"unexpected-result-count","analyzedSha":"6469eb736d691e8e9b8a419a57edd6429ca77417","analyzedAt":"2026-09-11T21:06:21.487Z","contentChangedAt":"2026-09-11T21:06:21.487Z","schemaVersion":2},"datasetVersion":"2026-09-14T11:17:12.474Z"}