Skip to content
Merged
Changes from 1 commit
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Next Next commit
Reproduce the issue
  • Loading branch information
liamzwbao committed Jul 4, 2025
commit 9ae839fddbb2d3d25e1e8ecb77506c42e7754d70
137 changes: 137 additions & 0 deletions datafusion/sqllogictest/test_files/parquet_filter_pushdown.slt
Original file line number Diff line number Diff line change
Expand Up @@ -113,6 +113,46 @@ physical_plan
02)--SortExec: expr=[a@0 ASC NULLS LAST], preserve_partitioning=[true]
03)----DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a], file_type=parquet, predicate=b@1 > 2, pruning_predicate=b_null_count@1 != row_count@2 AND b_max@0 > 2, required_guarantees=[]

query T
select a from t where b = 2 ORDER BY b;
----
bar

query T
select a from t_pushdown where b = 2 ORDER BY b;
----
bar

query TT
EXPLAIN select a from t where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t.a
02)--Sort: t.b ASC NULLS LAST
03)----Filter: t.b = Int32(2)
04)------TableScan: t projection=[a, b], partial_filters=[t.b = Int32(2)]
physical_plan
01)CoalescePartitionsExec
02)--ProjectionExec: expr=[a@0 as a]
03)----CoalesceBatchesExec: target_batch_size=8192
04)------FilterExec: b@1 = 2
05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=2
06)----------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

query TT
EXPLAIN select a from t_pushdown where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t_pushdown.a
02)--Sort: t_pushdown.b ASC NULLS LAST
03)----Filter: t_pushdown.b = Int32(2)
04)------TableScan: t_pushdown projection=[a, b], partial_filters=[t_pushdown.b = Int32(2)]
physical_plan
01)ProjectionExec: expr=[a@0 as a]
02)--SortPreservingMergeExec: [b@1 ASC NULLS LAST]
03)----SortExec: expr=[b@1 ASC NULLS LAST], preserve_partitioning=[true]
04)------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

# If we set the setting to `true` it override's the table's setting
statement ok
set datafusion.execution.parquet.pushdown_filters = true;
Expand Down Expand Up @@ -161,6 +201,44 @@ physical_plan
02)--SortExec: expr=[a@0 ASC NULLS LAST], preserve_partitioning=[true]
03)----DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a], file_type=parquet, predicate=b@1 > 2, pruning_predicate=b_null_count@1 != row_count@2 AND b_max@0 > 2, required_guarantees=[]

query T
select a from t where b = 2 ORDER BY b;
----
bar

query T
select a from t_pushdown where b = 2 ORDER BY b;
----
bar

query TT
EXPLAIN select a from t where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t.a
02)--Sort: t.b ASC NULLS LAST
03)----Filter: t.b = Int32(2)
04)------TableScan: t projection=[a, b], partial_filters=[t.b = Int32(2)]
physical_plan
01)ProjectionExec: expr=[a@0 as a]
02)--SortPreservingMergeExec: [b@1 ASC NULLS LAST]
03)----SortExec: expr=[b@1 ASC NULLS LAST], preserve_partitioning=[true]
04)------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

query TT
EXPLAIN select a from t_pushdown where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t_pushdown.a
02)--Sort: t_pushdown.b ASC NULLS LAST
03)----Filter: t_pushdown.b = Int32(2)
04)------TableScan: t_pushdown projection=[a, b], partial_filters=[t_pushdown.b = Int32(2)]
physical_plan
01)ProjectionExec: expr=[a@0 as a]
02)--SortPreservingMergeExec: [b@1 ASC NULLS LAST]
03)----SortExec: expr=[b@1 ASC NULLS LAST], preserve_partitioning=[true]
04)------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

# If we reset the default the table created without pushdown goes back to disabling it
statement ok
set datafusion.execution.parquet.pushdown_filters = false;
Expand Down Expand Up @@ -212,6 +290,46 @@ physical_plan
02)--SortExec: expr=[a@0 ASC NULLS LAST], preserve_partitioning=[true]
03)----DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a], file_type=parquet, predicate=b@1 > 2, pruning_predicate=b_null_count@1 != row_count@2 AND b_max@0 > 2, required_guarantees=[]

query T
select a from t where b = 2 ORDER BY b;
----
bar

query T
select a from t_pushdown where b = 2 ORDER BY b;
----
bar

query TT
EXPLAIN select a from t where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t.a
02)--Sort: t.b ASC NULLS LAST
03)----Filter: t.b = Int32(2)
04)------TableScan: t projection=[a, b], partial_filters=[t.b = Int32(2)]
physical_plan
01)CoalescePartitionsExec
02)--ProjectionExec: expr=[a@0 as a]
03)----CoalesceBatchesExec: target_batch_size=8192
04)------FilterExec: b@1 = 2
05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=2
06)----------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

query TT
EXPLAIN select a from t_pushdown where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t_pushdown.a
02)--Sort: t_pushdown.b ASC NULLS LAST
03)----Filter: t_pushdown.b = Int32(2)
04)------TableScan: t_pushdown projection=[a, b], partial_filters=[t_pushdown.b = Int32(2)]
physical_plan
01)ProjectionExec: expr=[a@0 as a]
02)--SortPreservingMergeExec: [b@1 ASC NULLS LAST]
03)----SortExec: expr=[b@1 ASC NULLS LAST], preserve_partitioning=[true]
04)------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

# When filter pushdown *is* enabled, ParquetExec can filter exactly,
# not just metadata, so we expect to see no FilterExec
query T
Expand Down Expand Up @@ -239,6 +357,25 @@ physical_plan
05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=2
06)----------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 > 2, pruning_predicate=b_null_count@1 != row_count@2 AND b_max@0 > 2, required_guarantees=[]

query T
select a from t_pushdown where b = 2 ORDER BY b;
----
bar

query TT
EXPLAIN select a from t_pushdown where b = 2 ORDER BY b;
----
logical_plan
01)Projection: t_pushdown.a
02)--Sort: t_pushdown.b ASC NULLS LAST
03)----Filter: t_pushdown.b = Int32(2)
04)------TableScan: t_pushdown projection=[a, b], partial_filters=[t_pushdown.b = Int32(2)]
physical_plan
01)ProjectionExec: expr=[a@0 as a]
02)--SortPreservingMergeExec: [b@1 ASC NULLS LAST]
03)----SortExec: expr=[b@1 ASC NULLS LAST], preserve_partitioning=[true]
04)------DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/parquet_filter_pushdown/parquet_table/2.parquet]]}, projection=[a, b], file_type=parquet, predicate=b@1 = 2, pruning_predicate=b_null_count@2 != row_count@3 AND b_min@0 <= 2 AND 2 <= b_max@1, required_guarantees=[b in (2)]

Comment on lines +352 to +368

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

@liamzwbao could you help me understand what I should be looking for in this output / what proves that this fix works / what would have been different before this fix?

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

The 1st commit reproduce the issue, and the 2nd commit reflect the fix.

Basically, the SortExec which matches the filter equivalence gets removed after the fix.

# also test querying on columns that are not in all the files
query T
select a from t_pushdown where b > 2 AND a IS NOT NULL order by a;
Expand Down