Use SQL null semantics for DuckDB IN and NOT IN pushdown by robert3005 · Pull Request #10054 · vortex-data/vortex

robert3005 · 2026-09-25T13:56:23Z

DuckDB membership pushdown used Vortex's default null rules, which can make a nonmatch in NOT IN (1, NULL) true. Use SQL null semantics so that result remains unknown and the row is excluded.

codspeed · 2026-09-25T14:03:43Z

Merging this PR will improve performance by 13.19%

⚠️

Unknown Walltime execution environment detected

Using the Walltime instrument on standard Hosted Runners will lead to inconsistent data.

For the most accurate results, we recommend using CodSpeed Macro Runners: bare-metal machines fine-tuned for performance measurement consistency.

⚠️

2 benchmarks measured no execution time

Nothing ran under measurement, usually because the compiler removed the code under test. These results are not comparable, so they count as unchanged.

Preventing compiler optimizations

bench_compare_primitive[(10000, 2)]

bench_compare_sliced_dict_primitive[(3333, 10000)]

⚠️

16 benchmarks spent significant time in system calls

System calls cannot be consistently instrumented, so they are not included in the measure, which understates the real cost. Please switch to the Walltime instrument to accurately measure system calls.

Measurement and system calls

take_indices[(10000, 4096)]

test_scan_filter[int_float]

test_scan_filter[int_str]

decompress[u16, (4000, 256)]

test_scan_filter[int_str_float]

test_scan[int_str]

take_varbin

density_sweep_dense_runs[0.001]

multipolygon_mixed_validity

ilike_contains

…and 6 more

⚠️

Different runtime environments detected

Some benchmarks with significant performance changes were compared across different runtime environments,
which may affect the accuracy of the results.

Open the report in CodSpeed to investigate

⚡ 3 improved benchmarks
✅ 2256 untouched benchmarks

Performance Changes

	Mode	Benchmark	`BASE`	`HEAD`	Efficiency
⚡	WallTime	`threshold_neon[64]`	5.2 µs	4.6 µs	+13.78%
⚡	WallTime	`threshold_neon[8]`	3.6 µs	3.1 µs	+13.13%
⚡	WallTime	`filter_neon[64]`	3.2 µs	2.8 µs	+12.68%

Tip

Curious why performance improved? Comment @codspeedbot explain why performance improved on this PR, or directly use the CodSpeed MCP with your agent.

_{Comparing rk/duckdb-list-contains (c97dc82) with develop (c128e99)}

Convert both IN table filters and bound membership expressions through in_list, so a null set element leaves nonmatches unknown. Decline nonconstant lists instead of promising conversion and failing later. Add scan-result and EXPLAIN regressions for nullable sets, views, large constant lists, and fallback for nonconstant list elements. Signed-off-by: Robert Kruszewski <[email protected]>

Signed-off-by: Robert Kruszewski <[email protected]>

DuckDB IN and NOT IN filters now convert to in_list, which shows opts=sql_null_semantics in EXPLAIN. Update the expected TPC-H, TPC-DS and ClickBench plans to match. The membership pushdown test searched EXPLAIN for "list_contains", but the scan shows "vortex.list.contains". Search for "sql_null_semantics" instead, because the EXPLAIN box does not split it across lines. Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

Before, any IN whose children could push was accepted, and conversion failed when a list value was not a constant. The previous fix only accepted constant lists, which pushed less than before. Accept the same IN expressions as before and convert all of them. Constant values go into one in_list. Each nonconstant value becomes an equality with the element, joined with Kleene OR, which keeps the SQL null semantics of IN and NOT IN. Remove a stale TODO on the IN table filter. Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

Revert the OR-of-equalities lowering for nonconstant IN values. in_list already gives SQL semantics for constant lists, and DuckDB keeps IN expressions with nonconstant values. Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

Bring back the conversion of IN values that are not constants to equalities joined with OR, and explain it. All constants still go into one in_list with SQL null semantics. Kleene OR keeps the SQL result of IN and NOT IN when values are null. Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

joseph-isaacs · 2026-10-15T11:26:49Z

remove this

Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

joseph-isaacs · 2026-10-15T11:28:48Z

            let dtype = scalars[0].dtype().clone();
-            let list_scalar = Scalar::list(Arc::new(dtype), scalars, Nullability::Nullable);
-            list_contains(lit(list_scalar), col.clone())
+            let list_scalar = Scalar::list(Arc::new(dtype), scalars, Nullability::NonNullable);
+            in_list(col.clone(), lit(list_scalar))


comment please

DuckDB's in_clause optimizer rewrites IN lists with nonconstant values to equalities, so the OR lowering only ran with that optimizer disabled. Remove it and keep IN pushdown for constant lists, which in_list converts with SQL null semantics. DuckDB keeps other IN lists. Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

robert3005 added this pull request to stack #10055 September 25, 2026 13:56

This was referenced Sep 25, 2026

Optimize list_contains with prepared constant sets #10052

Merged

Expose list membership with SQL null semantics in Java and Python #10053

Merged

robert3005 removed this pull request from stack #10055 September 25, 2026 14:05

robert3005 force-pushed the rk/list-contains-bindings branch from 2aaa1b6 to 1b63fd8 Compare September 25, 2026 14:06

robert3005 force-pushed the rk/duckdb-list-contains branch from 7f7f687 to 29fa2b0 Compare September 25, 2026 14:06

robert3005 added this pull request to stack #10058 September 25, 2026 14:06

robert3005 mentioned this pull request Sep 25, 2026

Add SQL null semantics to list_contains #10057

Merged

robert3005 removed this pull request from stack #10058 September 25, 2026 14:09

robert3005 force-pushed the rk/list-contains-bindings branch from 1b63fd8 to 3151b45 Compare September 25, 2026 14:09

robert3005 added this pull request to stack #10061 September 25, 2026 14:09

robert3005 force-pushed the rk/duckdb-list-contains branch from 29fa2b0 to adc15ee Compare September 25, 2026 14:09

robert3005 mentioned this pull request Sep 25, 2026

Add list_contains membership benchmarks #10060

Merged

robert3005 force-pushed the rk/duckdb-list-contains branch from adc15ee to 2535299 Compare September 25, 2026 15:53

robert3005 force-pushed the rk/list-contains-bindings branch from 3151b45 to b6ffdda Compare September 25, 2026 15:53

robert3005 force-pushed the rk/duckdb-list-contains branch from 2535299 to cafd7d2 Compare September 25, 2026 16:28

robert3005 force-pushed the rk/list-contains-bindings branch 2 times, most recently from ed99a18 to 0e3443a Compare September 25, 2026 17:10

robert3005 force-pushed the rk/duckdb-list-contains branch 2 times, most recently from 04bd414 to 459162a Compare September 25, 2026 17:26

robert3005 force-pushed the rk/list-contains-bindings branch 2 times, most recently from 2242462 to 828a8f7 Compare September 25, 2026 20:28

robert3005 force-pushed the rk/duckdb-list-contains branch 2 times, most recently from c0e6065 to 843d57c Compare September 28, 2026 20:43

robert3005 force-pushed the rk/list-contains-bindings branch from 828a8f7 to 3dd8f74 Compare September 28, 2026 20:43

robert3005 commented Sep 28, 2026

View reviewed changes

Comment thread vortex-duckdb/src/convert/expr.rs Resolved

robert3005 commented Sep 28, 2026

View reviewed changes

Comment thread vortex-duckdb/src/convert/table_filter.rs Resolved

robert3005 force-pushed the rk/duckdb-list-contains branch from 843d57c to e4b6598 Compare September 29, 2026 19:13

robert3005 force-pushed the rk/list-contains-bindings branch from 3dd8f74 to 2a51612 Compare September 29, 2026 19:13

jackylee-ch mentioned this pull request Oct 14, 2026

Support Spark floating point semantics in bound comparisons #10064

Open

robert3005 force-pushed the rk/duckdb-list-contains branch from 2a977a1 to 5b12a0f Compare October 14, 2026 17:32

robert3005 force-pushed the rk/list-contains-bindings branch from 446956a to 6801f5b Compare October 14, 2026 17:32

robert3005 force-pushed the rk/duckdb-list-contains branch 2 times, most recently from 757e4da to 461e8a7 Compare October 14, 2026 18:34

robert3005 force-pushed the rk/list-contains-bindings branch from 856b420 to 2bda1b5 Compare October 14, 2026 18:34

robert3005 removed this pull request from stack #10443 October 14, 2026 18:35

robert3005 changed the base branch from rk/list-contains-bindings to develop October 14, 2026 18:35

robert3005 and others added 4 commits October 15, 2026 11:16

Remove unused DuckDB conversion import

353eaf7

Signed-off-by: Robert Kruszewski <[email protected]>

Format DuckDB membership pushdown test

cd79386

Signed-off-by: Robert Kruszewski <[email protected]>

robert3005 force-pushed the rk/duckdb-list-contains branch from 461e8a7 to c31d440 Compare October 15, 2026 10:35

robert3005 added the changelog/fix A bug fix label Oct 15, 2026

robert3005 and others added 3 commits October 15, 2026 12:04

robert3005 marked this pull request as ready for review October 15, 2026 11:09

robert3005 requested a review from joseph-isaacs October 15, 2026 11:09

Shorten IN conversion comment

c07a503

Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

joseph-isaacs reviewed Oct 15, 2026

View reviewed changes

Comment thread vortex-duckdb/src/convert/expr.rs

joseph-isaacs Oct 15, 2026

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.

remove this

Explain when IN reaches Vortex with nonconstant values

dd647f6

Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

joseph-isaacs reviewed Oct 15, 2026

View reviewed changes

joseph-isaacs approved these changes Oct 15, 2026

View reviewed changes

robert3005 and others added 2 commits October 15, 2026 12:30

Explain non-nullable IN list scalars

c97dc82

Signed-off-by: Robert Kruszewski <[email protected]> Co-Authored-By: Claude Opus 5.5 <[email protected]>

robert3005 enabled auto-merge (squash) October 15, 2026 11:38

robert3005 merged commit c1a2ca1 into develop Oct 15, 2026
91 checks passed

robert3005 deleted the rk/duckdb-list-contains branch October 15, 2026 11:41

joseph-isaacs mentioned this pull request Oct 15, 2026

perf(duckdb): push down join filters and optimize JOB scans #10507

Closed

Navigation Menu

Repository navigation

Use SQL null semantics for DuckDB IN and NOT IN pushdown - #10054

Use SQL null semantics for DuckDB IN and NOT IN pushdown#10054
robert3005 merged 11 commits into
developfrom
rk/duckdb-list-contains

robert3005 commented Sep 25, 2026 •

edited

Loading

Uh oh!

codspeed Bot commented Sep 25, 2026 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

joseph-isaacs Oct 15, 2026

Uh oh!

joseph-isaacs Oct 15, 2026

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

robert3005 commented Sep 25, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

codspeed Bot commented Sep 25, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Merging this PR will improve performance by 13.19%

Performance Changes

Uh oh!

Uh oh!

Uh oh!

joseph-isaacs Oct 15, 2026

Choose a reason for hiding this comment

Uh oh!

joseph-isaacs Oct 15, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

robert3005 commented Sep 25, 2026 •

edited

Loading

codspeed Bot commented Sep 25, 2026 •

edited

Loading