Skip to content

Commit 95d7aee

Browse files
committed
Showed the applied dynamic filter and covered partitioned mode in SLT.
`EXPLAIN ANALYZE` pins the populated filter: IS NULL disjuncts when the build holds a NULL, none when it does not. Two files per side force real hash routing for the partitioned case.
1 parent 75125ad commit 95d7aee

1 file changed

Lines changed: 120 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/push_down_filter_parquet.slt‎

Lines changed: 120 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1086,13 +1086,133 @@ SELECT mnej_build.a, mnej_build.b, mnej_probe.a, mnej_probe.b FROM mnej_build JO
10861086
1 10 1 10
10871087
2 NULL 2 NULL
10881088

1089+
statement ok
1090+
set datafusion.explain.analyze_categories = 'rows';
1091+
1092+
# After execution the populated filter shows the applied predicate: an IS NULL disjunct
1093+
# per key ahead of the build-side membership check, because the build holds a NULL.
1094+
query TT
1095+
EXPLAIN ANALYZE SELECT mnej_build.a, mnej_build.b, mnej_probe.a, mnej_probe.b FROM mnej_build JOIN mnej_probe ON (mnej_build.a IS NOT DISTINCT FROM mnej_probe.a) AND (mnej_build.b IS NOT DISTINCT FROM mnej_probe.b)
1096+
----
1097+
Plan with Metrics
1098+
01)HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(a@0, a@0), (b@1, b@1)], NullsEqual: true, metrics=[output_rows=2, output_batches=1, array_map_created_count=0, build_input_batches=1, build_input_rows=2, input_batches=1, input_rows=3, avg_fanout=100% (2/2), probe_hit_rate=66.67% (2/3)]
1099+
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/mnej_build.parquet]]}, projection=[a, b], file_type=parquet, metrics=[output_rows=2, output_batches=1, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=1 total → 1 matched, row_groups_pruned_bloom_filter=1 total → 1 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, file_open_errors=0, file_scan_errors=0, files_opened=1, files_processed=1, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, scan_efficiency_ratio=16.42% (133/810)]
1100+
03)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/mnej_probe.parquet]]}, projection=[a, b], file_type=parquet, predicate=DynamicFilter [ a@0 IS NULL OR b@1 IS NULL OR a@0 >= 1 AND a@0 <= 2 AND b@1 >= 10 AND b@1 <= 10 AND struct(a@0, b@1) IN (SET) ([{c0:1,c1:10}, {c0:2,c1:}]) ], dynamic_rg_pruning=eligible, pruning_predicate=a_null_count@0 > 0 OR b_null_count@1 > 0 OR a_null_count@0 != row_count@3 AND a_max@2 >= 1 AND a_null_count@0 != row_count@3 AND a_min@4 <= 2 AND b_null_count@1 != row_count@3 AND b_max@5 >= 10 AND b_null_count@1 != row_count@3 AND b_min@6 <= 10, required_guarantees=[], metrics=[output_rows=3, output_batches=1, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=1 total → 1 matched, row_groups_pruned_bloom_filter=1 total → 1 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, file_open_errors=0, file_scan_errors=0, files_opened=1, files_processed=1, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=3, pushdown_rows_pruned=0, predicate_cache_inner_records=6, predicate_cache_records=6, scan_efficiency_ratio=18.16% (148/815)]
1101+
1102+
statement ok
1103+
reset datafusion.explain.analyze_categories;
1104+
10891105
statement ok
10901106
drop table mnej_build;
10911107

10921108
statement ok
10931109
drop table mnej_probe;
10941110

10951111

1112+
# A NULL-free build has nothing for a probe NULL to null-match, so the pushed filter
1113+
# skips the IS NULL widening and keeps its full selectivity.
1114+
statement ok
1115+
COPY (SELECT * FROM (VALUES (11), (22)) v(id)) TO 'test_files/scratch/push_down_filter_parquet/nnb_build.parquet' STORED AS PARQUET;
1116+
1117+
statement ok
1118+
COPY (SELECT * FROM (VALUES (11), (33), (NULL)) v(id)) TO 'test_files/scratch/push_down_filter_parquet/nnb_probe.parquet' STORED AS PARQUET;
1119+
1120+
statement ok
1121+
CREATE EXTERNAL TABLE nnb_build STORED AS PARQUET LOCATION 'test_files/scratch/push_down_filter_parquet/nnb_build.parquet';
1122+
1123+
statement ok
1124+
CREATE EXTERNAL TABLE nnb_probe STORED AS PARQUET LOCATION 'test_files/scratch/push_down_filter_parquet/nnb_probe.parquet';
1125+
1126+
query II rowsort
1127+
SELECT nnb_build.id, nnb_probe.id FROM nnb_build JOIN nnb_probe ON nnb_build.id IS NOT DISTINCT FROM nnb_probe.id
1128+
----
1129+
11 11
1130+
1131+
statement ok
1132+
set datafusion.explain.analyze_categories = 'rows';
1133+
1134+
# No IS NULL disjunct in the populated filter: the probe NULL can be pruned safely.
1135+
query TT
1136+
EXPLAIN ANALYZE SELECT nnb_build.id, nnb_probe.id FROM nnb_build JOIN nnb_probe ON nnb_build.id IS NOT DISTINCT FROM nnb_probe.id
1137+
----
1138+
Plan with Metrics
1139+
01)HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(id@0, id@0)], NullsEqual: true, metrics=[output_rows=1, output_batches=1, array_map_created_count=1, build_input_batches=1, build_input_rows=2, input_batches=1, input_rows=1, avg_fanout=100% (1/1), probe_hit_rate=100% (1/1)]
1140+
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/nnb_build.parquet]]}, projection=[id], file_type=parquet, metrics=[output_rows=2, output_batches=1, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=1 total → 1 matched, row_groups_pruned_bloom_filter=1 total → 1 matched, page_index_pages_pruned=0 total → 0 matched, page_index_rows_pruned=0 total → 0 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, file_open_errors=0, file_scan_errors=0, files_opened=1, files_processed=1, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=0, pushdown_rows_pruned=0, predicate_cache_inner_records=0, predicate_cache_records=0, scan_efficiency_ratio=13.71% (68/496)]
1141+
03)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/nnb_probe.parquet]]}, projection=[id], file_type=parquet, predicate=DynamicFilter [ id@0 >= 11 AND id@0 <= 22 AND id@0 IN (SET) ([11, 22]) ], dynamic_rg_pruning=eligible, pruning_predicate=id_null_count@1 != row_count@2 AND id_max@0 >= 11 AND id_null_count@1 != row_count@2 AND id_min@3 <= 22 AND (id_null_count@1 != row_count@2 AND id_min@3 <= 11 AND 11 <= id_max@0 OR id_null_count@1 != row_count@2 AND id_min@3 <= 22 AND 22 <= id_max@0), required_guarantees=[id in (11, 22)], metrics=[output_rows=1, output_batches=1, files_ranges_pruned_statistics=1 total → 1 matched, row_groups_pruned_statistics=1 total → 1 matched, row_groups_pruned_bloom_filter=1 total → 1 matched, page_index_pages_pruned=1 total → 1 matched, page_index_rows_pruned=3 total → 3 matched, limit_pruned_row_groups=0 total → 0 matched, batches_split=0, file_open_errors=0, file_scan_errors=0, files_opened=1, files_processed=1, num_predicate_creation_errors=0, predicate_evaluation_errors=0, pushdown_rows_matched=1, pushdown_rows_pruned=2, predicate_cache_inner_records=3, predicate_cache_records=1, scan_efficiency_ratio=14.45% (74/512)]
1142+
1143+
statement ok
1144+
reset datafusion.explain.analyze_categories;
1145+
1146+
statement ok
1147+
drop table nnb_build;
1148+
1149+
statement ok
1150+
drop table nnb_probe;
1151+
1152+
1153+
# Partitioned mode: the per-partition CASE filter gets the same IS NULL widening, so a
1154+
# probe NULL routed to a pruning branch still reaches the join.
1155+
statement ok
1156+
set datafusion.execution.target_partitions = 4;
1157+
1158+
statement ok
1159+
set datafusion.optimizer.hash_join_single_partition_threshold = 0;
1160+
1161+
statement ok
1162+
set datafusion.optimizer.hash_join_single_partition_threshold_rows = 0;
1163+
1164+
# Two files per side so each scan starts with multiple partitions and the join
1165+
# runs real hash routing instead of collapsing to a single branch.
1166+
statement ok
1167+
COPY (SELECT * FROM (VALUES (11), (22)) v(id)) TO 'test_files/scratch/push_down_filter_parquet/pnej_probe/1.parquet' STORED AS PARQUET;
1168+
1169+
statement ok
1170+
COPY (SELECT * FROM (VALUES (33), (NULL)) v(id)) TO 'test_files/scratch/push_down_filter_parquet/pnej_probe/2.parquet' STORED AS PARQUET;
1171+
1172+
statement ok
1173+
COPY (SELECT * FROM (VALUES (11)) v(id)) TO 'test_files/scratch/push_down_filter_parquet/pnej_build/1.parquet' STORED AS PARQUET;
1174+
1175+
statement ok
1176+
COPY (SELECT * FROM (VALUES (NULL)) v(id)) TO 'test_files/scratch/push_down_filter_parquet/pnej_build/2.parquet' STORED AS PARQUET;
1177+
1178+
statement ok
1179+
CREATE EXTERNAL TABLE pnej_probe STORED AS PARQUET LOCATION 'test_files/scratch/push_down_filter_parquet/pnej_probe/';
1180+
1181+
statement ok
1182+
CREATE EXTERNAL TABLE pnej_build STORED AS PARQUET LOCATION 'test_files/scratch/push_down_filter_parquet/pnej_build/';
1183+
1184+
query TT
1185+
EXPLAIN SELECT pnej_build.id, pnej_probe.id FROM pnej_build JOIN pnej_probe ON pnej_build.id IS NOT DISTINCT FROM pnej_probe.id
1186+
----
1187+
physical_plan
1188+
01)HashJoinExec: mode=Partitioned, join_type=Inner, on=[(id@0, id@0)], NullsEqual: true
1189+
02)--RepartitionExec: partitioning=Hash([id@0], 4), input_partitions=2
1190+
03)----DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/pnej_build/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/pnej_build/2.parquet]]}, projection=[id], file_type=parquet
1191+
04)--RepartitionExec: partitioning=Hash([id@0], 4), input_partitions=2
1192+
05)----DataSourceExec: file_groups={2 groups: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/pnej_probe/1.parquet], [WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/push_down_filter_parquet/pnej_probe/2.parquet]]}, projection=[id], file_type=parquet, predicate=DynamicFilter [ empty ], dynamic_rg_pruning=eligible
1193+
1194+
query II rowsort
1195+
SELECT pnej_build.id, pnej_probe.id FROM pnej_build JOIN pnej_probe ON pnej_build.id IS NOT DISTINCT FROM pnej_probe.id
1196+
----
1197+
11 11
1198+
NULL NULL
1199+
1200+
statement ok
1201+
drop table pnej_build;
1202+
1203+
statement ok
1204+
drop table pnej_probe;
1205+
1206+
statement ok
1207+
set datafusion.execution.target_partitions = 4;
1208+
1209+
statement ok
1210+
RESET datafusion.optimizer.hash_join_single_partition_threshold;
1211+
1212+
statement ok
1213+
RESET datafusion.optimizer.hash_join_single_partition_threshold_rows;
1214+
1215+
10961216
########
10971217
# Regression test for build-NULL + emptied-probe interaction in null-aware LeftAnti joins.
10981218
#

0 commit comments

Comments
 (0)