@@ -1086,13 +1086,133 @@ SELECT mnej_build.a, mnej_build.b, mnej_probe.a, mnej_probe.b FROM mnej_build JO
108610861 10 1 10
108710872 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+
10891105statement ok
10901106drop table mnej_build;
10911107
10921108statement ok
10931109drop 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