What causes "subquery must return only one column" in risingwavelabs/risingwave?

Select exactly one column inside the subquery.

subquery must return only one column

Error message

subquery must return only one column

What it means

into_array_agg converts a subquery expression into an ARRAY_AGG aggregate, but an array can only be built from a single column. If the subquery's schema has more (or fewer) than exactly one output column — detected via exactly_one(out_fields.ones()) — this error is thrown.

Source

Thrown at src/frontend/src/optimizer/mod.rs:355

            return self.plan;
        }
        LogicalProject::with_out_fields(self.plan, &self.out_fields).into()
    }

    /// Transform the [`PlanRoot`] wrapped in an array-construction subquery to a [`PlanRef`]
    /// supported by `ARRAY_AGG`. Similar to the unordered version, this abstracts away internal
    /// `self.plan` which is further modified by `self.required_order` then `self.out_fields`.
    pub fn into_array_agg(self) -> Result<LogicalPlanRef> {
        use generic::Agg;
        use plan_node::PlanAggCall;
        use risingwave_common::types::ListValue;
        use risingwave_expr::aggregate::PbAggKind;

        use crate::expr::{ExprImpl, ExprType, FunctionCall, InputRef};
        use crate::utils::{Condition, IndexSet};

        let Ok(select_idx) = Itertools::exactly_one(self.out_fields.ones()) else {
            bail!("subquery must return only one column");
        };
        let input_column_type = self.plan.schema().fields()[select_idx].data_type();
        let return_type = DataType::list(input_column_type.clone());
        let agg = Agg::new(
            vec![PlanAggCall {
                agg_type: PbAggKind::ArrayAgg.into(),
                return_type: return_type.clone(),
                inputs: vec![InputRef::new(select_idx, input_column_type.clone())],
                distinct: false,
                order_by: self.required_order.column_orders,
                filter: Condition::true_cond(),
                direct_args: vec![],
            }],
            IndexSet::empty(),
            self.plan,
        );
        Ok(LogicalProject::create(
            agg.into(),

View on GitHub (pinned to 6469eb736d)

Solutions

Select exactly one column inside the subquery.
If multiple columns are needed, use a tuple/row comparison or rewrite with a JOIN instead of a subquery.
Move extra columns into a WHERE/JOIN condition instead of the SELECT list.

Example fix

// before
SELECT * FROM s WHERE s.x = ANY(SELECT a, b FROM t);
// after
SELECT * FROM s WHERE s.x = ANY(SELECT a FROM t);

Defensive patterns

Strategy: validation

Validate before calling

-- the subquery used with ANY/IN-style predicates must select exactly one column
SELECT a FROM t;  -- not SELECT a, b FROM t

Try / catch

match query_result {
    Err(e) if e.to_string().contains("subquery must return only one column") => {
        eprintln!("Project exactly one column inside the subquery");
    }
    other => other?,
}

Prevention

Always project a single column in subqueries used as value expressions.
Use JOINs instead of multi-column subquery comparisons.
Lint SQL for `ANY(SELECT *` and multi-column subquery selects.

When it happens

Trigger: Using a subquery in an array-producing context (e.g. `= ANY(subquery)`, IN-unnesting converted to array agg, or expression forms that become array_agg) where the subquery selects multiple columns, such as `SELECT a, b FROM t`.

Common situations: Writing `col = ANY(SELECT a, b FROM t)` or similar multi-column subqueries in predicates; forgetting to project exactly one column inside the subquery.

Related errors

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