[dbolympics] Support correlated scalar subquery without aggregation #16137

Activity

1. findepi

   added

   enhancementNew feature or requestNew feature or request

   on May 21, 2025
2. logan-keede commented on May 25, 2025

   

   logan-keede

   on May 25, 2025

   Contributor

   More actions

   datafusion/datafusion/expr/src/logical_plan/invariants.rs

   Lines 174 to 201 in 34f250a

   	if !subquery.outer_ref_columns.is_empty() {
   	match strip_inner_query(inner_plan) {
   	LogicalPlan::Aggregate(agg) => {
   	check_aggregation_in_scalar_subquery(inner_plan, agg)
   	}
   	LogicalPlan::Filter(Filter { input, .. })
   	if matches!(input.as_ref(), LogicalPlan::Aggregate(_)) =>
   	{
   	if let LogicalPlan::Aggregate(agg) = input.as_ref() {
   	check_aggregation_in_scalar_subquery(inner_plan, agg)
   	} else {
   	Ok(())
   	}
   	}
   	_ => {
   	if inner_plan
   	.max_rows()
   	.filter(|max_row| *max_row <= 1)
   	.is_some()
   	{
   	Ok(())
   	} else {
   	plan_err!(
   	"Correlated scalar subquery must be aggregated to return at most one row"
   	)
   	}
   	}
   	}?;

   DataFusion tries to predict maximum rows possible instead of actually checking the number of rows.

   DuckDB used to fix this by limiting the number of rows to one, now they have changed the behaviour to pass error by using something like case when rows> 0 then error(err_msg) into the projection itself .

   ┌──────────────┬────────────┐
   │         PROJECTION        │
   │    ────────────────────   │
   │ CASE  WHEN ((#1 > 1)) THEN│
   │  (error('More than one row│
   │   returned by a subquery  │
   │   used as an expression - │
   │    scalar subqueries can  │
   │  only return a single row.│
   │          Use "SET         │
   │ scalar_subquery_error_on_m│
   │   ultiple_rows=false" to  │
   │     revert to previous    │
   │   behavior of returning a │
   │ random row.')) ELSE #0 END│
   │                           │
   │          ~1 Rows          │
   └─────────────┬─────────────┘
   

   Reference PR

   I tried using limit and got

   >  WITH src AS (
       SELECT *
       FROM (VALUES
           (1, NULL, 'Europe'),
           (2, 1, 'Warsaw'),
           (3, 1, 'Paris')
       ) t(id, parent_id, name)
   )
   SELECT
       id,
       name,
       (SELECT p.name FROM src p WHERE p.id = s.parent_id limit 1) AS parent_name
   FROM src s;
   This feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)

   which is weird to say the least. I do not understand why just adding a limit will make it different.
3. alamb

   changed the title [-]Support correlated scalar subquery without aggregation[/-] [+][dbolympics] Support correlated scalar subquery without aggregation[/+] on Oct 24, 2026
4. alamb commented on Oct 24, 2026

   

   alamb

   on Oct 24, 2026

   Contributor

   More actions

   Affects DB Olympics, see: #26661
5. alamb commented on Oct 28, 2026

   

   alamb

   on Oct 28, 2026

   Contributor

   More actions

   I think we should implement support support for this feature as part of the more general subquery decorrelation project described here

   • [dbolympics] General framework to decorrelate the subqueries #5492

   in my opinion WE SHOULD NOT try and extend the existing subquery rewrites to support this type of query as that risks introducing many more wrong results, see #5492 (comment)