Skip to content

Commit 246a397

Browse files
authored
fix(duckdb): handle table filters in aggregate pushdown (#11071)
`TryReplaceAggregate` rewrites the scan's `column_ids` to one column per aggregate but keeps its table filters, which DuckDB keys by position in `column_ids`. Over files partitioned by `region`, `SELECT sum(v) ... WHERE region = 'US-East'` fails with `INTERNAL Error: Attempted to access index 1 within vector of size 1`. With two aggregates the filter lands on `v`: over files partitioned by `y`, `SELECT count(*), max(v) ... WHERE y = 2024` returns `0 NULL` instead of `2 3`. The rewrite now: - declines table filters on hive, `filename` and `file_index` columns, which DuckDB evaluates per file by `column_ids`; - converts the other table filters to Vortex filters, declines if one doesn't convert, and clears them; - reads storage indexes from `column_ids` before the resize. ## Tests On `develop`, the new hive, `filename`, `file_index` and `struct_extract_at` queries fail or return wrong results. `EXPLAIN` cases check that file-column and `file_row_number` filters keep the aggregate in the scan. Only the `struct_extract_at` query reaches the conversion, and declines; with `pushdown_complex_filter` off, `v > 1` and `i > 1` convert and keep the aggregate. The `vortex-duckdb` tests, clippy, clang-format and the other SLTs pass; as on the previous head, the TPC-H and TPC-DS runs stop at the `q11` and `q41` plans, so later plans didn't run. ClickBench and the spatial e2e tests didn't run. ## AI assistance Written with agentic AI assistance. --------- Signed-off-by: jackylee-ch <[email protected]>
1 parent 7d00329 commit 246a397

7 files changed

Lines changed: 165 additions & 11 deletions

File tree

‎vortex-duckdb/cpp/aggregate_fn_pushdown.cpp‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -84,8 +84,11 @@ LogicalOperatorPtr TryReplaceAggregate(ClientContext &context,
8484
if (!aggregate_pushdown(context, {*get, input})) {
8585
return op;
8686
}
87+
// Vortex now evaluates the table filters
88+
get->table_filters.ClearFilters();
8789

8890
// GET now returns one column per aggregate. Expand existing columns
91+
const vector<ColumnIndex> scan_column_ids = get->GetColumnIds();
8992
auto &column_ids = get->GetMutableColumnIds();
9093
get->types.resize(aggregates_len);
9194
get->returned_types.resize(aggregates_len);
@@ -98,7 +101,7 @@ LogicalOperatorPtr TryReplaceAggregate(ClientContext &context,
98101
if (column_index == COUNT_STAR_PROJ_IDX) {
99102
names[i] = "count_star()";
100103
} else {
101-
const idx_t storage_index = get->GetColumnIds()[column_index].GetPrimaryIndex();
104+
const idx_t storage_index = scan_column_ids[column_index].GetPrimaryIndex();
102105
names[i] = get->names[storage_index];
103106
}
104107
get->types[i] = expr.GetReturnType();

‎vortex-duckdb/cpp/include/table_function.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,8 @@ duckdb_state duckdb_vx_register_version_function(duckdb_database ffi_db, const c
104104
typedef struct duckdb_vx_agg_input_ *duckdb_vx_agg_input;
105105
idx_t duckdb_vx_aggregate_len(duckdb_vx_agg_input ffi);
106106
duckdb_vx_expr duckdb_vx_aggregate_at(duckdb_vx_agg_input ffi, idx_t index, idx_t *proj_idx);
107+
duckdb_vx_table_filter_set duckdb_vx_aggregate_table_filters(duckdb_vx_agg_input ffi);
108+
idx_t duckdb_vx_aggregate_column_id(duckdb_vx_agg_input ffi, idx_t scan_index);
107109

108110
#ifdef __cplusplus
109111
}

‎vortex-duckdb/cpp/table_function.cpp‎

Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -53,13 +53,24 @@ idx_t duckdb_vx_aggregate_len(duckdb_vx_agg_input ffi_input) {
5353
return reinterpret_cast<const TableFunctionUngroupedAggregateInput *>(ffi_input)->projections.size();
5454
}
5555

56+
idx_t duckdb_vx_aggregate_column_id(duckdb_vx_agg_input ffi_input, idx_t scan_index) {
57+
const auto &input = *reinterpret_cast<const TableFunctionUngroupedAggregateInput *>(ffi_input);
58+
return input.get.GetColumnIds()[scan_index].GetPrimaryIndex();
59+
}
60+
5661
duckdb_vx_expr duckdb_vx_aggregate_at(duckdb_vx_agg_input ffi_input, idx_t i, idx_t *proj_idx) {
5762
const auto &input = *reinterpret_cast<const TableFunctionUngroupedAggregateInput *>(ffi_input);
5863
const auto &[scan_index, expr] = input.projections[i];
59-
*proj_idx = scan_index == COUNT_STAR_PROJ_IDX ? scan_index
60-
: input.get.GetColumnIds()[scan_index].GetPrimaryIndex();
64+
*proj_idx =
65+
scan_index == COUNT_STAR_PROJ_IDX ? scan_index : duckdb_vx_aggregate_column_id(ffi_input, scan_index);
6166
return get_ffi_expr(expr);
6267
}
68+
69+
duckdb_vx_table_filter_set duckdb_vx_aggregate_table_filters(duckdb_vx_agg_input ffi_input) {
70+
const auto &input = *reinterpret_cast<const TableFunctionUngroupedAggregateInput *>(ffi_input);
71+
return reinterpret_cast<duckdb_vx_table_filter_set>(
72+
const_cast<TableFilterSet *>(&input.get.table_filters));
73+
}
6374
}
6475

6576
bool aggregate_pushdown(ClientContext &, const TableFunctionUngroupedAggregateInput &input) {
@@ -76,6 +87,18 @@ bool aggregate_pushdown(ClientContext &, const TableFunctionUngroupedAggregateIn
7687
return false;
7788
}
7889
}
90+
// ClearFilters would drop filters Vortex doesn't see
91+
if (input.get.table_filters.HasMultiColumnFilters()) {
92+
return false;
93+
}
94+
// DuckDB evaluates hive and filename filters per file by column_ids, which
95+
// the rewrite replaces. Hive columns may override file columns
96+
for (const auto &entry : input.get.table_filters) {
97+
const idx_t primary_index = input.get.GetColumnIds()[entry.GetIndex()].GetPrimaryIndex();
98+
if (SkipMultiFileReaderColumn(primary_index, filename_idx, hive_indices)) {
99+
return false;
100+
}
101+
}
79102

80103
void *const ffi_bind = get_ffi_bind(input.get.bind_data.get());
81104
duckdb_vx_error error_out = nullptr;

‎vortex-duckdb/src/duckdb/aggregate_pushdown.rs‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,8 @@ use num_traits::AsPrimitive;
66
use crate::cpp;
77
use crate::duckdb::Expression;
88
use crate::duckdb::ExpressionRef;
9+
use crate::duckdb::TableFilterSet;
10+
use crate::duckdb::TableFilterSetRef;
911
use crate::lifetime_wrapper;
1012

1113
lifetime_wrapper!(
@@ -42,4 +44,14 @@ impl AggregatePushdownInputRef {
4244
projection_id,
4345
}
4446
}
47+
48+
/// Table filters of the scan, keyed by scan column index
49+
pub fn table_filters(&self) -> &TableFilterSetRef {
50+
unsafe { TableFilterSet::borrow(cpp::duckdb_vx_aggregate_table_filters(self.as_ptr())) }
51+
}
52+
53+
/// Storage index of the scan column
54+
pub fn column_id(&self, scan_index: u64) -> u64 {
55+
unsafe { cpp::duckdb_vx_aggregate_column_id(self.as_ptr(), scan_index) }
56+
}
4557
}

‎vortex-duckdb/src/projection.rs‎

Lines changed: 13 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ use crate::convert::try_from_virtual_column_filter;
2828
use crate::duckdb::ExpressionClass;
2929
use crate::duckdb::LogicalType;
3030
use crate::duckdb::TableFilterClass;
31+
use crate::duckdb::TableFilterRef;
3132
use crate::duckdb::TableFilterSetRef;
3233
use crate::table_function::ColumnAggregate;
3334

@@ -201,6 +202,17 @@ fn push_filter_expr(filter_exprs: &mut Vec<Expression>, expr: &Expression) {
201202
}
202203
}
203204

205+
pub(crate) fn is_optional_table_filter(filter: &TableFilterRef) -> bool {
206+
match filter.as_class() {
207+
TableFilterClass::Optional(_) => true,
208+
TableFilterClass::ExpressionRef(expr) => match expr.as_class() {
209+
Some(ExpressionClass::BoundFunction(bfn)) => bfn.optional().is_some(),
210+
_ => false,
211+
},
212+
_ => false,
213+
}
214+
}
215+
204216
impl Filter {
205217
/// Creates a table filter expression, row selection, and row range from the table filter set,
206218
/// column metadata, additional filter expressions, and the top-level DType.
@@ -219,14 +231,7 @@ impl Filter {
219231
let idx_u: usize = idx.as_();
220232
!is_virtual_column(column_ids[idx_u])
221233
}) {
222-
has_non_optional_filter |= match ex.as_class() {
223-
TableFilterClass::Optional(_) => false,
224-
TableFilterClass::ExpressionRef(expr) => match expr.as_class() {
225-
Some(ExpressionClass::BoundFunction(bfn)) => bfn.optional().is_none(),
226-
_ => true,
227-
},
228-
_ => true,
229-
};
234+
has_non_optional_filter |= !is_optional_table_filter(ex);
230235

231236
let idx_u: usize = idx.as_();
232237
let col_idx: usize = column_ids[idx_u].as_();

‎vortex-duckdb/src/table_function.rs‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ use vortex::error::vortex_ensure;
3636
use vortex::error::vortex_err;
3737
use vortex::expr::BoundExpression;
3838
use vortex::expr::Expression;
39+
use vortex::expr::col;
3940
use vortex::expr::proto::ExprSerializeProtoExt;
4041
#[cfg(unix)]
4142
use vortex::metrics::tracing::get_global_labels;
@@ -49,6 +50,7 @@ use crate::convert::PushedAggregate;
4950
use crate::convert::try_from_bound_expression;
5051
use crate::convert::try_from_projection_aggregate;
5152
use crate::convert::try_from_projection_expression;
53+
use crate::convert::try_from_table_filter;
5254
use crate::cpp::DUCKDB_TYPE;
5355
use crate::duckdb::AggregateExpression;
5456
use crate::duckdb::AggregatePushdownInputRef;
@@ -66,6 +68,7 @@ use crate::projection::DuckdbField;
6668
use crate::projection::Filter;
6769
use crate::projection::Projection;
6870
use crate::projection::ProjectionInput;
71+
use crate::projection::is_optional_table_filter;
6972

7073
// Duckdb has two state machines for an extension. The outer one is the table
7174
// function state machine which calls the file reader state machine.
@@ -606,10 +609,40 @@ pub fn pushdown_projection_aggregates(
606609
if !has_non_count_star {
607610
return Ok(false);
608611
}
612+
// TryReplaceAggregate clears table filters, so Vortex evaluates them
613+
let Some((filters, has_non_optional_filter)) = aggregate_table_filters(bind_data, input) else {
614+
return Ok(false);
615+
};
616+
if has_non_optional_filter {
617+
bind_data
618+
.has_non_optional_filter
619+
.store(true, Ordering::Relaxed);
620+
}
621+
bind_data.filters.extend(filters);
609622
bind_data.aggregates = aggregates;
610623
Ok(true)
611624
}
612625

626+
// None if a filter is on a column absent from the files or doesn't convert
627+
fn aggregate_table_filters(
628+
bind_data: &BindState,
629+
input: &AggregatePushdownInputRef,
630+
) -> Option<(Vec<Expression>, bool)> {
631+
let mut filters = Vec::new();
632+
let mut has_non_optional_filter = false;
633+
for (scan_index, filter) in input.table_filters() {
634+
let column_id: usize = input.column_id(scan_index).as_();
635+
let field = bind_data.columns.get(column_id)?;
636+
let column = col(field.name.as_str());
637+
let expr = try_from_table_filter(filter, &column, &bind_data.dtype)
638+
.ok()
639+
.flatten()?;
640+
has_non_optional_filter |= !is_optional_table_filter(filter);
641+
filters.push(expr);
642+
}
643+
Some((filters, has_non_optional_filter))
644+
}
645+
613646
fn try_push_projection_aggregate(
614647
bind_data: &BindState,
615648
aggregate: AggregateExpression<'_>,

‎vortex-sqllogictest/slt/duckdb/agg_corner_filter.slt‎

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,3 +23,79 @@ query IIIIR
2323
SELECT count(*), count(i), sum(i), max(j), avg(j) FROM '${WORK_DIR}/aggregate-with-filter.vortex' WHERE i > 1;
2424
----
2525
2 2 5 30 25
26+
27+
# Filters on hive partition and filename columns
28+
statement ok
29+
COPY (SELECT * FROM (VALUES ('EU-West', 1), ('US-East', 2), ('EU-West', 3)) t(region, v))
30+
TO '${WORK_DIR}/aggregate-with-filter-hive' (FORMAT vortex, PARTITION_BY (region));
31+
32+
query I
33+
SELECT sum(v) FROM '${WORK_DIR}/aggregate-with-filter-hive/**/*.vortex' WHERE region = 'US-East';
34+
----
35+
2
36+
37+
query I
38+
SELECT sum(v) FROM read_vortex('${WORK_DIR}/aggregate-with-filter-hive/**/*.vortex', filename = true)
39+
WHERE filename LIKE '%US-East%';
40+
----
41+
2
42+
43+
statement ok
44+
COPY (SELECT * FROM (VALUES (2024, 1::BIGINT), (2025, 2::BIGINT), (2024, 3::BIGINT)) t(y, v))
45+
TO '${WORK_DIR}/aggregate-with-filter-year' (FORMAT vortex, PARTITION_BY (y));
46+
47+
query II
48+
SELECT count(*), max(v) FROM '${WORK_DIR}/aggregate-with-filter-year/**/*.vortex' WHERE y = 2024;
49+
----
50+
2 3
51+
52+
# A filter on a file column keeps the aggregate in the scan
53+
query TT
54+
EXPLAIN (FORMAT json) SELECT sum(v), max(v) FROM '${WORK_DIR}/aggregate-with-filter-hive/**/*.vortex' WHERE v > 1;
55+
----
56+
<!REGEX>:.*UNGROUPED_AGGREGATE.*
57+
58+
query II
59+
SELECT sum(v), max(v) FROM '${WORK_DIR}/aggregate-with-filter-hive/**/*.vortex' WHERE v > 1;
60+
----
61+
5 3
62+
63+
query TT
64+
EXPLAIN (FORMAT json) SELECT sum(i), max(j) FROM '${WORK_DIR}/aggregate-with-filter.vortex' WHERE i > 1;
65+
----
66+
<!REGEX>:.*UNGROUPED_AGGREGATE.*
67+
68+
# Filters on file_index and file_row_number
69+
statement ok
70+
COPY (SELECT * FROM (VALUES (1, 10), (2, 20)) t(k, v)) TO '${WORK_DIR}/aggregate-with-filter-0.vortex';
71+
72+
statement ok
73+
COPY (SELECT * FROM (VALUES (3, 30), (4, 40)) t(k, v)) TO '${WORK_DIR}/aggregate-with-filter-1.vortex';
74+
75+
query I
76+
SELECT sum(v) FROM '${WORK_DIR}/aggregate-with-filter-?.vortex' WHERE file_index = 0;
77+
----
78+
30
79+
80+
query I
81+
SELECT sum(v) FROM read_vortex('${WORK_DIR}/aggregate-with-filter-?.vortex') WHERE file_row_number = 0;
82+
----
83+
40
84+
85+
query TT
86+
EXPLAIN (FORMAT json) SELECT sum(v) FROM read_vortex('${WORK_DIR}/aggregate-with-filter-?.vortex') WHERE file_row_number = 0;
87+
----
88+
<!REGEX>:.*UNGROUPED_AGGREGATE.*
89+
90+
query I
91+
SELECT sum(v) FROM '${WORK_DIR}/aggregate-with-filter-?.vortex' WHERE filename LIKE '%filter-1%';
92+
----
93+
70
94+
95+
# Table filter that Vortex can't convert
96+
statement ok
97+
COPY (SELECT * FROM (VALUES (1, {'a': 3}), (2, {'a': 4}), (5, {'a': 3})) t(v, s))
98+
TO '${WORK_DIR}/aggregate-with-filter-struct.vortex';
99+
100+
statement error struct_extract_at is not supported
101+
SELECT sum(v) FROM '${WORK_DIR}/aggregate-with-filter-struct.vortex' WHERE struct_extract_at(s, 1) = 3;

0 commit comments

Comments
 (0)