{"record":{"id":"2bdc9c6d6dcf7210","repo":"risingwavelabs/risingwave","slug":"scalar-subquery-might-produce-more-than-one-row","errorCode":null,"errorMessage":"Scalar subquery might produce more than one row.","messagePattern":"Scalar subquery might produce more than one row\\.","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"src/frontend/src/optimizer/logical_optimization.rs","lineNumber":754,"sourceCode":"                ctx.trace(plan.explain_to_string());\n            }\n        }\n        plan = plan.optimize_by_rules(&SET_OPERATION_MERGE)?;\n        plan = plan.optimize_by_rules(&SET_OPERATION_TO_JOIN)?;\n        // Convert `generate_series` ends with `now()` to a `Now` source. Only for streaming mode.\n        // Should be applied before converting table function to project set.\n        plan = plan.optimize_by_rules(&STREAM_GENERATE_SERIES_WITH_NOW)?;\n        // In order to unnest a table function, we need to convert it into a `project_set` first.\n        plan = plan.optimize_by_rules(&TABLE_FUNCTION_CONVERT)?;\n\n        plan = plan.optimize_by_rules(&CORRELATED_TOP_N_TO_VECTOR_SEARCH_FOR_STREAM)?;\n\n        plan = Self::subquery_unnesting(plan, enable_share_plan, explain_trace, &ctx)?;\n        if has_logical_max_one_row(plan.clone()) {\n            // `MaxOneRow` is currently only used for the runtime check of\n            // scalar subqueries, while it's not supported in streaming mode, so\n            // we raise a precise error here.\n            bail!(\"Scalar subquery might produce more than one row.\");\n        }\n\n        // Same to batch plan optimization, this rule shall be applied before\n        // predicate push down\n        plan = plan.optimize_by_rules(&LOGICAL_FILTER_EXPRESSION_SIMPLIFY)?;\n\n        // Predicate Push-down\n        plan = Self::predicate_pushdown(plan, explain_trace, &ctx);\n\n        if plan.ctx().session_ctx().config().enable_join_ordering() {\n            // Merge inner joins and intermediate filters into multijoin\n            // This rule assumes that filters have already been pushed down near to\n            // their relevant joins.\n            plan = plan.optimize_by_rules(&TO_MULTI_JOIN)?;\n\n            // Reorder multijoin into join tree.\n            if plan\n                .ctx()","sourceCodeStart":736,"sourceCodeEnd":772,"githubUrl":"https://github.com/risingwavelabs/risingwave/blob/6469eb736d691e8e9b8a419a57edd6429ca77417/src/frontend/src/optimizer/logical_optimization.rs#L736-L772","documentation":"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.","triggerScenarios":"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.","commonSituations":"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).","solutions":["Make the scalar subquery provably single-row: correlate it on a unique/primary key.","Rewrite the subquery to use an aggregate (e.g. MAX/MIN) so it always returns one row.","Add LIMIT 1 inside the scalar subquery if any single row is acceptable.","Rewrite as a JOIN with the subquery unnested instead of a scalar subquery."],"exampleFix":"// before\nCREATE MATERIALIZED VIEW mv AS\nSELECT v, (SELECT w FROM t WHERE t.k = s.k) AS w FROM s;\n-- after (aggregate guarantees one row)\nCREATE MATERIALIZED VIEW mv AS\nSELECT s.v, (SELECT max(t.w) FROM t WHERE t.k = s.k) AS w FROM s;","handlingStrategy":"validation","validationCode":"-- avoid uncorrelated/multi-row scalar subqueries in MV definitions\n-- safe pattern: subquery correlated on a unique key, or wrapped in MAX()/LIMIT 1","typeGuard":null,"tryCatchPattern":"match create_mv_result {\n    Err(e) if e.to_string().contains(\"might produce more than one row\") => {\n        eprintln!(\"Make the scalar subquery single-row (aggregate, LIMIT 1, or unique-key correlation)\");\n    }\n    other => other?,\n}","preventionTips":["Wrap scalar subqueries in aggregates (MAX/MIN/SUM) so they always return one row.","Correlate scalar subqueries on primary/unique keys.","Remember this restriction applies to streaming MVs/sinks, not batch queries."],"tags":["streaming","subquery","planner","sql"],"backgroundTag":"unsupported-operation","analyzedSha":"6469eb736d691e8e9b8a419a57edd6429ca77417","analyzedAt":"2026-09-11T21:06:21.487Z","contentChangedAt":"2026-09-11T21:06:21.487Z","schemaVersion":2},"datasetVersion":"2026-09-14T16:17:12.679Z"}