Skip to content

Commit 0dab3ae

Browse files
authored
fix(query): preserve IGNORE NULLS semantics in distributed queries (#9601)
* fix(query): skip LastRow pushdown for IGNORE NULLS Signed-off-by: discord9 <[email protected]> * fix(query): keep IGNORE NULLS aggregates above remote scans Signed-off-by: discord9 <[email protected]> * test(query): record IGNORE NULLS results before and after flush Signed-off-by: discord9 <[email protected]> * test(query): share IGNORE NULLS coverage across deployments Signed-off-by: discord9 <[email protected]> * test(query): refresh last-value plans after rebase Signed-off-by: discord9 <[email protected]> --------- Signed-off-by: discord9 <[email protected]>
1 parent df8f684 commit 0dab3ae

7 files changed

Lines changed: 395 additions & 6 deletions

File tree

‎src/query/src/dist_plan/analyzer/test.rs‎

Lines changed: 70 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ use datafusion::functions_nested::expr_fn::make_array;
3232
use datafusion::prelude::SessionContext;
3333
use datafusion_common::tree_node::TreeNodeRecursion;
3434
use datafusion_common::{ExprSchema, JoinType, ScalarValue, TableReference};
35-
use datafusion_expr::expr::{Exists, ScalarFunction};
35+
use datafusion_expr::expr::{Exists, NullTreatment, ScalarFunction};
3636
use datafusion_expr::utils::split_conjunction;
3737
use datafusion_expr::{
3838
AggregateUDF, Expr, ExprSchemable as _, Extension, LogicalPlanBuilder, Operator, Subquery,
@@ -2484,6 +2484,75 @@ fn date_bin_ts_group_by() {
24842484
assert_eq!(expected, result.to_string());
24852485
}
24862486

2487+
#[test]
2488+
fn ignore_nulls_aggregate_stays_on_frontend() {
2489+
use datafusion::functions_aggregate::first_last::last_value;
2490+
2491+
let table = TestTable::table_with_name(0, "t".to_string());
2492+
let source = Arc::new(DefaultTableSource::new(Arc::new(
2493+
DfTableProviderAdapter::new(table),
2494+
)));
2495+
let input = LogicalPlanBuilder::scan_with_filters("t", source, None, vec![])
2496+
.unwrap()
2497+
.build()
2498+
.unwrap();
2499+
for (group_by, null_treatment) in [
2500+
(
2501+
vec![col("pk1"), col("pk2")],
2502+
Some(NullTreatment::IgnoreNulls),
2503+
),
2504+
(vec![col("number")], Some(NullTreatment::IgnoreNulls)),
2505+
(vec![col("pk1"), col("pk2")], None),
2506+
(
2507+
vec![col("pk1"), col("pk2")],
2508+
Some(NullTreatment::RespectNulls),
2509+
),
2510+
] {
2511+
let mut ignored = last_value(col("number"), vec![]);
2512+
let Expr::AggregateFunction(func) = &mut ignored else {
2513+
panic!("expected aggregate expression");
2514+
};
2515+
func.params.null_treatment = null_treatment;
2516+
let aggregate_exprs = if null_treatment == Some(NullTreatment::IgnoreNulls) {
2517+
let mut ordinary = last_value(col("number"), vec![]);
2518+
let Expr::AggregateFunction(func) = &mut ordinary else {
2519+
panic!("expected aggregate expression");
2520+
};
2521+
func.params.null_treatment = None;
2522+
vec![ordinary.alias("ordinary"), ignored.alias("ignored")]
2523+
} else {
2524+
vec![ignored]
2525+
};
2526+
let plan = LogicalPlanBuilder::from(input.clone())
2527+
.aggregate(group_by, aggregate_exprs)
2528+
.unwrap()
2529+
.build()
2530+
.unwrap();
2531+
let analyzed = DistPlannerAnalyzer {}
2532+
.analyze(plan.clone(), &ConfigOptions::default())
2533+
.unwrap();
2534+
if null_treatment == Some(NullTreatment::IgnoreNulls) {
2535+
let LogicalPlan::Aggregate(aggregate) = &analyzed else {
2536+
panic!("IGNORE NULLS aggregate must remain on the frontend: {analyzed}");
2537+
};
2538+
let remote_input = find_merge_scan(&analyzed).unwrap().input();
2539+
assert!(
2540+
!matches!(remote_input, LogicalPlan::Aggregate(_)),
2541+
"remote input contains aggregate: {remote_input}"
2542+
);
2543+
let Some(func) =
2544+
common_function::aggrs::aggr_wrapper::get_aggr_func(&aggregate.aggr_expr[1])
2545+
else {
2546+
unreachable!();
2547+
};
2548+
assert_eq!(Some(NullTreatment::IgnoreNulls), func.params.null_treatment);
2549+
} else {
2550+
let remote_input = find_merge_scan(&analyzed).unwrap().input();
2551+
assert!(matches!(remote_input, LogicalPlan::Aggregate(_)));
2552+
}
2553+
}
2554+
}
2555+
24872556
#[test]
24882557
fn test_last_value_order_by() {
24892558
init_default_ut_logging();

‎src/query/src/dist_plan/commutativity.rs‎

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,10 +15,13 @@
1515
use std::collections::HashSet;
1616
use std::sync::Arc;
1717

18-
use common_function::aggrs::aggr_wrapper::{StateMergeHelper, is_all_aggr_exprs_steppable};
18+
use common_function::aggrs::aggr_wrapper::{
19+
StateMergeHelper, get_aggr_func, is_all_aggr_exprs_steppable,
20+
};
1921
use common_telemetry::debug;
2022
use datafusion::error::Result as DfResult;
2123
use datafusion_common::tree_node::{TreeNode, TreeNodeRecursion};
24+
use datafusion_expr::expr::NullTreatment;
2225
use datafusion_expr::{Expr, LogicalPlan, UserDefinedLogicalNode};
2326
use promql::extension_plan::{
2427
EmptyMetric, InstantManipulate, RangeManipulate, SeriesDivide, SeriesNormalize,
@@ -116,6 +119,14 @@ impl Categorizer {
116119
LogicalPlan::Filter(filter) => Self::check_expr(&filter.predicate),
117120
LogicalPlan::Window(_) => Commutativity::Unimplemented,
118121
LogicalPlan::Aggregate(aggr) => {
122+
// The pinned Substrait codec drops IGNORE NULLS, so keep these aggregates on the frontend.
123+
if aggr.aggr_expr.iter().any(|expr| {
124+
get_aggr_func(expr).is_some_and(|func| {
125+
func.params.null_treatment == Some(NullTreatment::IgnoreNulls)
126+
})
127+
}) {
128+
return Ok(Commutativity::NonCommutative);
129+
}
119130
// The state/merge split maps each group expression to one output column,
120131
// which doesn't hold for grouping sets.
121132
let has_grouping_set = aggr

‎src/query/src/optimizer/scan_hint.rs‎

Lines changed: 50 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ use common_recordbatch::filter::SimpleFilterEvaluator;
2222
use datafusion::datasource::DefaultTableSource;
2323
use datafusion_common::tree_node::{Transformed, TreeNodeRewriter};
2424
use datafusion_common::{Column, Result};
25-
use datafusion_expr::expr::Sort;
25+
use datafusion_expr::expr::{NullTreatment, Sort};
2626
use datafusion_expr::{Expr, LogicalPlan, utils};
2727
use datafusion_optimizer::{OptimizerConfig, OptimizerRule};
2828
use datatypes::arrow::datatypes::{DataType, TimeUnit as ArrowTimeUnit};
@@ -413,6 +413,8 @@ impl ScanHintRewriter {
413413
&& func.func.name() != aggr_state_func_name("last_value"))
414414
|| func.params.filter.is_some()
415415
|| func.params.distinct
416+
// A shared final-row selector would discard earlier nonnull values.
417+
|| func.params.null_treatment == Some(NullTreatment::IgnoreNulls)
416418
{
417419
return None;
418420
}
@@ -455,7 +457,7 @@ mod test {
455457
use datafusion::prelude::JoinType;
456458
use datafusion_common::tree_node::TreeNodeRecursion;
457459
use datafusion_expr::expr::{
458-
AggregateFunction, AggregateFunctionParams, Cast, WindowFunctionParams,
460+
AggregateFunction, AggregateFunctionParams, Cast, NullTreatment, WindowFunctionParams,
459461
};
460462
use datafusion_expr::expr_fn::scalar_subquery;
461463
use datafusion_expr::{Extension, LogicalPlan, LogicalPlanBuilder, col, lit};
@@ -561,6 +563,13 @@ mod test {
561563
}
562564

563565
fn last_value_aggregate(input: LogicalPlan) -> LogicalPlan {
566+
last_value_aggregate_with_null_treatments(input, [None, None])
567+
}
568+
569+
fn last_value_aggregate_with_null_treatments(
570+
input: LogicalPlan,
571+
null_treatments: [Option<NullTreatment>; 2],
572+
) -> LogicalPlan {
564573
let aggregate = LogicalPlanBuilder::from(input)
565574
.aggregate(
566575
vec![col("k0")],
@@ -576,7 +585,7 @@ mod test {
576585
asc: true,
577586
nulls_first: true,
578587
}],
579-
null_treatment: None,
588+
null_treatment: null_treatments[0],
580589
},
581590
}),
582591
Expr::AggregateFunction(AggregateFunction {
@@ -590,7 +599,7 @@ mod test {
590599
asc: true,
591600
nulls_first: true,
592601
}],
593-
null_treatment: None,
602+
null_treatment: null_treatments[1],
594603
},
595604
}),
596605
],
@@ -901,6 +910,43 @@ mod test {
901910
}
902911
}
903912

913+
#[test]
914+
fn last_value_null_treatment_controls_selector() {
915+
let provider = Arc::new(mock_table_provider(RegionId::new(1, 1)));
916+
let context = OptimizerContext::default();
917+
918+
for treatment in [None, Some(NullTreatment::RespectNulls)] {
919+
let rewritten = ScanHintRule
920+
.rewrite(
921+
last_value_aggregate_with_null_treatments(
922+
scan_plan(provider.clone(), "t"),
923+
[treatment, treatment],
924+
),
925+
&context,
926+
)
927+
.unwrap()
928+
.data;
929+
assert_eq!(
930+
scan_requests(&rewritten)[0].series_row_selector,
931+
Some(TimeSeriesRowSelector::LastRow { after_merge: false })
932+
);
933+
}
934+
935+
// One IGNORE NULLS member prevents the shared scan selector for every
936+
// aggregate in the group, including the member that respects nulls.
937+
let rewritten = ScanHintRule
938+
.rewrite(
939+
last_value_aggregate_with_null_treatments(
940+
scan_plan(provider, "t"),
941+
[None, Some(NullTreatment::IgnoreNulls)],
942+
),
943+
&context,
944+
)
945+
.unwrap()
946+
.data;
947+
assert_eq!(scan_requests(&rewritten)[0].series_row_selector, None);
948+
}
949+
904950
#[test]
905951
fn single_evaluation_last_value_aggregate_keeps_legacy_selector() {
906952
let provider = Arc::new(mock_table_provider(RegionId::new(1, 1)));
Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
create table last_value_ignore_nulls (
2+
ts timestamp time index,
3+
host string primary key,
4+
v1 double,
5+
v2 double,
6+
);
7+
8+
Affected Rows: 0
9+
10+
insert into last_value_ignore_nulls values
11+
(1000, 'a', 42.0, null),
12+
(2000, 'a', null, 20.0),
13+
(1000, 'b', null, null),
14+
(2000, 'b', null, null);
15+
16+
Affected Rows: 4
17+
18+
select
19+
host,
20+
last_value(v1 order by ts) ignore nulls as v1_ignore,
21+
last_value(v2 order by ts) ignore nulls as v2_ignore,
22+
last_value(v1 order by ts) as v1_default,
23+
last_value(v1 order by ts) respect nulls as v1_respect
24+
from last_value_ignore_nulls
25+
group by host
26+
order by host;
27+
28+
+------+-----------+-----------+------------+------------+
29+
| host | v1_ignore | v2_ignore | v1_default | v1_respect |
30+
+------+-----------+-----------+------------+------------+
31+
| a | 42.0 | 20.0 | | |
32+
| b | | | | |
33+
+------+-----------+-----------+------------+------------+
34+
35+
-- Default and RESPECT NULLS return the same results in both modes.
36+
select
37+
host,
38+
last_value(v1 order by ts) as v1_default,
39+
last_value(v1 order by ts) respect nulls as v1_respect
40+
from last_value_ignore_nulls
41+
group by host
42+
order by host;
43+
44+
+------+------------+------------+
45+
| host | v1_default | v1_respect |
46+
+------+------------+------------+
47+
| a | | |
48+
| b | | |
49+
+------+------------+------------+
50+
51+
-- The IGNORE NULLS member rejects the shared selector, including when it is second.
52+
-- SQLNESS REPLACE (-+) -
53+
-- SQLNESS REPLACE (\s\s+) _
54+
-- SQLNESS REPLACE (elapsed_compute.*) REDACTED
55+
-- SQLNESS REPLACE (peers.*) REDACTED
56+
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
57+
-- SQLNESS REPLACE (metrics.*) REDACTED
58+
-- SQLNESS REPLACE (partitioning.*) REDACTED
59+
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
60+
-- SQLNESS REPLACE \\?"(dyn_filters|time_range|file_summary|max_part_range)\\?":.* "$1":REDACTED
61+
explain analyze select
62+
last_value(v1 order by ts),
63+
last_value(v2 order by ts) ignore nulls
64+
from last_value_ignore_nulls
65+
group by host;
66+
67+
+-+-+-+
68+
| stage | node | plan_|
69+
+-+-+-+
70+
| 0_| 0_|_ProjectionExec: expr=[last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]@1 as last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST], last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]@2 as last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]] REDACTED
71+
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST], last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]] REDACTED
72+
|_|_|_RepartitionExec: REDACTED
73+
|_|_|_AggregateExec: mode=Partial, gby=[host@1 as host], aggr=[last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST], last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]] REDACTED
74+
|_|_|_RepartitionExec: REDACTED
75+
|_|_|_MergeScanExec: REDACTED
76+
|_|_|_|
77+
| 1_| 0_|_CooperativeExec REDACTED
78+
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "projection": ["ts", "host", "v1", "v2"], "time_range":REDACTED
79+
|_|_|_|
80+
|_|_| Total rows: 2_|
81+
+-+-+-+
82+
83+
admin flush_table('last_value_ignore_nulls');
84+
85+
+----------------------------------------------+
86+
| ADMIN flush_table('last_value_ignore_nulls') |
87+
+----------------------------------------------+
88+
| 0 |
89+
+----------------------------------------------+
90+
91+
select
92+
host,
93+
last_value(v1 order by ts) ignore nulls as v1_ignore,
94+
last_value(v2 order by ts) ignore nulls as v2_ignore,
95+
last_value(v1 order by ts) as v1_default,
96+
last_value(v1 order by ts) respect nulls as v1_respect
97+
from last_value_ignore_nulls
98+
group by host
99+
order by host;
100+
101+
+------+-----------+-----------+------------+------------+
102+
| host | v1_ignore | v2_ignore | v1_default | v1_respect |
103+
+------+-----------+-----------+------------+------------+
104+
| a | 42.0 | 20.0 | | |
105+
| b | | | | |
106+
+------+-----------+-----------+------------+------------+
107+
108+
-- SQLNESS REPLACE (-+) -
109+
-- SQLNESS REPLACE (\s\s+) _
110+
-- SQLNESS REPLACE (elapsed_compute.*) REDACTED
111+
-- SQLNESS REPLACE (peers.*) REDACTED
112+
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
113+
-- SQLNESS REPLACE (metrics.*) REDACTED
114+
-- SQLNESS REPLACE (partitioning.*) REDACTED
115+
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
116+
-- SQLNESS REPLACE \\?"(dyn_filters|time_range|file_summary|max_part_range)\\?":.* "$1":REDACTED
117+
explain analyze select
118+
last_value(v1 order by ts),
119+
last_value(v2 order by ts) ignore nulls
120+
from last_value_ignore_nulls
121+
group by host;
122+
123+
+-+-+-+
124+
| stage | node | plan_|
125+
+-+-+-+
126+
| 0_| 0_|_ProjectionExec: expr=[last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]@1 as last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST], last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]@2 as last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]] REDACTED
127+
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[host@0 as host], aggr=[last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST], last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]] REDACTED
128+
|_|_|_RepartitionExec: REDACTED
129+
|_|_|_AggregateExec: mode=Partial, gby=[host@1 as host], aggr=[last_value(last_value_ignore_nulls.v1) ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST], last_value(last_value_ignore_nulls.v2) IGNORE NULLS ORDER BY [last_value_ignore_nulls.ts ASC NULLS LAST]] REDACTED
130+
|_|_|_RepartitionExec: REDACTED
131+
|_|_|_MergeScanExec: REDACTED
132+
|_|_|_|
133+
| 1_| 0_|_CooperativeExec REDACTED
134+
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":0, "files":1, "file_ranges":1}, "projection": ["ts", "host", "v1", "v2"], "time_range":REDACTED
135+
|_|_|_|
136+
|_|_| Total rows: 2_|
137+
+-+-+-+
138+
139+
drop table last_value_ignore_nulls;
140+
141+
Affected Rows: 0
142+

0 commit comments

Comments
 (0)