Skip to content

Commit 833f499

Browse files
jdymitaraialamb
andauthored
[dbolympics] fix: cast outer projection expr to declared field type in SingleDistinctToGroupBy (#26211)
## Which issue does this PR close? Closes #26193. Closes #17699. ## Rationale for this change When the SingleDistinctToGroupBy optimizer rule rewrites an aggregate combining DISTINCT and non-distinct expressions such as sum over Decimal128, the non-distinct expression is split into inner and outer phases. The outer phase evaluates sum over the already-widened Decimal output of the inner aggregation, which widens precision a second time and causes the resulting schema to diverge from the original aggregate schema. This type divergence also triggers runtime comparison errors in subsequent expressions like nullif or DB Olympics query q158. ## What changes are included in this PR? The outer projection construction in SingleDistinctToGroupBy checks whether the data type produced by outer_aggr differs from the field type declared in the original schema. When a mismatch occurs, the projected expression is wrapped with a cast back to the declared field type, ensuring schema consistency across optimizer passes. ## Are these changes tested? Covered by unit tests in single_distinct_to_groupby.rs and end-to-end sqllogictests in single_distinct_to_groupby.slt and aggregate.slt pinning the returned batch types, EXPLAIN plan with CAST, and multi-aggregate group-by behavior. ## Are there any user-facing changes? Yes, aggregate queries rewritten by SingleDistinctToGroupBy containing Decimal expressions (such as sum over Decimal128) now retain their original declared Decimal precision and scale rather than producing an unintentionally expanded Decimal type. Co-authored-by: Andrew Lamb <[email protected]>
1 parent 0442090 commit 833f499

3 files changed

Lines changed: 83 additions & 6 deletions

File tree

‎datafusion/optimizer/src/single_distinct_to_groupby.rs‎

Lines changed: 39 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,7 @@ use datafusion_expr::builder::project;
3030
use datafusion_expr::expr::AggregateFunctionParams;
3131
use datafusion_expr::expr_schema::ExprSchemable;
3232
use datafusion_expr::{
33-
AggregateUDF, Expr, col,
33+
AggregateUDF, Expr, cast, col,
3434
expr::AggregateFunction,
3535
lit,
3636
logical_plan::{Aggregate, LogicalPlan},
@@ -402,6 +402,12 @@ impl OptimizerRule for SingleDistinctToGroupBy {
402402
.map(|(expr, _)| expr.clone())
403403
.collect();
404404

405+
let outer_aggr = LogicalPlan::Aggregate(Aggregate::try_new(
406+
Arc::new(inner_agg),
407+
outer_group_exprs,
408+
outer_aggr_exprs,
409+
)?);
410+
405411
// so the aggregates are displayed in the same way even after the rewrite
406412
// this optimizer has two kinds of alias:
407413
// - group_by aggr
@@ -417,15 +423,16 @@ impl OptimizerRule for SingleDistinctToGroupBy {
417423
.chain(outer_proj_exprs.into_iter().enumerate().map(|(idx, expr)| {
418424
let idx = idx + group_size;
419425
let (qualifier, field) = schema.qualified_field(idx);
426+
let current_type = outer_aggr.schema().field(idx).data_type();
427+
let expr = if current_type != field.data_type() {
428+
cast(expr, field.data_type().clone())
429+
} else {
430+
expr
431+
};
420432
expr.alias_qualified(qualifier.cloned(), field.name())
421433
}))
422434
.collect();
423435

424-
let outer_aggr = LogicalPlan::Aggregate(Aggregate::try_new(
425-
Arc::new(inner_agg),
426-
outer_group_exprs,
427-
outer_aggr_exprs,
428-
)?);
429436
Ok(Transformed::yes(project(outer_aggr, alias_expr)?))
430437
}
431438
_ => Ok(Transformed::no(plan)),
@@ -1310,4 +1317,30 @@ mod tests {
13101317
",
13111318
)
13121319
}
1320+
1321+
#[test]
1322+
fn sum_decimal_with_distinct_preserves_declared_type() -> Result<()> {
1323+
let schema = Schema::new(vec![
1324+
Field::new("d", DataType::Decimal128(5, 2), false),
1325+
Field::new("x", DataType::Int32, false),
1326+
]);
1327+
let table_scan = table_scan(Some("test"), &schema, None)?.build()?;
1328+
1329+
let plan = LogicalPlanBuilder::from(table_scan)
1330+
.aggregate(
1331+
Vec::<Expr>::new(),
1332+
vec![sum(col("d")), count_distinct(col("x"))],
1333+
)?
1334+
.build()?;
1335+
1336+
let rule: Arc<dyn OptimizerRule + Send + Sync> =
1337+
Arc::new(SingleDistinctToGroupBy::new());
1338+
let optimizer = Optimizer::with_rules(vec![rule]);
1339+
let optimized_plan = optimizer
1340+
.optimize(plan.clone(), &RegistryOptimizerContext::new(), |_, _| {})
1341+
.expect("failed to optimize plan");
1342+
1343+
assert_eq!(plan.schema().as_ref(), optimized_plan.schema().as_ref());
1344+
Ok(())
1345+
}
13131346
}

‎datafusion/sqllogictest/test_files/aggregate.slt‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10629,3 +10629,17 @@ DROP TABLE a;
1062910629

1063010630
statement ok
1063110631
DROP TABLE b;
10632+
10633+
10634+
# Repro for issue #17699 / #26661: distinct count with sum(decimal) preserves declared type across rewrites
10635+
statement ok
10636+
CREATE TABLE s_decimal_distinct (g varchar, k varchar, x decimal(18,6)) AS VALUES ('a', 'u1', 1.5), ('a', 'u2', 2.5), ('b', 'u1', 3.0);
10637+
10638+
query TRII
10639+
SELECT g, nullif(sum(x), 0) AS s, count(*) AS n, count(DISTINCT k) AS d FROM s_decimal_distinct GROUP BY g ORDER BY g;
10640+
----
10641+
a 4.000000 2 2
10642+
b 3.000000 1 1
10643+
10644+
statement ok
10645+
DROP TABLE s_decimal_distinct;

‎datafusion/sqllogictest/test_files/single_distinct_to_groupby.slt‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -453,3 +453,33 @@ DROP TABLE empty_t;
453453

454454
statement ok
455455
DROP TABLE t;
456+
457+
# Repro for issue #26193: single distinct with sum(decimal) preserves declared type
458+
statement ok
459+
CREATE TABLE t_decimal_distinct AS SELECT * FROM (VALUES (CAST(1.25 AS DECIMAL(5,2)), 1), (CAST(2.50 AS DECIMAL(5,2)), 2)) AS v(d, x);
460+
461+
query TT
462+
SELECT arrow_typeof(s), arrow_typeof(c) FROM (SELECT sum(d) AS s, count(DISTINCT x) AS c FROM t_decimal_distinct);
463+
----
464+
Decimal128(15, 2) Int64
465+
466+
query TT
467+
EXPLAIN SELECT sum(d) AS s, count(DISTINCT x) AS c FROM t_decimal_distinct;
468+
----
469+
logical_plan
470+
01)Projection: CAST(sum(alias2) AS Decimal128(15, 2)) AS s, count(alias1) AS c
471+
02)--Aggregate: groupBy=[[]], aggr=[[sum(alias2), count(alias1)]]
472+
03)----Aggregate: groupBy=[[t_decimal_distinct.x AS alias1]], aggr=[[sum(t_decimal_distinct.d) AS alias2]]
473+
04)------TableScan: t_decimal_distinct projection=[d, x]
474+
physical_plan
475+
01)ProjectionExec: expr=[CAST(sum(alias2)@0 AS Decimal128(15, 2)) as s, count(alias1)@1 as c]
476+
02)--AggregateExec: mode=Final, gby=[], aggr=[sum(alias2), count(alias1)]
477+
03)----CoalescePartitionsExec
478+
04)------AggregateExec: mode=Partial, gby=[], aggr=[sum(alias2), count(alias1)]
479+
05)--------AggregateExec: mode=FinalPartitioned, gby=[alias1@0 as alias1], aggr=[sum(t_decimal_distinct.d) as alias2]
480+
06)----------RepartitionExec: partitioning=Hash([alias1@0], 4), input_partitions=1
481+
07)------------AggregateExec: mode=Partial, gby=[x@1 as alias1], aggr=[sum(t_decimal_distinct.d) as alias2]
482+
08)--------------DataSourceExec: partitions=1, partition_sizes=[1]
483+
484+
statement ok
485+
DROP TABLE t_decimal_distinct;

0 commit comments

Comments
 (0)