You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "viirya (via GitHub)" <gi...@apache.org> on 2026/01/16 18:14:45 UTC
[PR] Multi column null aware anti join [datafusion]
viirya opened a new pull request, #19857:
URL: https://github.com/apache/datafusion/pull/19857
## Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
- Closes #.
## Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
## What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
feat: Add multi-column support for null-aware anti joins
This commit extends null-aware anti join functionality to support
multiple columns, enabling queries like:
SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT x, y FROM t2);
and correlated multi-column NOT IN subqueries:
SELECT * FROM t1 WHERE (c2, c3) NOT IN (
SELECT c2, c3 FROM t2 WHERE t1.c1 = t2.c1
);
Changes:
Physical Execution Layer:
- Remove single-column validation restriction in HashJoinExec
- Extend NULL detection in probe phase to check ANY column for NULLs
- Extend NULL filtering in final phase to filter rows with ANY NULL column
- Add comprehensive unit tests for 2-column and 3-column joins
SQL Planning Layer:
- Allow tuple expressions in parse_in_subquery()
- Add validation for tuple field count matching
Query Optimization Layer:
- Update InSubquery validation to allow struct expressions
- Skip type coercion for struct expressions (handled in decorrelation)
- Implement struct decomposition in decorrelate_predicate_subquery
- Decompose struct(a, b) into individual join conditions a = x AND b = y
- Handle both correlated and non-correlated multi-column subqueries
Test Coverage:
- Add 7 new SQL logic test cases (Tests 19-25)
- Add 3 unit test functions with 15 test variants (5 batch sizes each)
- Cover 2-column, 3-column, empty subquery, and NULL patterns
- Include correlated multi-column NOT IN from issue #10583
- Add test coverage for multi-column IN subqueries to verify that the
struct expression support works correctly for both negated (NOT IN)
and non-negated (IN) cases.
## Are these changes tested?
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
-->
## Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `api change` label.
-->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5828457880
<!-- semver-check-comment -->
Thank you for opening this pull request!
Reviewer note: [cargo-semver-checks](https://github.com/obi1kenobi/cargo-semver-checks) reported the current version number is not SemVer-compatible with the changes in this pull request (compared against the base branch).
<details>
<summary>Details</summary>
```
Cloning apache/main
Building datafusion v55.1.0 (current)
Built [ 62.449s] (current)
Parsing datafusion v55.1.0 (current)
Parsed [ 0.036s] (current)
Building datafusion v55.1.0 (baseline)
Built [ 61.844s] (baseline)
Parsing datafusion v55.1.0 (baseline)
Parsed [ 0.036s] (baseline)
Checking datafusion v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.626s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 126.776s] datafusion
Building datafusion-expr v55.1.0 (current)
Built [ 30.613s] (current)
Parsing datafusion-expr v55.1.0 (current)
Parsed [ 0.086s] (current)
Building datafusion-expr v55.1.0 (baseline)
Built [ 30.074s] (baseline)
Parsing datafusion-expr v55.1.0 (baseline)
Parsed [ 0.081s] (baseline)
Checking datafusion-expr v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 1.262s] 223 checks: 222 pass, 1 fail, 0 warn, 31 skip
--- failure constructible_struct_adds_field: struct exhaustively constructible through public API adds field ---
Description:
A pub struct that could be exhaustively constructed with a literal using only public API has a new pub field, breaking existing exhaustive literals.
ref: https://doc.rust-lang.org/reference/expressions/struct-expr.html
impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.50.0/src/lints/constructible_struct_adds_field.ron
Failed in:
field Join.null_aware_value_keys in /home/runner/work/datafusion/datafusion/datafusion/expr/src/logical_plan/plan.rs:4475
field Join.null_aware_value_keys in /home/runner/work/datafusion/datafusion/datafusion/expr/src/logical_plan/plan.rs:4475
Summary semver requires new major version: 1 major and 0 minor checks failed
Finished [ 63.272s] datafusion-expr
Building datafusion-optimizer v55.1.0 (current)
Built [ 28.221s] (current)
Parsing datafusion-optimizer v55.1.0 (current)
Parsed [ 0.033s] (current)
Building datafusion-optimizer v55.1.0 (baseline)
Built [ 29.951s] (baseline)
Parsing datafusion-optimizer v55.1.0 (baseline)
Parsed [ 0.033s] (baseline)
Checking datafusion-optimizer v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.168s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 59.261s] datafusion-optimizer
Building datafusion-physical-plan v55.1.0 (current)
Built [ 41.947s] (current)
Parsing datafusion-physical-plan v55.1.0 (current)
Parsed [ 0.173s] (current)
Building datafusion-physical-plan v55.1.0 (baseline)
Built [ 40.974s] (baseline)
Parsing datafusion-physical-plan v55.1.0 (baseline)
Parsed [ 0.171s] (baseline)
Checking datafusion-physical-plan v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.670s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 85.587s] datafusion-physical-plan
Building datafusion-proto v55.1.0 (current)
Built [ 58.710s] (current)
Parsing datafusion-proto v55.1.0 (current)
Parsed [ 0.018s] (current)
Building datafusion-proto v55.1.0 (baseline)
Built [ 59.740s] (baseline)
Parsing datafusion-proto v55.1.0 (baseline)
Parsed [ 0.019s] (baseline)
Checking datafusion-proto v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.130s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 120.192s] datafusion-proto
Building datafusion-proto-models v55.1.0 (current)
Built [ 26.798s] (current)
Parsing datafusion-proto-models v55.1.0 (current)
Parsed [ 0.135s] (current)
Building datafusion-proto-models v55.1.0 (baseline)
Built [ 26.894s] (baseline)
Parsing datafusion-proto-models v55.1.0 (baseline)
Parsed [ 0.136s] (baseline)
Checking datafusion-proto-models v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 1.698s] 223 checks: 222 pass, 1 fail, 0 warn, 31 skip
--- failure constructible_struct_adds_field: struct exhaustively constructible through public API adds field ---
Description:
A pub struct that could be exhaustively constructed with a literal using only public API has a new pub field, breaking existing exhaustive literals.
ref: https://doc.rust-lang.org/reference/expressions/struct-expr.html
impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.50.0/src/lints/constructible_struct_adds_field.ron
Failed in:
field JoinNode.null_aware_value_keys in /home/runner/work/datafusion/datafusion/datafusion/proto-models/src/generated/prost.rs:428
field JoinNode.null_aware_value_keys in /home/runner/work/datafusion/datafusion/datafusion/proto-models/src/generated/prost.rs:428
field HashJoinExecNode.null_aware_value_keys in /home/runner/work/datafusion/datafusion/datafusion/proto-models/src/generated/prost.rs:2156
field HashJoinExecNode.null_aware_value_keys in /home/runner/work/datafusion/datafusion/datafusion/proto-models/src/generated/prost.rs:2156
Summary semver requires new major version: 1 major and 0 minor checks failed
Finished [ 56.856s] datafusion-proto-models
Building datafusion-sql v55.1.0 (current)
Built [ 46.391s] (current)
Parsing datafusion-sql v55.1.0 (current)
Parsed [ 0.032s] (current)
Building datafusion-sql v55.1.0 (baseline)
Built [ 46.465s] (baseline)
Parsing datafusion-sql v55.1.0 (baseline)
Parsed [ 0.034s] (baseline)
Checking datafusion-sql v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.249s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 94.426s] datafusion-sql
Building datafusion-sqllogictest v55.1.0 (current)
Built [ 106.108s] (current)
Parsing datafusion-sqllogictest v55.1.0 (current)
Parsed [ 0.021s] (current)
Building datafusion-sqllogictest v55.1.0 (baseline)
Built [ 107.391s] (baseline)
Parsing datafusion-sqllogictest v55.1.0 (baseline)
Parsed [ 0.025s] (baseline)
Checking datafusion-sqllogictest v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.095s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 216.256s] datafusion-sqllogictest
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5901554752
π€ Benchmark completed (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-5901079308)
**Instance:** `c4a-highmem-16` (12 vCPU / 65 GiB)
Comparing multi-column-null-aware-anti-join (295c0561392e9fe23a401b3c59c37ceeba1f829f) to 696eaf5 (merge-base) [diff](https://github.com/apache/datafusion/compare/696eaf58d436faa6532aa0f5875b5a120b94d0eb..295c0561392e9fe23a401b3c59c37ceeba1f829f)
<details><summary>Run configuration</summary>
```yaml
run benchmark tpch
```
</details>
<details><summary>CPU Details (lscpu)</summary>
```
Architecture: aarch64
CPU op-mode(s): 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Vendor ID: ARM
Model name: Neoverse-V2
Model: 1
Thread(s) per core: 1
Core(s) per cluster: 16
Socket(s): -
Cluster(s): 1
Stepping: r0p1
BogoMIPS: 2000.00
Flags: fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm jscvt fcma lrcpc dcpop sha3 sm3 sm4 asimddp sha512 sve asimdfhm dit uscat ilrcpc flagm sb paca pacg dcpodp sve2 sveaes svepmull svebitperm svesha3 svesm4 flagm2 frint svei8mm svebf16 i8mm bf16 dgh rng bti
L1d cache: 1 MiB (16 instances)
L1i cache: 1 MiB (16 instances)
L2 cache: 32 MiB (16 instances)
L3 cache: 80 MiB (1 instance)
NUMA node(s): 1
NUMA node0 CPU(s): 0-15
Vulnerability Gather data sampling: Not affected
Vulnerability Indirect target selection: Not affected
Vulnerability Itlb multihit: Not affected
Vulnerability L1tf: Not affected
Vulnerability Mds: Not affected
Vulnerability Meltdown: Not affected
Vulnerability Mmio stale data: Not affected
Vulnerability Reg file data sampling: Not affected
Vulnerability Retbleed: Not affected
Vulnerability Spec rstack overflow: Not affected
Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Vulnerability Spectre v1: Mitigation; __user pointer sanitization
Vulnerability Spectre v2: Mitigation; CSV2, BHB
Vulnerability Srbds: Not affected
Vulnerability Tsa: Not affected
Vulnerability Tsx async abort: Not affected
Vulnerability Vmscape: Not affected
```
</details>
<details><summary>Details</summary>
<p>
```
Comparing HEAD and multi-column-null-aware-anti-join
--------------------
Benchmark tpch_sf1.json
--------------------
βββββββββββββ³βββββββββββ³ββββββββββββββββββββββββββββββββββββ³ββββββββββββ
β Query β HEAD β multi-column-null-aware-anti-join β Change β
β‘βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β QQuery 1 β 40.63 ms β 40.76 ms β no change β
β QQuery 2 β 18.35 ms β 18.46 ms β no change β
β QQuery 3 β 28.21 ms β 28.34 ms β no change β
β QQuery 4 β 17.18 ms β 17.19 ms β no change β
β QQuery 5 β 34.90 ms β 35.08 ms β no change β
β QQuery 6 β 15.97 ms β 16.34 ms β no change β
β QQuery 7 β 40.62 ms β 41.84 ms β no change β
β QQuery 8 β 39.99 ms β 40.23 ms β no change β
β QQuery 9 β 49.21 ms β 48.72 ms β no change β
β QQuery 10 β 40.26 ms β 40.15 ms β no change β
β QQuery 11 β 13.55 ms β 13.28 ms β no change β
β QQuery 12 β 20.79 ms β 20.77 ms β no change β
β QQuery 13 β 39.38 ms β 39.28 ms β no change β
β QQuery 14 β 24.47 ms β 24.32 ms β no change β
β QQuery 15 β 30.85 ms β 30.60 ms β no change β
β QQuery 16 β 13.81 ms β 13.94 ms β no change β
β QQuery 17 β 71.80 ms β 71.95 ms β no change β
β QQuery 18 β 62.01 ms β 61.48 ms β no change β
β QQuery 19 β 31.63 ms β 31.84 ms β no change β
β QQuery 20 β 31.36 ms β 31.51 ms β no change β
β QQuery 21 β 54.66 ms β 54.40 ms β no change β
β QQuery 22 β 14.31 ms β 13.65 ms β no change β
βββββββββββββ΄βββββββββββ΄ββββββββββββββββββββββββββββββββββββ΄ββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββ³βββββββββββ
β Benchmark Summary β β
β‘ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β Total Time (HEAD) β 733.94ms β
β Total Time (multi-column-null-aware-anti-join) β 734.14ms β
β Average Time (HEAD) β 33.36ms β
β Average Time (multi-column-null-aware-anti-join) β 33.37ms β
β Queries Faster β 0 β
β Queries Slower β 0 β
β Queries with No Change β 22 β
β Queries with Failure β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββ
Distribution per query (min / mean Β±stddev / max):
Comparing HEAD and multi-column-null-aware-anti-join
--------------------
Benchmark tpch_sf1.json
--------------------
βββββββββββββ³βββββββββββββββββββββββββββββββββ³ββββββββββββββββββββββββββββββββββββ³ββββββββββββ
β Query β HEAD β multi-column-null-aware-anti-join β Change β
β‘βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β QQuery 1 β 40.63 / 42.74 Β±2.56 / 46.49 ms β 40.76 / 41.46 Β±1.06 / 43.57 ms β no change β
β QQuery 2 β 18.35 / 18.67 Β±0.22 / 18.99 ms β 18.46 / 19.00 Β±0.65 / 20.25 ms β no change β
β QQuery 3 β 28.21 / 28.61 Β±0.23 / 28.87 ms β 28.34 / 28.50 Β±0.12 / 28.65 ms β no change β
β QQuery 4 β 17.18 / 17.98 Β±0.81 / 19.11 ms β 17.19 / 17.49 Β±0.33 / 18.12 ms β no change β
β QQuery 5 β 34.90 / 35.08 Β±0.12 / 35.21 ms β 35.08 / 36.09 Β±1.42 / 38.83 ms β no change β
β QQuery 6 β 15.97 / 16.45 Β±0.51 / 17.37 ms β 16.34 / 16.57 Β±0.16 / 16.74 ms β no change β
β QQuery 7 β 40.62 / 41.57 Β±1.07 / 43.55 ms β 41.84 / 43.13 Β±0.94 / 44.24 ms β no change β
β QQuery 8 β 39.99 / 40.61 Β±0.37 / 40.99 ms β 40.23 / 40.87 Β±0.89 / 42.63 ms β no change β
β QQuery 9 β 49.21 / 49.90 Β±0.73 / 51.22 ms β 48.72 / 50.22 Β±0.89 / 51.31 ms β no change β
β QQuery 10 β 40.26 / 41.38 Β±1.08 / 42.89 ms β 40.15 / 40.51 Β±0.22 / 40.79 ms β no change β
β QQuery 11 β 13.55 / 14.01 Β±0.70 / 15.41 ms β 13.28 / 13.57 Β±0.22 / 13.89 ms β no change β
β QQuery 12 β 20.79 / 21.22 Β±0.26 / 21.55 ms β 20.77 / 21.42 Β±0.60 / 22.46 ms β no change β
β QQuery 13 β 39.38 / 41.15 Β±2.01 / 44.64 ms β 39.28 / 40.45 Β±1.27 / 42.70 ms β no change β
β QQuery 14 β 24.47 / 24.73 Β±0.14 / 24.86 ms β 24.32 / 24.51 Β±0.13 / 24.69 ms β no change β
β QQuery 15 β 30.85 / 31.41 Β±0.61 / 32.54 ms β 30.60 / 31.04 Β±0.56 / 32.14 ms β no change β
β QQuery 16 β 13.81 / 14.15 Β±0.30 / 14.68 ms β 13.94 / 14.32 Β±0.37 / 15.02 ms β no change β
β QQuery 17 β 71.80 / 73.64 Β±1.27 / 75.57 ms β 71.95 / 73.85 Β±1.48 / 75.63 ms β no change β
β QQuery 18 β 62.01 / 64.38 Β±1.89 / 67.03 ms β 61.48 / 62.25 Β±0.53 / 62.83 ms β no change β
β QQuery 19 β 31.63 / 33.70 Β±1.41 / 35.67 ms β 31.84 / 32.48 Β±0.94 / 34.35 ms β no change β
β QQuery 20 β 31.36 / 31.78 Β±0.28 / 32.19 ms β 31.51 / 31.73 Β±0.17 / 32.02 ms β no change β
β QQuery 21 β 54.66 / 55.53 Β±0.68 / 56.35 ms β 54.40 / 55.13 Β±0.78 / 56.64 ms β no change β
β QQuery 22 β 14.31 / 14.37 Β±0.04 / 14.43 ms β 13.65 / 13.89 Β±0.17 / 14.08 ms β no change β
βββββββββββββ΄βββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββββββββ΄ββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββ³βββββββββββ
β Benchmark Summary β β
β‘ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β Total Time (HEAD) β 753.07ms β
β Total Time (multi-column-null-aware-anti-join) β 748.48ms β
β Average Time (HEAD) β 34.23ms β
β Average Time (multi-column-null-aware-anti-join) β 34.02ms β
β Queries Faster β 0 β
β Queries Slower β 0 β
β Queries with No Change β 22 β
β Queries with Failure β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββ
```
</p>
</details>
<details><summary>Resource Usage</summary>
**tpch β base (merge-base)**
| Metric | Value |
|--------|-------|
| Wall time | 5.0s |
| Peak memory | 1.2 GiB |
| Avg memory | 506.3 MiB |
| CPU user | 20.9s |
| CPU sys | 1.7s |
| Peak spill | 0 B |
**tpch β branch**
| Metric | Value |
|--------|-------|
| Wall time | 5.0s |
| Peak memory | 1.3 GiB |
| Avg memory | 495.3 MiB |
| CPU user | 20.9s |
| CPU sys | 1.6s |
| Peak spill | 0 B |
</details>
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132611332
run benchmark tpcds
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5350267528
Thank you for your contribution. Unfortunately, this pull request is stale because it has been open 60 days with no activity. Please remove the stale label or comment or this will be closed in 7 days.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-3880989276
@2010YOUY01 @jonathanc-n FYI
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r3100490162
##########
datafusion/sqllogictest/test_files/null_aware_anti_join.slt:
##########
@@ -394,16 +394,202 @@ query II rowsort
SELECT * FROM test_table WHERE (c1 NOT IN (SELECT c2 FROM test_table)) = true;
----
-# NOTE: The correlated subquery version from issue #10583:
-# SELECT * FROM test_table t1 WHERE c1 NOT IN (SELECT c2 FROM test_table t2 WHERE t1.c1 = t2.c1)
-# is not yet supported because it creates a multi-column join (correlation + NOT IN condition).
-# This is a known limitation - currently only supports single column null-aware anti joins.
-# This will be addressed in next Phase (multi-column support).
+#############
+## Test 19: Multi-column NOT IN with NULL in subquery
+#############
+
+statement ok
+CREATE TABLE multi_col_outer(a INT, b INT, value TEXT) AS VALUES
+(1, 2, 'x'),
+(3, 4, 'y'),
+(5, 6, 'z'),
+(NULL, 8, 'w');
+
+statement ok
+CREATE TABLE multi_col_inner_with_null(x INT, y INT) AS VALUES
+(1, 2),
+(NULL, 4);
+
+# Should return empty because subquery has NULL
+query IIT rowsort
+SELECT * FROM multi_col_outer
+WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_with_null);
+----
+
+#############
+## Test 20: Multi-column NOT IN without NULL
+#############
+
+statement ok
+CREATE TABLE multi_col_inner_no_null(x INT, y INT) AS VALUES
+(1, 2);
+
+# Should return (3, 4, 'y'), (5, 6, 'z')
+# The row (NULL, 8, 'w') is not returned because NULL is not comparable
+query IIT rowsort
+SELECT * FROM multi_col_outer
+WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_no_null);
+----
+3 4 y
+5 6 z
Review Comment:
This is wrong; compare with the output of Postgres, which returns
```
| a | b | value |
| --- | --- | ----- |
| 3 | 4 | y |
| 5 | 6 | z |
| | 8 | w |
```
`(NULL, 8, 'w')` *should* be returned, because we know that `b=8` does not match any row in the subquery result set.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya merged PR #19857:
URL: https://github.com/apache/datafusion/pull/19857
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132731190
π€ Benchmark completed (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-4132611332)
<details><summary>Details</summary>
<p>
```
Comparing HEAD and multi-column-null-aware-anti-join
--------------------
Benchmark tpcds_sf1.json
--------------------
βββββββββββββ³βββββββββββββββββββββββββββββββββββββββββββ³βββββββββββββββββββββββββββββββββββββββββββ³ββββββββββββββββ
β Query β HEAD β multi-column-null-aware-anti-join β Change β
β‘ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β QQuery 1 β 43.83 / 44.48 Β±0.78 / 45.74 ms β 43.31 / 44.09 Β±0.73 / 45.47 ms β no change β
β QQuery 2 β 146.21 / 147.16 Β±0.77 / 148.28 ms β 147.17 / 147.48 Β±0.36 / 147.92 ms β no change β
β QQuery 3 β 116.32 / 117.06 Β±0.54 / 117.70 ms β 115.53 / 116.25 Β±0.53 / 117.10 ms β no change β
β QQuery 4 β 1318.18 / 1335.72 Β±8.89 / 1342.24 ms β 1302.77 / 1339.59 Β±27.16 / 1375.30 ms β no change β
β QQuery 5 β 173.39 / 175.18 Β±1.71 / 177.99 ms β 175.20 / 176.42 Β±1.06 / 178.04 ms β no change β
β QQuery 6 β 976.93 / 1017.94 Β±25.92 / 1054.05 ms β 983.14 / 1014.13 Β±27.67 / 1053.44 ms β no change β
β QQuery 7 β 354.18 / 357.36 Β±2.47 / 361.50 ms β 352.61 / 356.67 Β±3.24 / 359.99 ms β no change β
β QQuery 8 β 116.72 / 117.72 Β±1.19 / 120.07 ms β 115.87 / 116.81 Β±0.74 / 117.72 ms β no change β
β QQuery 9 β 102.03 / 105.59 Β±2.94 / 108.74 ms β 107.50 / 112.35 Β±8.61 / 129.55 ms β 1.06x slower β
β QQuery 10 β 108.19 / 109.89 Β±1.06 / 110.86 ms β 107.98 / 109.25 Β±0.69 / 109.88 ms β no change β
β QQuery 11 β 908.88 / 932.51 Β±15.90 / 956.37 ms β 885.34 / 923.46 Β±27.79 / 960.12 ms β no change β
β QQuery 12 β 46.06 / 47.12 Β±0.70 / 48.11 ms β 45.21 / 45.71 Β±0.39 / 46.38 ms β no change β
β QQuery 13 β 403.29 / 407.02 Β±2.30 / 410.46 ms β 400.29 / 403.12 Β±3.05 / 408.99 ms β no change β
β QQuery 14 β 1029.55 / 1041.76 Β±8.13 / 1052.07 ms β 1027.32 / 1035.48 Β±6.46 / 1041.60 ms β no change β
β QQuery 15 β 16.56 / 17.29 Β±0.53 / 18.11 ms β 15.73 / 16.96 Β±0.86 / 17.87 ms β no change β
β QQuery 16 β 41.90 / 42.93 Β±0.73 / 44.12 ms β 41.38 / 42.25 Β±0.92 / 43.90 ms β no change β
β QQuery 17 β 245.22 / 247.74 Β±1.53 / 249.40 ms β 239.64 / 240.72 Β±0.66 / 241.59 ms β no change β
β QQuery 18 β 131.28 / 133.03 Β±1.12 / 134.30 ms β 129.15 / 130.47 Β±0.95 / 131.92 ms β no change β
β QQuery 19 β 158.07 / 159.11 Β±1.04 / 161.08 ms β 156.74 / 158.82 Β±1.44 / 160.64 ms β no change β
β QQuery 20 β 14.21 / 15.37 Β±0.77 / 16.32 ms β 13.80 / 14.39 Β±0.61 / 15.46 ms β +1.07x faster β
β QQuery 21 β 20.81 / 21.12 Β±0.22 / 21.37 ms β 19.99 / 20.26 Β±0.29 / 20.80 ms β no change β
β QQuery 22 β 493.95 / 496.55 Β±1.48 / 498.39 ms β 487.27 / 491.13 Β±3.32 / 496.07 ms β no change β
β QQuery 23 β 916.18 / 922.08 Β±6.99 / 935.23 ms β 896.75 / 908.58 Β±8.32 / 918.52 ms β no change β
β QQuery 24 β 418.66 / 421.78 Β±2.93 / 427.12 ms β 420.02 / 423.04 Β±3.35 / 429.56 ms β no change β
β QQuery 25 β 354.94 / 360.65 Β±3.68 / 365.79 ms β 358.66 / 360.03 Β±0.90 / 361.03 ms β no change β
β QQuery 26 β 84.89 / 85.93 Β±0.79 / 87.09 ms β 82.96 / 84.51 Β±1.72 / 87.71 ms β no change β
β QQuery 27 β 351.74 / 354.72 Β±3.43 / 360.26 ms β 347.83 / 352.22 Β±2.92 / 355.67 ms β no change β
β QQuery 28 β 150.47 / 153.64 Β±1.73 / 155.63 ms β 150.58 / 151.68 Β±1.09 / 153.25 ms β no change β
β QQuery 29 β 304.54 / 308.63 Β±3.86 / 314.17 ms β 299.22 / 302.51 Β±3.00 / 307.86 ms β no change β
β QQuery 30 β 43.75 / 46.65 Β±2.47 / 50.74 ms β 43.52 / 45.83 Β±1.63 / 48.58 ms β no change β
β QQuery 31 β 171.49 / 174.14 Β±1.77 / 176.36 ms β 171.63 / 174.30 Β±1.66 / 176.08 ms β no change β
β QQuery 32 β 57.66 / 67.54 Β±17.15 / 101.79 ms β 57.71 / 59.09 Β±1.14 / 60.54 ms β +1.14x faster β
β QQuery 33 β 144.20 / 145.25 Β±0.76 / 146.50 ms β 142.15 / 144.13 Β±1.39 / 145.86 ms β no change β
β QQuery 34 β 108.82 / 109.88 Β±1.04 / 111.73 ms β 107.76 / 108.62 Β±0.72 / 109.75 ms β no change β
β QQuery 35 β 110.99 / 112.62 Β±0.87 / 113.41 ms β 110.10 / 111.19 Β±1.11 / 113.20 ms β no change β
β QQuery 36 β 217.46 / 224.74 Β±4.61 / 230.43 ms β 216.54 / 220.79 Β±3.76 / 225.36 ms β no change β
β QQuery 37 β 180.41 / 184.25 Β±2.30 / 186.85 ms β 179.45 / 181.42 Β±1.34 / 183.52 ms β no change β
β QQuery 38 β 89.32 / 91.84 Β±2.78 / 96.01 ms β 85.64 / 89.98 Β±3.81 / 96.51 ms β no change β
β QQuery 39 β 124.50 / 130.42 Β±3.89 / 136.67 ms β 129.92 / 132.65 Β±2.37 / 135.43 ms β no change β
β QQuery 40 β 114.26 / 119.79 Β±5.41 / 129.80 ms β 116.16 / 119.54 Β±5.34 / 130.15 ms β no change β
β QQuery 41 β 14.74 / 15.91 Β±0.71 / 16.79 ms β 14.58 / 15.96 Β±1.05 / 17.31 ms β no change β
β QQuery 42 β 108.67 / 110.20 Β±0.85 / 111.07 ms β 109.11 / 110.81 Β±0.97 / 112.00 ms β no change β
β QQuery 43 β 84.01 / 85.47 Β±0.90 / 86.82 ms β 84.92 / 86.07 Β±1.05 / 87.41 ms β no change β
β QQuery 44 β 12.32 / 12.48 Β±0.17 / 12.81 ms β 11.45 / 11.86 Β±0.32 / 12.33 ms β +1.05x faster β
β QQuery 45 β 53.98 / 55.49 Β±0.89 / 56.65 ms β 52.75 / 54.47 Β±1.14 / 56.20 ms β no change β
β QQuery 46 β 236.05 / 238.07 Β±1.14 / 239.41 ms β 232.13 / 235.67 Β±2.35 / 238.90 ms β no change β
β QQuery 47 β 693.34 / 718.06 Β±16.10 / 741.93 ms β 691.86 / 704.19 Β±8.70 / 714.81 ms β no change β
β QQuery 48 β 289.45 / 295.56 Β±5.22 / 302.94 ms β 291.82 / 293.85 Β±3.10 / 300.00 ms β no change β
β QQuery 49 β 256.17 / 259.32 Β±2.88 / 263.76 ms β 254.94 / 256.85 Β±2.36 / 260.84 ms β no change β
β QQuery 50 β 236.05 / 238.86 Β±3.51 / 245.00 ms β 231.86 / 234.50 Β±2.35 / 237.93 ms β no change β
β QQuery 51 β 183.08 / 189.42 Β±4.47 / 195.93 ms β 188.83 / 191.15 Β±2.02 / 193.76 ms β no change β
β QQuery 52 β 108.99 / 110.06 Β±0.66 / 111.04 ms β 110.09 / 111.24 Β±0.96 / 112.84 ms β no change β
β QQuery 53 β 103.49 / 104.18 Β±0.63 / 105.31 ms β 105.87 / 107.09 Β±0.90 / 108.56 ms β no change β
β QQuery 54 β 148.12 / 149.81 Β±1.88 / 153.33 ms β 150.72 / 151.89 Β±1.56 / 154.69 ms β no change β
β QQuery 55 β 108.98 / 111.00 Β±1.87 / 113.69 ms β 109.27 / 110.39 Β±1.01 / 112.28 ms β no change β
β QQuery 56 β 143.43 / 145.10 Β±1.68 / 148.03 ms β 144.87 / 145.69 Β±0.56 / 146.43 ms β no change β
β QQuery 57 β 178.73 / 180.46 Β±1.51 / 182.61 ms β 178.24 / 179.32 Β±0.93 / 181.06 ms β no change β
β QQuery 58 β 295.59 / 308.05 Β±11.94 / 324.77 ms β 297.21 / 305.11 Β±6.36 / 314.08 ms β no change β
β QQuery 59 β 197.88 / 200.82 Β±2.19 / 203.82 ms β 203.20 / 204.87 Β±0.88 / 205.66 ms β no change β
β QQuery 60 β 144.17 / 146.66 Β±1.54 / 148.48 ms β 148.65 / 149.86 Β±0.81 / 150.79 ms β no change β
β QQuery 61 β 173.03 / 174.47 Β±0.82 / 175.26 ms β 174.07 / 175.60 Β±1.08 / 177.31 ms β no change β
β QQuery 62 β 928.31 / 940.78 Β±8.68 / 951.41 ms β 881.70 / 920.22 Β±31.31 / 970.43 ms β no change β
β QQuery 63 β 105.06 / 107.54 Β±2.52 / 111.73 ms β 106.19 / 108.64 Β±1.60 / 110.49 ms β no change β
β QQuery 64 β 714.18 / 717.77 Β±3.02 / 722.59 ms β 702.07 / 705.06 Β±3.71 / 711.51 ms β no change β
β QQuery 65 β 257.73 / 260.58 Β±2.32 / 263.22 ms β 253.11 / 256.56 Β±3.22 / 262.46 ms β no change β
β QQuery 66 β 248.65 / 256.39 Β±5.54 / 262.34 ms β 243.88 / 253.67 Β±8.19 / 263.92 ms β no change β
β QQuery 67 β 319.17 / 321.87 Β±2.96 / 327.32 ms β 314.63 / 317.13 Β±2.10 / 321.03 ms β no change β
β QQuery 68 β 282.59 / 287.94 Β±4.19 / 294.81 ms β 281.82 / 286.47 Β±3.05 / 290.84 ms β no change β
β QQuery 69 β 104.85 / 107.06 Β±3.00 / 112.91 ms β 103.82 / 106.11 Β±1.48 / 108.00 ms β no change β
β QQuery 70 β 336.33 / 352.78 Β±12.21 / 373.19 ms β 329.23 / 339.61 Β±12.22 / 363.38 ms β no change β
β QQuery 71 β 137.31 / 138.16 Β±0.90 / 139.81 ms β 134.47 / 136.82 Β±1.88 / 139.88 ms β no change β
β QQuery 72 β 714.00 / 735.13 Β±23.66 / 780.30 ms β 711.30 / 735.79 Β±14.74 / 751.21 ms β no change β
β QQuery 73 β 103.18 / 106.39 Β±2.30 / 109.20 ms β 105.75 / 108.36 Β±3.10 / 113.66 ms β no change β
β QQuery 74 β 579.47 / 592.96 Β±9.58 / 605.62 ms β 574.38 / 622.50 Β±29.78 / 654.64 ms β no change β
β QQuery 75 β 278.60 / 282.13 Β±1.83 / 283.80 ms β 282.42 / 289.49 Β±3.84 / 293.37 ms β no change β
β QQuery 76 β 133.09 / 134.84 Β±1.42 / 137.33 ms β 138.55 / 139.19 Β±0.48 / 139.90 ms β no change β
β QQuery 77 β 188.52 / 192.32 Β±2.32 / 195.51 ms β 189.12 / 192.93 Β±2.10 / 194.90 ms β no change β
β QQuery 78 β 359.65 / 364.01 Β±3.02 / 366.75 ms β 348.34 / 354.51 Β±4.27 / 360.49 ms β no change β
β QQuery 79 β 245.19 / 249.52 Β±2.98 / 252.64 ms β 236.97 / 237.98 Β±0.98 / 239.73 ms β no change β
β QQuery 80 β 331.90 / 337.17 Β±3.40 / 342.21 ms β 330.97 / 332.74 Β±1.02 / 334.08 ms β no change β
β QQuery 81 β 28.66 / 30.18 Β±1.62 / 33.20 ms β 26.16 / 27.56 Β±1.21 / 29.12 ms β +1.09x faster β
β QQuery 82 β 203.91 / 207.17 Β±2.42 / 210.21 ms β 203.99 / 205.52 Β±1.48 / 207.82 ms β no change β
β QQuery 83 β 40.55 / 42.37 Β±1.34 / 43.99 ms β 38.40 / 40.75 Β±1.44 / 42.86 ms β no change β
β QQuery 84 β 49.62 / 50.86 Β±1.08 / 52.18 ms β 49.13 / 49.80 Β±0.48 / 50.55 ms β no change β
β QQuery 85 β 153.00 / 154.47 Β±1.35 / 156.72 ms β 149.41 / 150.98 Β±1.67 / 154.22 ms β no change β
β QQuery 86 β 40.79 / 42.04 Β±0.97 / 43.45 ms β 38.25 / 39.60 Β±1.31 / 41.28 ms β +1.06x faster β
β QQuery 87 β 86.64 / 90.50 Β±3.92 / 97.56 ms β 84.53 / 88.29 Β±3.39 / 94.69 ms β no change β
β QQuery 88 β 101.02 / 101.80 Β±0.79 / 103.12 ms β 100.51 / 101.92 Β±0.84 / 102.77 ms β no change β
β QQuery 89 β 120.00 / 121.30 Β±1.05 / 122.53 ms β 119.10 / 120.92 Β±1.16 / 122.61 ms β no change β
β QQuery 90 β 23.69 / 24.39 Β±0.46 / 25.07 ms β 23.96 / 24.79 Β±0.62 / 25.70 ms β no change β
β QQuery 91 β 64.94 / 66.92 Β±1.16 / 68.09 ms β 62.85 / 64.59 Β±1.07 / 65.64 ms β no change β
β QQuery 92 β 58.27 / 59.03 Β±0.85 / 60.50 ms β 58.12 / 58.99 Β±0.78 / 59.94 ms β no change β
β QQuery 93 β 189.68 / 192.58 Β±2.47 / 197.13 ms β 189.91 / 191.49 Β±0.92 / 192.67 ms β no change β
β QQuery 94 β 62.20 / 62.74 Β±0.59 / 63.83 ms β 60.69 / 61.84 Β±0.62 / 62.55 ms β no change β
β QQuery 95 β 133.81 / 137.41 Β±1.99 / 139.20 ms β 133.63 / 135.07 Β±1.05 / 136.54 ms β no change β
β QQuery 96 β 73.80 / 75.51 Β±1.23 / 77.54 ms β 76.32 / 76.87 Β±0.31 / 77.22 ms β no change β
β QQuery 97 β 130.18 / 133.14 Β±2.37 / 136.33 ms β 130.53 / 135.82 Β±2.77 / 138.65 ms β no change β
β QQuery 98 β 156.33 / 157.92 Β±1.18 / 159.89 ms β 152.35 / 157.35 Β±3.55 / 161.90 ms β no change β
β QQuery 99 β 10713.08 / 10785.09 Β±43.20 / 10843.97 ms β 10730.66 / 10761.22 Β±23.32 / 10798.69 ms β no change β
βββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββ³βββββββββββββ
β Benchmark Summary β β
β‘ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β Total Time (HEAD) β 33974.38ms β
β Total Time (multi-column-null-aware-anti-join) β 33799.52ms β
β Average Time (HEAD) β 343.18ms β
β Average Time (multi-column-null-aware-anti-join) β 341.41ms β
β Queries Faster β 5 β
β Queries Slower β 1 β
β Queries with No Change β 93 β
β Queries with Failure β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββββββββ
```
</p>
</details>
<details><summary>Resource Usage</summary>
**tpcds β base (merge-base)**
| Metric | Value |
|--------|-------|
| Wall time | 170.2s |
| Peak memory | 5.8 GiB |
| Avg memory | 4.7 GiB |
| CPU user | 273.4s |
| CPU sys | 19.0s |
| Disk read | 0 B |
| Disk write | 702.8 MiB |
**tpcds β branch**
| Metric | Value |
|--------|-------|
| Wall time | 169.3s |
| Peak memory | 5.9 GiB |
| Avg memory | 4.7 GiB |
| CPU user | 272.5s |
| CPU sys | 18.4s |
| Disk read | 0 B |
| Disk write | 148.0 KiB |
</details>
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5901377522
I will resolve the conflicts.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r4197557438
##########
datafusion/optimizer/src/analyzer/type_coercion.rs:
##########
@@ -1622,6 +1644,36 @@ fn project_with_column_index(
.map(LogicalPlan::Projection)
}
+/// Coerces a multi-column `(a, b, ...) IN (SELECT x, y, ...)`.
+///
+/// Each tuple element and the subquery column at the same position are cast
+/// to their common comparison type, like the single-column form does for its
+/// one pair.
+fn coerce_multi_column_in_subquery(
Review Comment:
This PR now conflicts with `main` in this file. #25094 changed the scalar branch of this arm to `comparison_coercion_with_session_timezone(.., self.session_time_zone)`. This helper is a free function, so a resolution that only patches the scalar branch still compiles and leaves tuple elements on the timezone-unaware `comparison_coercion`. It needs the session time zone passed in as well. A tuple with a naive and a tz-aware timestamp element, with the session time zone set, would catch a miss.
##########
datafusion/expr/src/expr.rs:
##########
@@ -1401,6 +1402,54 @@ impl InSubquery {
}
}
+/// The tuple elements of a multi-column `(a, b, ...) IN (SELECT x, y, ...)`,
+/// given the compared expression `expr` and the `subquery` plan, or `None`
+/// for a single-column `IN`.
+///
+/// The tuple is planned as a `struct` call, which `simplify_expressions` folds
+/// into a struct literal when every element is a literal. It is a
+/// multi-column `IN` only when the subquery returns more than one column;
+/// against a single column the struct is one value compared with a
+/// struct-typed column.
+///
+/// Errors when the number of tuple elements does not match the number of
+/// subquery columns.
+pub fn in_subquery_tuple_values<'a>(
Review Comment:
This helper decides when an `InSubquery` is a tuple, but `Expr::nullable` (`expr_schema.rs`, `InSubquery` arm) still assumes one value. It returns `expr.nullable() | <first subquery column>.nullable()`. For a tuple, `expr` is a `struct(..)` call, and `struct` always reports a non-nullable field (`return_field_from_args`). So the elements never count, and only the first subquery column does. `(a, b) IN (SELECT 1, 5)` is typed NOT NULL, although it is UNKNOWN for `(NULL, 5)`.
`simplify_expressions` runs before decorrelation and folds `IS NULL` and `IS UNKNOWN` to `false`, and `IS NOT NULL` to `true`, from that flag. By my reading this gives a wrong result. I did not run it:
```sql
CREATE TABLE o(a INT, b INT) AS VALUES (NULL, 5), (1, 5);
SELECT a, ((a, b) IN (SELECT 1, 5)) IS NULL AS unk FROM o;
-- expected: (NULL, true) and (1, false)
-- by my reading: false for both rows
```
The scalar form is exact because `a.nullable | x.nullable` covers both sides. For a tuple the arm could return true when any element or any subquery column is nullable. An SLT case with `IS NULL` over a tuple `IN` and a NOT NULL first subquery column would pin it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r4198485575
##########
datafusion/expr/src/expr.rs:
##########
@@ -1401,6 +1402,54 @@ impl InSubquery {
}
}
+/// The tuple elements of a multi-column `(a, b, ...) IN (SELECT x, y, ...)`,
+/// given the compared expression `expr` and the `subquery` plan, or `None`
+/// for a single-column `IN`.
+///
+/// The tuple is planned as a `struct` call, which `simplify_expressions` folds
+/// into a struct literal when every element is a literal. It is a
+/// multi-column `IN` only when the subquery returns more than one column;
+/// against a single column the struct is one value compared with a
+/// struct-typed column.
+///
+/// Errors when the number of tuple elements does not match the number of
+/// subquery columns.
+pub fn in_subquery_tuple_values<'a>(
Review Comment:
Confirmed: `((a, b) IN (SELECT 1, 5)) IS NULL` folded to `false` for `(NULL, 5)`. Fixed in ab3120cc9. The `InSubquery` arm of `Expr::nullable` now treats a tuple `IN` as nullable when any element or any subquery column is nullable. `null_aware_multi_column.slt` covers `IS NULL` and `IS NOT NULL` over a tuple `IN` whose subquery columns are both NOT NULL.
##########
datafusion/optimizer/src/analyzer/type_coercion.rs:
##########
@@ -1622,6 +1644,36 @@ fn project_with_column_index(
.map(LogicalPlan::Projection)
}
+/// Coerces a multi-column `(a, b, ...) IN (SELECT x, y, ...)`.
+///
+/// Each tuple element and the subquery column at the same position are cast
+/// to their common comparison type, like the single-column form does for its
+/// one pair.
+fn coerce_multi_column_in_subquery(
Review Comment:
Merged `main` and passed the session time zone into `coerce_multi_column_in_subquery`, so the scalar branch and every tuple element both use `comparison_coercion_with_session_timezone`. The new case in `null_aware_multi_column.slt` compares a naive and a time-zone-aware timestamp element under `SET TIME ZONE = '+08'`. It fails if the tuple path drops the session time zone, because the naive value is then read in the aware value's own zone and matches.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-3880988140
Thanks @viirya I'll check it soon, afaik there is no TPC* like queries that cover multiple column null aware anti joins, so it would be prob nice to have a bench in future to make sure no performance regression introduced with future PRs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6022577923
Thanks @viirya
@neilconway please let us know your feedback as you requested changes
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r3105141854
##########
datafusion/optimizer/src/analyzer/type_coercion.rs:
##########
@@ -487,23 +487,43 @@ impl TreeNodeRewriter for TypeCoercionRewriter<'_> {
Arc::unwrap_or_clone(subquery.subquery),
)?
.data;
- let expr_type = expr.get_type(self.schema)?;
- let subquery_type = new_plan.schema().field(0).data_type();
- let common_type = comparison_coercion(&expr_type, subquery_type).ok_or(
- plan_datafusion_err!(
- "expr type {expr_type} can't cast to {subquery_type} in InSubquery"
- ),
- )?;
- let new_subquery = Subquery {
- subquery: Arc::new(new_plan),
- outer_ref_columns: subquery.outer_ref_columns,
- spans: subquery.spans,
- };
- Ok(Transformed::yes(Expr::InSubquery(InSubquery::new(
- Box::new(expr.cast_to(&common_type, self.schema)?),
- cast_subquery(new_subquery, &common_type)?,
- negated,
- ))))
+
+ // Check if this is a multi-column IN (struct expression)
+ let is_struct = matches!(*expr, Expr::ScalarFunction(ref func) if func.func.name() == "struct");
+
+ if is_struct {
+ // For multi-column IN, we don't need type coercion at this level
Review Comment:
Can you elaborate on this? It isn't clear to me why the type coercion requirements for multi-column IN would be different than for a single-column IN.
##########
datafusion/expr/src/logical_plan/invariants.rs:
##########
@@ -224,14 +224,31 @@ pub fn check_subquery_expr(
check_correlations_in_subquery(inner_plan)
} else {
if let Expr::InSubquery(subquery) = expr {
- // InSubquery should only return one column
- if subquery.subquery.subquery.schema().fields().len() > 1 {
+ // InSubquery should only return one column UNLESS the left expression is a struct
+ // (multi-column IN like: (a, b) NOT IN (SELECT x, y FROM ...))
+ let is_struct = matches!(*subquery.expr, Expr::ScalarFunction(ref func) if func.func.name() == "struct");
Review Comment:
Matching based on the function name here seems a bit fragile, which is unfortunate because we do it repeatedly in this PR. Is there a way to make the distinction more principled / manifest in the type system?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r4106477505
##########
datafusion/physical-plan/src/joins/hash_join/stream.rs:
##########
@@ -1541,6 +1556,58 @@ fn mark_null_candidates_for_probe_batch(
Ok(())
}
+/// Keeps the candidate pairs whose multi-column `NOT IN` value tuples are not a
+/// definite mismatch, i.e. every element pair is equal or involves a NULL.
+///
+/// Such a pair compares UNKNOWN when some element is NULL (TRUE pairs, with no
+/// NULL, are found by the hash lookup instead), whereas a pair with a definite
+/// mismatch compares FALSE whatever its NULLs are: `(NULL, 1) = (2, 3)` is
+/// `UNKNOWN AND FALSE`, which is FALSE.
+fn retain_value_mismatch_free(
+ build_value_keys: &[ArrayRef],
+ probe_value_keys: &[ArrayRef],
+ build_indices: UInt64Array,
+ probe_indices: UInt32Array,
+) -> Result<(UInt64Array, UInt32Array)> {
+ if build_indices.is_empty() {
+ return Ok((build_indices, probe_indices));
+ }
+ let comparators = build_value_keys
+ .iter()
+ .zip(probe_value_keys)
+ .map(|(build, probe)| {
+ Ok((
+ build.logical_nulls(),
+ probe.logical_nulls(),
+ // Same total order the hash join's own key comparison uses.
Review Comment:
This comment no longer holds. `make_comparator` uses IEEE 754 totalOrder, so `-0.0` vs `+0.0` counts as a definite mismatch here. The join's own key equality (`JoinKeyComparator::new` via `normalize_float_zero`) and SQL `=` treat them as equal. The UNKNOWN check then drops pairs that the exact-match path treats as equal:
```sql
CREATE TABLE fo(a DOUBLE, b DOUBLE) AS VALUES (NULL, 0.0), (1.0, 0.0), (2.0, 5.0);
CREATE TABLE fi(x DOUBLE, y DOUBLE) AS VALUES (1.0, arrow_cast('-0.0', 'Float64'));
CREATE TABLE fi_null(x DOUBLE, y DOUBLE) AS VALUES (NULL, arrow_cast('-0.0', 'Float64'));
-- returns (2, 5) and (NULL, 0). Expected only (2, 5): (NULL, 0) vs (1, -0.0) is UNKNOWN AND TRUE
SELECT * FROM fo WHERE (a, b) NOT IN (SELECT x, y FROM fi);
-- mark for (NULL, 0) is true, expected NULL
SELECT a, b, (a, b) NOT IN (SELECT x, y FROM fi) FROM fo;
-- returns all three rows, expected only (2, 5)
SELECT * FROM fo WHERE (a, b) NOT IN (SELECT x, y FROM fi_null);
```
Normalizing both arrays with `normalize_float_zero` before `make_comparator` fixes all three locally, and the existing SLT files still pass.
This setup also runs once per `batch_size` chunk of candidate pairs and works on the full build arrays. With normalization added, that becomes a full scan of each float build key per chunk. Building the comparators once per probe batch avoids it. A batch-level alternative is `take` on both sides plus the float-normalizing comparison in `datum::apply_cmp`, which keeps the semantics in one place.
##########
datafusion/optimizer/src/decorrelate_predicate_subquery.rs:
##########
@@ -613,34 +666,54 @@ fn build_join(
// Projecting the constant as a column of the outer side turns the predicate
// into a real equi-join key, which fixes all three. It only pays for itself
// on a join that ends up null-aware.
+ //
+ // Every constant element of a tuple is projected the same way, so that all
+ // value keys lead the equi-join keys in order.
let mut projected_left = None;
- if let Some((value, right_col, mut value_name, correlation_opt)) = in_value_expr
- && value.column_refs().is_empty()
- && null_aware
+ if null_aware
+ && in_values
+ .iter()
+ .any(|(value, _)| value.column_refs().is_empty())
{
- // The projected column is unqualified, so a left field that already has
- // this name β however unlikely β would make the reference ambiguous.
let left_schema = left.schema();
- while left_schema.fields().iter().any(|f| f.name() == &value_name) {
- value_name.push('_');
- }
- let value_col = Column::new_unqualified(value_name);
- let projections = left_schema
+ let mut projections = left_schema
.columns()
.into_iter()
.map(Expr::from)
- .chain(std::iter::once(value.alias(value_col.name())))
.collect::<Vec<_>>();
+ let is_tuple = in_values.len() > 1;
+ for (i, (value, _)) in in_values.iter_mut().enumerate() {
+ if !value.column_refs().is_empty() {
+ continue;
+ }
+ let mut value_name = if is_tuple {
+ format!("{alias}_value_{i}")
+ } else {
+ format!("{alias}_value")
+ };
+ // The projected column is unqualified, so a left field that already
+ // has this name β however unlikely β would make the reference
+ // ambiguous.
+ while left_schema.fields().iter().any(|f| f.name() == &value_name) {
+ value_name.push('_');
+ }
+ let value_col = Column::new_unqualified(value_name);
+ let constant = std::mem::replace(value, Expr::Column(value_col.clone()));
+ projections.push(constant.alias(value_col.name()));
+ }
projected_left = Some(
LogicalPlanBuilder::from(left.clone())
.project(projections)?
.build()?,
);
- // Rebuild the `IN` equality against the projected column.
- let in_predicate = Expr::eq(Expr::Column(value_col), Expr::Column(right_col));
- join_filter = in_predicate_first(in_predicate, correlation_opt);
+ // Rebuild the `IN` equalities against the projected columns.
+ join_filter = in_predicate_first(in_values_predicate(&in_values), in_correlation);
}
let left = projected_left.as_ref().unwrap_or(left);
+ // The `IN` equalities lead the join filter, so after they are extracted
+ // into equi-join keys the first `null_aware_value_keys` keys are the
+ // `NOT IN` value keys (see `Join::null_aware_value_keys`).
+ let null_aware_value_keys = in_values.len().max(1);
Review Comment:
`V` is fixed here, before `ExtractEquijoinPredicate` runs, and nothing later checks that the first `V` keys of `on` are the tuple equalities. `split_op_and_other_join_predicates` leaves an equality in the filter when `can_hash` is false (`RunEndEncoded`, `ListView`, `Union`). A correlation key then moves into the value-key range:
```sql
CREATE TABLE co(k INT, a INT, b INT) AS VALUES (1, 1, 2), (2, 1, 2), (3, NULL, 5), (4, 7, 7), (5, 4, 2);
CREATE TABLE ci(k INT, x INT, y INT) AS VALUES (1, 1, 2), (3, NULL, 2), (3, 9, 5);
CREATE TABLE ree_o AS SELECT k, arrow_cast(a, 'RunEndEncoded("run_ends": non-null Int32, "values": Int32)') AS a, b FROM co;
CREATE TABLE ree_i AS SELECT k, arrow_cast(x, 'RunEndEncoded("run_ends": non-null Int32, "values": Int32)') AS x, y FROM ci;
SELECT * FROM ree_o WHERE (a, b) NOT IN (SELECT x, y FROM ree_i WHERE ree_i.k = ree_o.k);
-- HashJoinExec: on=[(b@2, y@1), (k@0, k@2)], filter=a@0 = x@1, null_aware(value_keys=2)
-- returns (3, NULL, 5). Expected it excluded: (NULL, 5) vs (9, 5) is UNKNOWN
```
The scalar form already has this gap on `main`. `a NOT IN (SELECT x ... WHERE ree_i.k = ree_o.k)` plans `on=[(k@0, k@1)], filter=a@0 = x@1` and returns the same wrong row. This PR widens the assumption from one key to `V`. A guard here would cover both: if any value pair fails `find_valid_equijoin_key_pair` or `can_hash`, error out (for example `not_impl_err!`) instead of building a null-aware join.
##########
datafusion/physical-plan/src/joins/hash_join/exec.rs:
##########
@@ -245,8 +256,18 @@ impl NullAwareMode {
join_type: JoinType,
partition_mode: PartitionMode,
num_keys: usize,
+ num_value_keys: usize,
has_filter: bool,
) -> Result<Self> {
+ if num_value_keys == 0 || num_value_keys > num_keys {
+ return plan_err!(
+ "null_aware {join_type} join needs between 1 and {num_keys} `NOT IN` value keys, got {num_value_keys}"
+ );
+ }
+ // `num_keys > 1` also covers a multi-column value key without any
+ // correlation: a NULL in one tuple element leaves the comparison FALSE
+ // whenever another element is a definite mismatch, so it too must be
+ // decided per build row.
let correlated = num_keys > 1 || has_filter;
Review Comment:
Sending every tuple through the correlated per-build-row path has two costs that the scalar path does not.
Memory: `can_swap_hash_join` only swaps a null-aware join with a single key, so the outer side is always the build side:
```sql
CREATE TABLE big AS SELECT CASE WHEN v < 0 THEN NULL ELSE v END AS a, v % 1000 AS b FROM generate_series(1, 2000000) t(v);
CREATE TABLE small AS SELECT CASE WHEN v < 0 THEN NULL ELSE v END AS x, v % 1000 AS y FROM generate_series(1, 100) t(v);
-- datafusion-cli --memory-limit 64M
SELECT count(*) FROM big WHERE a NOT IN (SELECT x FROM small);
-- RightAnti, builds on `small`: runs
SELECT count(*) FROM big WHERE (a, b) NOT IN (SELECT x, y FROM small);
-- LeftAnti, builds on `big`: Resources exhausted ... Failed to allocate additional 75.6 MB for HashJoinInput
```
NULLs: without correlation, every NULL-valued row is paired with every row on the other side, and each pair goes through `retain_value_mismatch_free`. Measured on this branch with `release-nonlto`, equal row counts on both sides, and no matches:
| rows per side | nullable, no NULLs | 10% NULL in subquery | 10% NULL in outer |
|---|---|---|---|
| 20k | 1 ms | 96 ms | 68 ms |
| 40k | 1 ms | 270 ms | 221 ms |
| 80k | 2 ms | 1.27 s | 1.23 s |
| 160k | 3 ms | 4.00 s | 5.15 s |
<details><summary>data</summary>
```sql
CREATE TABLE o AS SELECT v AS a, v % 1000 AS b FROM generate_series(1, N) t(v);
CREATE TABLE o_null AS SELECT CASE WHEN v % 10 = 0 THEN NULL ELSE v END AS a, v % 1000 AS b FROM generate_series(1, N) t(v);
CREATE TABLE i_null AS SELECT CASE WHEN v % 10 = 0 THEN NULL ELSE v + 10000000 END AS x, v % 1000 + 5000 AS y FROM generate_series(1, N) t(v);
CREATE TABLE i_nullable AS SELECT CASE WHEN v < 0 THEN NULL ELSE v + 10000000 END AS x, v % 1000 + 5000 AS y FROM generate_series(1, N) t(v);
SELECT count(*) FROM o WHERE (a, b) NOT IN (SELECT x, y FROM i_nullable); -- nullable, no NULLs
SELECT count(*) FROM o WHERE (a, b) NOT IN (SELECT x, y FROM i_null); -- 10% NULL in subquery
SELECT count(*) FROM o_null WHERE (a, b) NOT IN (SELECT x, y FROM i_nullable); -- 10% NULL in outer
```
</details>
The NULL-free path already beats Spark, which plans nullable multi-column `NOT IN` as a `BroadcastNestedLoopJoinExec`. The NULL path has the same shape as PostgreSQL's `findPartialMatch`, but PostgreSQL builds on the subquery side, deduplicates, and decides each outer row as it streams. A multi-key null-aware `RightAnti` could do the same. A cheaper narrowing: an element that is NOT NULL on both sides can never make the comparison UNKNOWN, so it can act as a scope key instead of a value key. Following up on my earlier benchmark note, a benchmark for both shapes would help. Follow-ups are fine if the cost is documented.
##########
datafusion/expr/src/expr.rs:
##########
@@ -1399,6 +1399,34 @@ impl InSubquery {
negated,
}
}
+
+ /// The tuple elements of a multi-column `(a, b, ...) IN (SELECT x, y, ...)`,
+ /// or `None` for a single-column `IN`. See [`in_subquery_tuple_values`].
+ pub fn tuple_values(&self) -> Option<&[Expr]> {
+ in_subquery_tuple_values(&self.expr, &self.subquery.subquery)
+ }
+}
+
+/// The tuple elements of a multi-column `(a, b, ...) IN (SELECT x, y, ...)`,
+/// given the compared expression `expr` and the `subquery` plan, or `None`
+/// for a single-column `IN`.
+///
+/// The tuple is planned as a `struct` call. It is a multi-column `IN` only when
+/// the subquery returns more than one column; against a single column the
+/// `struct` is one value compared with a struct-typed column. The number of
+/// elements is not checked against the number of subquery columns here.
+pub fn in_subquery_tuple_values<'a>(
+ expr: &'a Expr,
+ subquery: &crate::LogicalPlan,
+) -> Option<&'a [Expr]> {
+ match expr {
+ Expr::ScalarFunction(func)
+ if func.func.name() == "struct" && subquery.schema().fields().len() > 1 =>
Review Comment:
Matching on the `struct` function name breaks once `simplify_expressions` folds an all-literal tuple into a `ScalarValue::Struct` literal. On `main` this query fails in the SQL planner with "Too many columns!". Now the planner accepts it, and it fails in the optimizer:
```sql
SELECT * FROM o WHERE (1, 2) NOT IN (SELECT x, y FROM i);
-- Optimizer rule 'simplify_expressions' failed
-- Invalid (non-executable) plan after Optimizer rule: simplify_expressions
-- caused by: InSubquery should only return one column, but found 2: i.x, i.y
```
Recognizing a struct literal here as well (the return type would need to own the element exprs) fixes it. An SLT case with an all-literal tuple would cover it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132598515
π€ Criterion benchmark running (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-4132580418)
`Linux bench-c4132580418-557-qcnt8 6.12.55+ #1 SMP Sun Feb 1 08:59:41 UTC 2026 aarch64 GNU/Linux`
Comparing multi-column-null-aware-anti-join (6613da5547d3fd52ace572d9475aa07965855d04) to 4e2e4e8 (merge-base) [diff](https://github.com/apache/datafusion/compare/4e2e4e84d501b0344ac7d4212c7f3b0f0ce05dd5..6613da5547d3fd52ace572d9475aa07965855d04)
BENCH_NAME=tpc-ds
BENCH_COMMAND=cargo bench --features=parquet --bench tpc-ds
BENCH_FILTER=
Results will be posted here when complete
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132627378
π€ Benchmark running (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-4132611332)
`Linux bench-c4132611332-558-9q4bg 6.12.55+ #1 SMP Sun Feb 1 08:59:41 UTC 2026 aarch64 GNU/Linux`
Comparing multi-column-null-aware-anti-join (6613da5547d3fd52ace572d9475aa07965855d04) to 4e2e4e8 (merge-base) [diff](https://github.com/apache/datafusion/compare/4e2e4e84d501b0344ac7d4212c7f3b0f0ce05dd5..6613da5547d3fd52ace572d9475aa07965855d04) using: tpcds
Results will be posted here when complete
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6019423112
@viirya checking this again
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6029676036
Thanks all for reviewing this!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132580418
run benchmark tpc-ds
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132599209
Benchmark for [this request](https://github.com/apache/datafusion/pull/19857#issuecomment-4132580418) failed.
Last 20 lines of output:
<details><summary>Click to expand</summary>
```
stats_merge
strpos
struct_query_sql
substr
substr_index
substring
sum
to_char
to_hex
to_timestamp
topk_aggregate
topk_repartition
translate
trim
trunc
unhex
upper
uuid
window_query_sql
with_hashes
```
</details>
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r4202077201
##########
datafusion/optimizer/src/analyzer/type_coercion.rs:
##########
@@ -487,23 +487,43 @@ impl TreeNodeRewriter for TypeCoercionRewriter<'_> {
Arc::unwrap_or_clone(subquery.subquery),
)?
.data;
- let expr_type = expr.get_type(self.schema)?;
- let subquery_type = new_plan.schema().field(0).data_type();
- let common_type = comparison_coercion(&expr_type, subquery_type).ok_or(
- plan_datafusion_err!(
- "expr type {expr_type} can't cast to {subquery_type} in InSubquery"
- ),
- )?;
- let new_subquery = Subquery {
- subquery: Arc::new(new_plan),
- outer_ref_columns: subquery.outer_ref_columns,
- spans: subquery.spans,
- };
- Ok(Transformed::yes(Expr::InSubquery(InSubquery::new(
- Box::new(expr.cast_to(&common_type, self.schema)?),
- cast_subquery(new_subquery, &common_type)?,
- negated,
- ))))
+
+ // Check if this is a multi-column IN (struct expression)
+ let is_struct = matches!(*expr, Expr::ScalarFunction(ref func) if func.func.name() == "struct");
+
+ if is_struct {
+ // For multi-column IN, we don't need type coercion at this level
Review Comment:
This one is out of date: the multi-column form now coerces each tuple element against its subquery column, the same way the single-column form coerces its one pair (`coerce_multi_column_in_subquery`, which also applies the session time zone).
##########
datafusion/expr/src/logical_plan/invariants.rs:
##########
@@ -224,14 +224,31 @@ pub fn check_subquery_expr(
check_correlations_in_subquery(inner_plan)
} else {
if let Expr::InSubquery(subquery) = expr {
- // InSubquery should only return one column
- if subquery.subquery.subquery.schema().fields().len() > 1 {
+ // InSubquery should only return one column UNLESS the left expression is a struct
+ // (multi-column IN like: (a, b) NOT IN (SELECT x, y FROM ...))
+ let is_struct = matches!(*subquery.expr, Expr::ScalarFunction(ref func) if func.func.name() == "struct");
Review Comment:
Agreed. The name check now lives in one place, `in_subquery_tuple_values`, which every caller goes through (SQL planning, invariants, type coercion, nullability, placeholder inference and decorrelation). It also recognizes the struct literal that `simplify_expressions` folds an all-literal tuple into. Making the tuple explicit in the type system, for example as a dedicated field on `InSubquery`, would be cleaner but changes a public expression type.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5901572341
π€ Benchmark completed (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-5901079308)
**Instance:** `c4a-highmem-16` (12 vCPU / 65 GiB)
Comparing multi-column-null-aware-anti-join (295c0561392e9fe23a401b3c59c37ceeba1f829f) to 696eaf5 (merge-base) [diff](https://github.com/apache/datafusion/compare/696eaf58d436faa6532aa0f5875b5a120b94d0eb..295c0561392e9fe23a401b3c59c37ceeba1f829f)
<details><summary>Run configuration</summary>
```yaml
run benchmark tpcds
```
</details>
<details><summary>CPU Details (lscpu)</summary>
```
Architecture: aarch64
CPU op-mode(s): 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Vendor ID: ARM
Model name: Neoverse-V2
Model: 1
Thread(s) per core: 1
Core(s) per cluster: 16
Socket(s): -
Cluster(s): 1
Stepping: r0p1
BogoMIPS: 2000.00
Flags: fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm jscvt fcma lrcpc dcpop sha3 sm3 sm4 asimddp sha512 sve asimdfhm dit uscat ilrcpc flagm sb paca pacg dcpodp sve2 sveaes svepmull svebitperm svesha3 svesm4 flagm2 frint svei8mm svebf16 i8mm bf16 dgh rng bti
L1d cache: 1 MiB (16 instances)
L1i cache: 1 MiB (16 instances)
L2 cache: 32 MiB (16 instances)
L3 cache: 80 MiB (1 instance)
NUMA node(s): 1
NUMA node0 CPU(s): 0-15
Vulnerability Gather data sampling: Not affected
Vulnerability Indirect target selection: Not affected
Vulnerability Itlb multihit: Not affected
Vulnerability L1tf: Not affected
Vulnerability Mds: Not affected
Vulnerability Meltdown: Not affected
Vulnerability Mmio stale data: Not affected
Vulnerability Reg file data sampling: Not affected
Vulnerability Retbleed: Not affected
Vulnerability Spec rstack overflow: Not affected
Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Vulnerability Spectre v1: Mitigation; __user pointer sanitization
Vulnerability Spectre v2: Mitigation; CSV2, BHB
Vulnerability Srbds: Not affected
Vulnerability Tsa: Not affected
Vulnerability Tsx async abort: Not affected
Vulnerability Vmscape: Not affected
```
</details>
<details><summary>Details</summary>
<p>
```
Comparing HEAD and multi-column-null-aware-anti-join
--------------------
Benchmark tpcds_sf1.json
--------------------
βββββββββββββ³βββββββββββββ³ββββββββββββββββββββββββββββββββββββ³βββββββββββββββ
β Query β HEAD β multi-column-null-aware-anti-join β Change β
β‘ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β QQuery 1 β 5.58 ms β 5.90 ms β 1.06x slower β
β QQuery 2 β 80.94 ms β 80.60 ms β no change β
β QQuery 3 β 29.18 ms β 28.97 ms β no change β
β QQuery 4 β 466.81 ms β 473.23 ms β no change β
β QQuery 5 β 52.03 ms β 51.74 ms β no change β
β QQuery 6 β 35.96 ms β 36.27 ms β no change β
β QQuery 7 β 73.96 ms β 74.09 ms β no change β
β QQuery 8 β 36.49 ms β 36.46 ms β no change β
β QQuery 9 β 52.55 ms β 53.94 ms β no change β
β QQuery 10 β 61.98 ms β 61.40 ms β no change β
β QQuery 11 β 293.06 ms β 291.31 ms β no change β
β QQuery 12 β 29.23 ms β 28.96 ms β no change β
β QQuery 13 β 117.84 ms β 117.83 ms β no change β
β QQuery 14 β 396.28 ms β 394.55 ms β no change β
β QQuery 15 β 56.78 ms β 55.99 ms β no change β
β QQuery 16 β 6.67 ms β 6.49 ms β no change β
β QQuery 17 β 79.23 ms β 78.13 ms β no change β
β QQuery 18 β 104.93 ms β 104.74 ms β no change β
β QQuery 19 β 41.28 ms β 42.46 ms β no change β
β QQuery 20 β 36.04 ms β 35.81 ms β no change β
β QQuery 21 β 17.34 ms β 17.51 ms β no change β
β QQuery 22 β 64.50 ms β 65.25 ms β no change β
β QQuery 23 β 321.07 ms β 318.59 ms β no change β
β QQuery 24 β 199.28 ms β 199.22 ms β no change β
β QQuery 25 β 107.34 ms β 108.21 ms β no change β
β QQuery 26 β 48.47 ms β 48.72 ms β no change β
β QQuery 27 β 6.36 ms β 6.28 ms β no change β
β QQuery 28 β 56.51 ms β 58.45 ms β no change β
β QQuery 29 β 95.18 ms β 95.62 ms β no change β
β QQuery 30 β 32.99 ms β 33.33 ms β no change β
β QQuery 31 β 109.78 ms β 110.59 ms β no change β
β QQuery 32 β 20.87 ms β 20.91 ms β no change β
β QQuery 33 β 37.47 ms β 37.69 ms β no change β
β QQuery 34 β 10.38 ms β 10.34 ms β no change β
β QQuery 35 β 73.65 ms β 74.65 ms β no change β
β QQuery 36 β 6.16 ms β 5.85 ms β no change β
β QQuery 37 β 7.08 ms β 7.11 ms β no change β
β QQuery 38 β 63.21 ms β 63.88 ms β no change β
β QQuery 39 β 75.03 ms β 75.95 ms β no change β
β QQuery 40 β 24.83 ms β 24.21 ms β no change β
β QQuery 41 β 11.86 ms β 11.90 ms β no change β
β QQuery 42 β 24.10 ms β 24.09 ms β no change β
β QQuery 43 β 5.37 ms β 5.31 ms β no change β
β QQuery 44 β 9.55 ms β 9.62 ms β no change β
β QQuery 45 β 40.12 ms β 40.12 ms β no change β
β QQuery 46 β 12.20 ms β 12.49 ms β no change β
β QQuery 47 β 232.51 ms β 230.78 ms β no change β
β QQuery 48 β 95.86 ms β 95.84 ms β no change β
β QQuery 49 β 71.10 ms β 70.79 ms β no change β
β QQuery 50 β 58.59 ms β 58.08 ms β no change β
β QQuery 51 β 93.28 ms β 92.52 ms β no change β
β QQuery 52 β 24.41 ms β 26.13 ms β 1.07x slower β
β QQuery 53 β 29.49 ms β 29.38 ms β no change β
β QQuery 54 β 54.60 ms β 54.51 ms β no change β
β QQuery 55 β 23.75 ms β 23.79 ms β no change β
β QQuery 56 β 39.10 ms β 38.43 ms β no change β
β QQuery 57 β 169.17 ms β 169.69 ms β no change β
β QQuery 58 β 109.97 ms β 109.47 ms β no change β
β QQuery 59 β 116.72 ms β 116.51 ms β no change β
β QQuery 60 β 39.31 ms β 39.19 ms β no change β
β QQuery 61 β 11.87 ms β 11.97 ms β no change β
β QQuery 62 β 44.68 ms β 45.24 ms β no change β
β QQuery 63 β 29.65 ms β 29.81 ms β no change β
β QQuery 64 β 365.35 ms β 368.69 ms β no change β
β QQuery 65 β 130.61 ms β 132.23 ms β no change β
β QQuery 66 β 77.36 ms β 77.88 ms β no change β
β QQuery 67 β 258.25 ms β 260.60 ms β no change β
β QQuery 68 β 12.24 ms β 12.20 ms β no change β
β QQuery 69 β 57.20 ms β 57.48 ms β no change β
β QQuery 70 β 104.55 ms β 105.89 ms β no change β
β QQuery 71 β 35.53 ms β 35.43 ms β no change β
β QQuery 72 β 1830.92 ms β 1814.12 ms β no change β
β QQuery 73 β 10.18 ms β 10.17 ms β no change β
β QQuery 74 β 169.50 ms β 174.00 ms β no change β
β QQuery 75 β 142.65 ms β 143.38 ms β no change β
β QQuery 76 β 35.39 ms β 34.16 ms β no change β
β QQuery 77 β 60.90 ms β 62.20 ms β no change β
β QQuery 78 β 163.97 ms β 164.63 ms β no change β
β QQuery 79 β 66.77 ms β 67.05 ms β no change β
β QQuery 80 β 97.80 ms β 96.14 ms β no change β
β QQuery 81 β 26.62 ms β 26.37 ms β no change β
β QQuery 82 β 16.91 ms β 17.23 ms β no change β
β QQuery 83 β 34.27 ms β 34.29 ms β no change β
β QQuery 84 β 29.97 ms β 30.31 ms β no change β
β QQuery 85 β 104.39 ms β 103.66 ms β no change β
β QQuery 86 β 26.26 ms β 26.00 ms β no change β
β QQuery 87 β 63.91 ms β 64.30 ms β no change β
β QQuery 88 β 61.73 ms β 62.09 ms β no change β
β QQuery 89 β 36.05 ms β 35.63 ms β no change β
β QQuery 90 β 17.13 ms β 17.14 ms β no change β
β QQuery 91 β 44.74 ms β 45.93 ms β no change β
β QQuery 92 β 29.98 ms β 30.78 ms β no change β
β QQuery 93 β 49.44 ms β 49.18 ms β no change β
β QQuery 94 β 39.13 ms β 38.98 ms β no change β
β QQuery 95 β 80.22 ms β 79.99 ms β no change β
β QQuery 96 β 24.21 ms β 24.09 ms β no change β
β QQuery 97 β 51.17 ms β 51.36 ms β no change β
β QQuery 98 β 43.37 ms β 43.74 ms β no change β
β QQuery 99 β 65.90 ms β 66.42 ms β no change β
βββββββββββββ΄βββββββββββββ΄ββββββββββββββββββββββββββββββββββββ΄βββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββ³ββββββββββββ
β Benchmark Summary β β
β‘βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β Total Time (HEAD) β 9246.15ms β
β Total Time (multi-column-null-aware-anti-join) β 9250.60ms β
β Average Time (HEAD) β 93.40ms β
β Average Time (multi-column-null-aware-anti-join) β 93.44ms β
β Queries Faster β 0 β
β Queries Slower β 2 β
β Queries with No Change β 97 β
β Queries with Failure β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββ
Distribution per query (min / mean Β±stddev / max):
Comparing HEAD and multi-column-null-aware-anti-join
--------------------
Benchmark tpcds_sf1.json
--------------------
βββββββββββββ³ββββββββββββββββββββββββββββββββββββββββ³ββββββββββββββββββββββββββββββββββββββββ³ββββββββββββββββ
β Query β HEAD β multi-column-null-aware-anti-join β Change β
β‘ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β QQuery 1 β 5.58 / 6.24 Β±0.96 / 8.14 ms β 5.90 / 6.39 Β±0.85 / 8.09 ms β no change β
β QQuery 2 β 80.94 / 81.19 Β±0.17 / 81.37 ms β 80.60 / 81.05 Β±0.31 / 81.37 ms β no change β
β QQuery 3 β 29.18 / 29.35 Β±0.14 / 29.55 ms β 28.97 / 29.34 Β±0.29 / 29.61 ms β no change β
β QQuery 4 β 466.81 / 476.10 Β±11.68 / 498.95 ms β 473.23 / 475.47 Β±1.23 / 476.80 ms β no change β
β QQuery 5 β 52.03 / 53.57 Β±2.59 / 58.72 ms β 51.74 / 53.72 Β±2.79 / 59.10 ms β no change β
β QQuery 6 β 35.96 / 36.69 Β±0.40 / 37.12 ms β 36.27 / 36.48 Β±0.22 / 36.87 ms β no change β
β QQuery 7 β 73.96 / 74.96 Β±0.71 / 75.80 ms β 74.09 / 74.59 Β±0.33 / 75.05 ms β no change β
β QQuery 8 β 36.49 / 36.77 Β±0.21 / 37.03 ms β 36.46 / 36.76 Β±0.29 / 37.22 ms β no change β
β QQuery 9 β 52.55 / 53.63 Β±0.90 / 55.12 ms β 53.94 / 54.83 Β±1.03 / 56.80 ms β no change β
β QQuery 10 β 61.98 / 62.15 Β±0.19 / 62.49 ms β 61.40 / 62.20 Β±0.56 / 63.13 ms β no change β
β QQuery 11 β 293.06 / 294.59 Β±1.47 / 297.03 ms β 291.31 / 294.19 Β±1.92 / 297.29 ms β no change β
β QQuery 12 β 29.23 / 29.50 Β±0.19 / 29.79 ms β 28.96 / 29.42 Β±0.29 / 29.82 ms β no change β
β QQuery 13 β 117.84 / 118.62 Β±0.52 / 119.20 ms β 117.83 / 118.84 Β±0.57 / 119.53 ms β no change β
β QQuery 14 β 396.28 / 401.13 Β±2.98 / 404.27 ms β 394.55 / 400.31 Β±4.44 / 405.79 ms β no change β
β QQuery 15 β 56.78 / 60.92 Β±5.78 / 71.93 ms β 55.99 / 56.75 Β±0.84 / 58.31 ms β +1.07x faster β
β QQuery 16 β 6.67 / 6.89 Β±0.19 / 7.22 ms β 6.49 / 6.69 Β±0.15 / 6.89 ms β no change β
β QQuery 17 β 79.23 / 80.39 Β±1.35 / 82.98 ms β 78.13 / 79.93 Β±1.46 / 82.60 ms β no change β
β QQuery 18 β 104.93 / 106.64 Β±1.18 / 108.26 ms β 104.74 / 105.26 Β±0.49 / 106.01 ms β no change β
β QQuery 19 β 41.28 / 41.89 Β±0.33 / 42.23 ms β 42.46 / 42.66 Β±0.24 / 43.08 ms β no change β
β QQuery 20 β 36.04 / 36.47 Β±0.63 / 37.71 ms β 35.81 / 36.59 Β±0.67 / 37.83 ms β no change β
β QQuery 21 β 17.34 / 17.56 Β±0.19 / 17.90 ms β 17.51 / 17.66 Β±0.14 / 17.90 ms β no change β
β QQuery 22 β 64.50 / 65.99 Β±0.76 / 66.60 ms β 65.25 / 65.83 Β±0.39 / 66.45 ms β no change β
β QQuery 23 β 321.07 / 323.48 Β±1.91 / 325.25 ms β 318.59 / 322.27 Β±3.71 / 329.28 ms β no change β
β QQuery 24 β 199.28 / 202.38 Β±2.40 / 206.60 ms β 199.22 / 202.57 Β±2.00 / 205.43 ms β no change β
β QQuery 25 β 107.34 / 108.59 Β±1.16 / 110.74 ms β 108.21 / 112.31 Β±6.06 / 124.35 ms β no change β
β QQuery 26 β 48.47 / 48.86 Β±0.38 / 49.55 ms β 48.72 / 49.36 Β±0.63 / 50.43 ms β no change β
β QQuery 27 β 6.36 / 6.70 Β±0.20 / 6.95 ms β 6.28 / 6.41 Β±0.11 / 6.55 ms β no change β
β QQuery 28 β 56.51 / 59.59 Β±2.23 / 61.76 ms β 58.45 / 61.00 Β±1.29 / 62.00 ms β no change β
β QQuery 29 β 95.18 / 96.88 Β±1.45 / 98.78 ms β 95.62 / 100.13 Β±6.73 / 113.52 ms β no change β
β QQuery 30 β 32.99 / 33.43 Β±0.23 / 33.62 ms β 33.33 / 33.72 Β±0.32 / 34.23 ms β no change β
β QQuery 31 β 109.78 / 112.05 Β±2.78 / 117.21 ms β 110.59 / 113.49 Β±3.51 / 120.38 ms β no change β
β QQuery 32 β 20.87 / 21.11 Β±0.18 / 21.33 ms β 20.91 / 21.05 Β±0.14 / 21.30 ms β no change β
β QQuery 33 β 37.47 / 37.84 Β±0.30 / 38.37 ms β 37.69 / 38.61 Β±0.81 / 40.02 ms β no change β
β QQuery 34 β 10.38 / 10.61 Β±0.24 / 11.08 ms β 10.34 / 10.74 Β±0.28 / 11.04 ms β no change β
β QQuery 35 β 73.65 / 74.53 Β±0.94 / 76.30 ms β 74.65 / 75.38 Β±0.81 / 76.85 ms β no change β
β QQuery 36 β 6.16 / 6.32 Β±0.14 / 6.54 ms β 5.85 / 6.05 Β±0.18 / 6.38 ms β no change β
β QQuery 37 β 7.08 / 7.26 Β±0.16 / 7.53 ms β 7.11 / 7.35 Β±0.16 / 7.59 ms β no change β
β QQuery 38 β 63.21 / 64.13 Β±0.93 / 65.72 ms β 63.88 / 65.24 Β±1.95 / 69.00 ms β no change β
β QQuery 39 β 75.03 / 76.01 Β±0.71 / 77.16 ms β 75.95 / 77.00 Β±1.28 / 79.46 ms β no change β
β QQuery 40 β 24.83 / 25.02 Β±0.19 / 25.32 ms β 24.21 / 24.75 Β±0.36 / 25.09 ms β no change β
β QQuery 41 β 11.86 / 12.08 Β±0.13 / 12.24 ms β 11.90 / 12.12 Β±0.18 / 12.44 ms β no change β
β QQuery 42 β 24.10 / 24.73 Β±0.45 / 25.48 ms β 24.09 / 24.66 Β±0.60 / 25.64 ms β no change β
β QQuery 43 β 5.37 / 5.46 Β±0.13 / 5.73 ms β 5.31 / 5.44 Β±0.15 / 5.72 ms β no change β
β QQuery 44 β 9.55 / 9.68 Β±0.09 / 9.81 ms β 9.62 / 11.31 Β±3.07 / 17.44 ms β 1.17x slower β
β QQuery 45 β 40.12 / 40.89 Β±0.69 / 42.07 ms β 40.12 / 41.02 Β±0.62 / 41.71 ms β no change β
β QQuery 46 β 12.20 / 12.57 Β±0.35 / 13.18 ms β 12.49 / 12.90 Β±0.34 / 13.34 ms β no change β
β QQuery 47 β 232.51 / 238.16 Β±4.52 / 244.48 ms β 230.78 / 241.81 Β±7.76 / 253.73 ms β no change β
β QQuery 48 β 95.86 / 96.57 Β±0.45 / 97.27 ms β 95.84 / 96.40 Β±0.35 / 96.83 ms β no change β
β QQuery 49 β 71.10 / 71.57 Β±0.47 / 72.41 ms β 70.79 / 72.12 Β±1.85 / 75.75 ms β no change β
β QQuery 50 β 58.59 / 59.87 Β±1.91 / 63.64 ms β 58.08 / 59.76 Β±2.15 / 63.94 ms β no change β
β QQuery 51 β 93.28 / 95.87 Β±3.85 / 103.29 ms β 92.52 / 97.62 Β±6.34 / 109.90 ms β no change β
β QQuery 52 β 24.41 / 24.78 Β±0.50 / 25.75 ms β 26.13 / 26.63 Β±0.51 / 27.33 ms β 1.07x slower β
β QQuery 53 β 29.49 / 29.61 Β±0.13 / 29.83 ms β 29.38 / 30.81 Β±0.74 / 31.39 ms β no change β
β QQuery 54 β 54.60 / 55.14 Β±0.64 / 56.34 ms β 54.51 / 54.87 Β±0.42 / 55.69 ms β no change β
β QQuery 55 β 23.75 / 24.06 Β±0.25 / 24.37 ms β 23.79 / 24.11 Β±0.31 / 24.51 ms β no change β
β QQuery 56 β 39.10 / 39.38 Β±0.20 / 39.63 ms β 38.43 / 39.09 Β±0.43 / 39.59 ms β no change β
β QQuery 57 β 169.17 / 172.29 Β±3.03 / 177.89 ms β 169.69 / 172.96 Β±2.74 / 177.19 ms β no change β
β QQuery 58 β 109.97 / 111.33 Β±1.00 / 112.68 ms β 109.47 / 111.63 Β±1.75 / 114.82 ms β no change β
β QQuery 59 β 116.72 / 117.40 Β±0.77 / 118.79 ms β 116.51 / 117.22 Β±0.42 / 117.82 ms β no change β
β QQuery 60 β 39.31 / 40.36 Β±1.18 / 42.58 ms β 39.19 / 39.50 Β±0.23 / 39.77 ms β no change β
β QQuery 61 β 11.87 / 11.98 Β±0.09 / 12.08 ms β 11.97 / 12.10 Β±0.11 / 12.28 ms β no change β
β QQuery 62 β 44.68 / 45.66 Β±1.18 / 47.97 ms β 45.24 / 45.78 Β±0.83 / 47.42 ms β no change β
β QQuery 63 β 29.65 / 30.09 Β±0.31 / 30.40 ms β 29.81 / 30.28 Β±0.48 / 31.11 ms β no change β
β QQuery 64 β 365.35 / 368.33 Β±2.01 / 371.43 ms β 368.69 / 370.73 Β±1.78 / 372.88 ms β no change β
β QQuery 65 β 130.61 / 135.18 Β±2.57 / 138.58 ms β 132.23 / 137.03 Β±3.16 / 141.38 ms β no change β
β QQuery 66 β 77.36 / 77.70 Β±0.44 / 78.55 ms β 77.88 / 79.88 Β±2.80 / 85.39 ms β no change β
β QQuery 67 β 258.25 / 265.77 Β±6.46 / 274.59 ms β 260.60 / 262.88 Β±3.61 / 270.07 ms β no change β
β QQuery 68 β 12.24 / 12.33 Β±0.09 / 12.50 ms β 12.20 / 12.47 Β±0.20 / 12.69 ms β no change β
β QQuery 69 β 57.20 / 60.08 Β±5.39 / 70.86 ms β 57.48 / 60.64 Β±4.84 / 70.22 ms β no change β
β QQuery 70 β 104.55 / 106.54 Β±1.70 / 109.40 ms β 105.89 / 108.07 Β±1.94 / 110.45 ms β no change β
β QQuery 71 β 35.53 / 36.00 Β±0.43 / 36.66 ms β 35.43 / 35.97 Β±0.30 / 36.28 ms β no change β
β QQuery 72 β 1830.92 / 1901.74 Β±46.05 / 1953.53 ms β 1814.12 / 1839.72 Β±19.92 / 1865.70 ms β no change β
β QQuery 73 β 10.18 / 10.53 Β±0.31 / 11.07 ms β 10.17 / 11.10 Β±0.68 / 11.88 ms β 1.05x slower β
β QQuery 74 β 169.50 / 173.56 Β±2.66 / 177.00 ms β 174.00 / 176.73 Β±2.07 / 180.24 ms β no change β
β QQuery 75 β 142.65 / 143.75 Β±1.11 / 145.85 ms β 143.38 / 145.41 Β±2.01 / 148.83 ms β no change β
β QQuery 76 β 35.39 / 37.86 Β±3.56 / 44.89 ms β 34.16 / 34.79 Β±0.46 / 35.50 ms β +1.09x faster β
β QQuery 77 β 60.90 / 61.40 Β±0.30 / 61.64 ms β 62.20 / 62.64 Β±0.38 / 63.08 ms β no change β
β QQuery 78 β 163.97 / 169.22 Β±5.40 / 177.60 ms β 164.63 / 169.51 Β±5.09 / 178.87 ms β no change β
β QQuery 79 β 66.77 / 67.74 Β±0.63 / 68.62 ms β 67.05 / 67.80 Β±0.54 / 68.73 ms β no change β
β QQuery 80 β 97.80 / 100.66 Β±3.62 / 107.58 ms β 96.14 / 97.15 Β±0.95 / 98.93 ms β no change β
β QQuery 81 β 26.62 / 26.70 Β±0.08 / 26.84 ms β 26.37 / 28.79 Β±3.97 / 36.71 ms β 1.08x slower β
β QQuery 82 β 16.91 / 17.20 Β±0.20 / 17.54 ms β 17.23 / 17.28 Β±0.05 / 17.37 ms β no change β
β QQuery 83 β 34.27 / 34.78 Β±0.42 / 35.52 ms β 34.29 / 34.51 Β±0.22 / 34.91 ms β no change β
β QQuery 84 β 29.97 / 30.17 Β±0.16 / 30.42 ms β 30.31 / 31.25 Β±1.44 / 34.13 ms β no change β
β QQuery 85 β 104.39 / 108.66 Β±6.79 / 122.11 ms β 103.66 / 104.61 Β±0.57 / 105.27 ms β no change β
β QQuery 86 β 26.26 / 26.76 Β±0.26 / 27.03 ms β 26.00 / 29.13 Β±5.78 / 40.68 ms β 1.09x slower β
β QQuery 87 β 63.91 / 65.05 Β±0.95 / 66.52 ms β 64.30 / 65.11 Β±0.79 / 66.46 ms β no change β
β QQuery 88 β 61.73 / 62.18 Β±0.40 / 62.85 ms β 62.09 / 62.83 Β±0.45 / 63.30 ms β no change β
β QQuery 89 β 36.05 / 36.45 Β±0.41 / 37.21 ms β 35.63 / 35.81 Β±0.18 / 36.14 ms β no change β
β QQuery 90 β 17.13 / 17.30 Β±0.14 / 17.50 ms β 17.14 / 19.32 Β±4.05 / 27.41 ms β 1.12x slower β
β QQuery 91 β 44.74 / 45.63 Β±0.98 / 47.47 ms β 45.93 / 46.47 Β±0.48 / 47.35 ms β no change β
β QQuery 92 β 29.98 / 30.54 Β±0.52 / 31.38 ms β 30.78 / 31.91 Β±1.42 / 34.68 ms β no change β
β QQuery 93 β 49.44 / 49.88 Β±0.44 / 50.74 ms β 49.18 / 50.73 Β±0.95 / 51.68 ms β no change β
β QQuery 94 β 39.13 / 40.68 Β±1.79 / 44.16 ms β 38.98 / 39.57 Β±0.33 / 39.89 ms β no change β
β QQuery 95 β 80.22 / 81.72 Β±1.75 / 84.67 ms β 79.99 / 82.39 Β±2.70 / 87.42 ms β no change β
β QQuery 96 β 24.21 / 24.35 Β±0.08 / 24.44 ms β 24.09 / 24.61 Β±0.85 / 26.31 ms β no change β
β QQuery 97 β 51.17 / 52.37 Β±1.33 / 54.95 ms β 51.36 / 52.56 Β±1.30 / 55.04 ms β no change β
β QQuery 98 β 43.37 / 45.01 Β±2.90 / 50.80 ms β 43.74 / 44.00 Β±0.40 / 44.80 ms β no change β
β QQuery 99 β 65.90 / 66.19 Β±0.31 / 66.70 ms β 66.42 / 66.70 Β±0.20 / 66.91 ms β no change β
βββββββββββββ΄ββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββββββ
ββββββββββββββββββββββββββββββββββββββββββββββββββββ³ββββββββββββ
β Benchmark Summary β β
β‘βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ©
β Total Time (HEAD) β 9445.56ms β
β Total Time (multi-column-null-aware-anti-join) β 9412.67ms β
β Average Time (HEAD) β 95.41ms β
β Average Time (multi-column-null-aware-anti-join) β 95.08ms β
β Queries Faster β 2 β
β Queries Slower β 6 β
β Queries with No Change β 91 β
β Queries with Failure β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ΄ββββββββββββ
```
</p>
</details>
<details><summary>Resource Usage</summary>
**tpcds β base (merge-base)**
| Metric | Value |
|--------|-------|
| Wall time | 50.0s |
| Peak memory | 1.9 GiB |
| Avg memory | 1.4 GiB |
| CPU user | 207.3s |
| CPU sys | 5.2s |
| Peak spill | 0 B |
**tpcds β branch**
| Metric | Value |
|--------|-------|
| Wall time | 50.0s |
| Peak memory | 2.1 GiB |
| Avg memory | 1.4 GiB |
| CPU user | 206.7s |
| CPU sys | 5.5s |
| Peak spill | 0 B |
</details>
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5901519259
π€ Benchmark running (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-5901079308)
**Instance:** `c4a-highmem-16` (12 vCPU / 65 GiB) | `Linux bench-c5901079308-2931-rwznz 6.12.94+ #1 SMP Wed Aug 19 07:47:20 UTC 2026 aarch64 GNU/Linux`
<details><summary>CPU Details (lscpu)</summary>
```
Architecture: aarch64
CPU op-mode(s): 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Vendor ID: ARM
Model name: Neoverse-V2
Model: 1
Thread(s) per core: 1
Core(s) per cluster: 16
Socket(s): -
Cluster(s): 1
Stepping: r0p1
BogoMIPS: 2000.00
Flags: fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm jscvt fcma lrcpc dcpop sha3 sm3 sm4 asimddp sha512 sve asimdfhm dit uscat ilrcpc flagm sb paca pacg dcpodp sve2 sveaes svepmull svebitperm svesha3 svesm4 flagm2 frint svei8mm svebf16 i8mm bf16 dgh rng bti
L1d cache: 1 MiB (16 instances)
L1i cache: 1 MiB (16 instances)
L2 cache: 32 MiB (16 instances)
L3 cache: 80 MiB (1 instance)
NUMA node(s): 1
NUMA node0 CPU(s): 0-15
Vulnerability Gather data sampling: Not affected
Vulnerability Indirect target selection: Not affected
Vulnerability Itlb multihit: Not affected
Vulnerability L1tf: Not affected
Vulnerability Mds: Not affected
Vulnerability Meltdown: Not affected
Vulnerability Mmio stale data: Not affected
Vulnerability Reg file data sampling: Not affected
Vulnerability Retbleed: Not affected
Vulnerability Spec rstack overflow: Not affected
Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Vulnerability Spectre v1: Mitigation; __user pointer sanitization
Vulnerability Spectre v2: Mitigation; CSV2, BHB
Vulnerability Srbds: Not affected
Vulnerability Tsa: Not affected
Vulnerability Tsx async abort: Not affected
Vulnerability Vmscape: Not affected
```
</details>
Comparing multi-column-null-aware-anti-join (295c0561392e9fe23a401b3c59c37ceeba1f829f) to 696eaf5 (merge-base) [diff](https://github.com/apache/datafusion/compare/696eaf58d436faa6532aa0f5875b5a120b94d0eb..295c0561392e9fe23a401b3c59c37ceeba1f829f)
<details><summary>Run configuration</summary>
```yaml
run benchmark tpcds
```
</details>
Results will be posted here when complete
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r4109556476
##########
datafusion/physical-plan/src/joins/hash_join/stream.rs:
##########
@@ -1541,6 +1556,58 @@ fn mark_null_candidates_for_probe_batch(
Ok(())
}
+/// Keeps the candidate pairs whose multi-column `NOT IN` value tuples are not a
+/// definite mismatch, i.e. every element pair is equal or involves a NULL.
+///
+/// Such a pair compares UNKNOWN when some element is NULL (TRUE pairs, with no
+/// NULL, are found by the hash lookup instead), whereas a pair with a definite
+/// mismatch compares FALSE whatever its NULLs are: `(NULL, 1) = (2, 3)` is
+/// `UNKNOWN AND FALSE`, which is FALSE.
+fn retain_value_mismatch_free(
+ build_value_keys: &[ArrayRef],
+ probe_value_keys: &[ArrayRef],
+ build_indices: UInt64Array,
+ probe_indices: UInt32Array,
+) -> Result<(UInt64Array, UInt32Array)> {
+ if build_indices.is_empty() {
+ return Ok((build_indices, probe_indices));
+ }
+ let comparators = build_value_keys
+ .iter()
+ .zip(probe_value_keys)
+ .map(|(build, probe)| {
+ Ok((
+ build.logical_nulls(),
+ probe.logical_nulls(),
+ // Same total order the hash join's own key comparison uses.
Review Comment:
Good catch, all three queries reproduced. Fixed in 8728b2c by taking your batch-level suggestion: each chunk of candidate pairs is now compared with `take` on both sides plus `datum::apply_cmp(Operator::Eq)`, so the UNKNOWN check uses the same float-normalizing `=` as SQL (nested types included), and the work is proportional to the chunk rather than a scan of the full build arrays. Your three queries are now in `null_aware_multi_column.slt`.
##########
datafusion/optimizer/src/decorrelate_predicate_subquery.rs:
##########
@@ -613,34 +666,54 @@ fn build_join(
// Projecting the constant as a column of the outer side turns the predicate
// into a real equi-join key, which fixes all three. It only pays for itself
// on a join that ends up null-aware.
+ //
+ // Every constant element of a tuple is projected the same way, so that all
+ // value keys lead the equi-join keys in order.
let mut projected_left = None;
- if let Some((value, right_col, mut value_name, correlation_opt)) = in_value_expr
- && value.column_refs().is_empty()
- && null_aware
+ if null_aware
+ && in_values
+ .iter()
+ .any(|(value, _)| value.column_refs().is_empty())
{
- // The projected column is unqualified, so a left field that already has
- // this name β however unlikely β would make the reference ambiguous.
let left_schema = left.schema();
- while left_schema.fields().iter().any(|f| f.name() == &value_name) {
- value_name.push('_');
- }
- let value_col = Column::new_unqualified(value_name);
- let projections = left_schema
+ let mut projections = left_schema
.columns()
.into_iter()
.map(Expr::from)
- .chain(std::iter::once(value.alias(value_col.name())))
.collect::<Vec<_>>();
+ let is_tuple = in_values.len() > 1;
+ for (i, (value, _)) in in_values.iter_mut().enumerate() {
+ if !value.column_refs().is_empty() {
+ continue;
+ }
+ let mut value_name = if is_tuple {
+ format!("{alias}_value_{i}")
+ } else {
+ format!("{alias}_value")
+ };
+ // The projected column is unqualified, so a left field that already
+ // has this name β however unlikely β would make the reference
+ // ambiguous.
+ while left_schema.fields().iter().any(|f| f.name() == &value_name) {
+ value_name.push('_');
+ }
+ let value_col = Column::new_unqualified(value_name);
+ let constant = std::mem::replace(value, Expr::Column(value_col.clone()));
+ projections.push(constant.alias(value_col.name()));
+ }
projected_left = Some(
LogicalPlanBuilder::from(left.clone())
.project(projections)?
.build()?,
);
- // Rebuild the `IN` equality against the projected column.
- let in_predicate = Expr::eq(Expr::Column(value_col), Expr::Column(right_col));
- join_filter = in_predicate_first(in_predicate, correlation_opt);
+ // Rebuild the `IN` equalities against the projected columns.
+ join_filter = in_predicate_first(in_values_predicate(&in_values), in_correlation);
}
let left = projected_left.as_ref().unwrap_or(left);
+ // The `IN` equalities lead the join filter, so after they are extracted
+ // into equi-join keys the first `null_aware_value_keys` keys are the
+ // `NOT IN` value keys (see `Join::null_aware_value_keys`).
+ let null_aware_value_keys = in_values.len().max(1);
Review Comment:
Agreed. `build_join` now checks, before building a null-aware join, that every `NOT IN` value pair passes `find_valid_equijoin_key_pair` and `can_hash` on both sides, and returns `not_impl_err!` otherwise. Since the check runs on the value pairs regardless of their count, it also closes the scalar gap you found on `main`. Both the tuple and the scalar RunEndEncoded queries are covered in `null_aware_multi_column.slt`.
##########
datafusion/expr/src/expr.rs:
##########
@@ -1399,6 +1399,34 @@ impl InSubquery {
negated,
}
}
+
+ /// The tuple elements of a multi-column `(a, b, ...) IN (SELECT x, y, ...)`,
+ /// or `None` for a single-column `IN`. See [`in_subquery_tuple_values`].
+ pub fn tuple_values(&self) -> Option<&[Expr]> {
+ in_subquery_tuple_values(&self.expr, &self.subquery.subquery)
+ }
+}
+
+/// The tuple elements of a multi-column `(a, b, ...) IN (SELECT x, y, ...)`,
+/// given the compared expression `expr` and the `subquery` plan, or `None`
+/// for a single-column `IN`.
+///
+/// The tuple is planned as a `struct` call. It is a multi-column `IN` only when
+/// the subquery returns more than one column; against a single column the
+/// `struct` is one value compared with a struct-typed column. The number of
+/// elements is not checked against the number of subquery columns here.
+pub fn in_subquery_tuple_values<'a>(
+ expr: &'a Expr,
+ subquery: &crate::LogicalPlan,
+) -> Option<&'a [Expr]> {
+ match expr {
+ Expr::ScalarFunction(func)
+ if func.func.name() == "struct" && subquery.schema().fields().len() > 1 =>
Review Comment:
Fixed. `in_subquery_tuple_values` now also recognizes a `ScalarValue::Struct` literal and returns its elements as owned exprs (`Cow<[Expr]>`). `(1, 2) NOT IN (...)` is covered in `null_aware_multi_column.slt`, both as a filter and as projected values (matched / UNKNOWN / mismatched).
##########
datafusion/physical-plan/src/joins/hash_join/exec.rs:
##########
@@ -245,8 +256,18 @@ impl NullAwareMode {
join_type: JoinType,
partition_mode: PartitionMode,
num_keys: usize,
+ num_value_keys: usize,
has_filter: bool,
) -> Result<Self> {
+ if num_value_keys == 0 || num_value_keys > num_keys {
+ return plan_err!(
+ "null_aware {join_type} join needs between 1 and {num_keys} `NOT IN` value keys, got {num_value_keys}"
+ );
+ }
+ // `num_keys > 1` also covers a multi-column value key without any
+ // correlation: a NULL in one tuple element leaves the comparison FALSE
+ // whenever another element is a definite mismatch, so it too must be
+ // decided per build row.
let correlated = num_keys > 1 || has_filter;
Review Comment:
Thanks for the measurements. I'd like to keep the build-side change and the narrowing as a follow-up, as you suggested. For now:
- The cost is documented on `HashJoinExec::null_aware_value_keys`: the outer side stays the build side, since only a single-key join is swapped, and without correlation keys every NULL-valued row is paired with every row on the other side.
- The `null_aware_join` suite has three new queries for these shapes: Q10 with no NULLs, Q11 with 1% NULLs in one subquery element, and Q12 with 1% NULLs in one outer element.
Treating an element that is NOT NULL on both sides as a scope key, and a multi-key null-aware `RightAnti` that builds on the subquery side: agreed these are the right next steps; leaving them as follow-ups.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5841533236
Thanks @comphead for the thorough review. 8728b2c addresses it. Besides the inline fixes:
**Tests**
- The execution cases moved from `exec.rs` to a new `null_aware_multi_column.slt`, with a `configMatrix` over `datafusion.execution.batch_size` (1, 2, 100, 8192). It adds the two cases that weren't in SLT: an equality correlation with mixed outcomes and a NULL scope key, and `(NULL, NULL)` against an empty subquery. The helpers are gone. `test_null_aware_validation_value_keys` stays and no longer needs them.
- Merged the overlapping SLT tests (20/21 and 22/23). Replaced the empty-result Test 24 with a correlated case that returns rows. `null_aware_anti_join.slt` now only checks the plans.
- Fixed `subquery.slt` Test 8 so its filter actually drops a row.
- The two dynamic-filter escape tests are one test parametrized over the value key count. The two proto round-trip tests are one test looping over both queries.
- Added the missing `-0.0`, all-literal tuple, and non-hashable element cases.
**Simplification**
- The tuple/subquery arity check now lives only in `in_subquery_tuple_values`, which returns an error on a mismatch. I removed the copies in the SQL planner, `coerce_multi_column_in_subquery`, `build_join` and placeholder inference, along with `InSubquery::tuple_values`.
- Added `cast_subquery_columns`, and `cast_subquery` now delegates to it. The coercion computes the common types and the element casts in one loop.
- `in_join_filter` builds the filter with `conjunction`.
- The value key count now lives only in `NullAwareMode`. I removed it from `collect_left_input`, `JoinLeftData` and `SharedBuildAccumulator`.
- Nits: the stale `on[0]` doc is gone with `in_predicate_first`, the logical encoder uses a checked `u32` conversion, and `with_null_aware_value_keys` returns an internal error instead of the silent arm.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "adriangbot (via GitHub)" <gi...@apache.org>.
adriangbot commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5901477620
π€ Benchmark running (GKE) | [trigger](https://github.com/apache/datafusion/pull/19857#issuecomment-5901079308)
**Instance:** `c4a-highmem-16` (12 vCPU / 65 GiB) | `Linux bench-c5901079308-2930-gp676 6.12.94+ #1 SMP Wed Aug 19 07:47:20 UTC 2026 aarch64 GNU/Linux`
<details><summary>CPU Details (lscpu)</summary>
```
Architecture: aarch64
CPU op-mode(s): 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Vendor ID: ARM
Model name: Neoverse-V2
Model: 1
Thread(s) per core: 1
Core(s) per cluster: 16
Socket(s): -
Cluster(s): 1
Stepping: r0p1
BogoMIPS: 2000.00
Flags: fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm jscvt fcma lrcpc dcpop sha3 sm3 sm4 asimddp sha512 sve asimdfhm dit uscat ilrcpc flagm sb paca pacg dcpodp sve2 sveaes svepmull svebitperm svesha3 svesm4 flagm2 frint svei8mm svebf16 i8mm bf16 dgh rng bti
L1d cache: 1 MiB (16 instances)
L1i cache: 1 MiB (16 instances)
L2 cache: 32 MiB (16 instances)
L3 cache: 80 MiB (1 instance)
NUMA node(s): 1
NUMA node0 CPU(s): 0-15
Vulnerability Gather data sampling: Not affected
Vulnerability Indirect target selection: Not affected
Vulnerability Itlb multihit: Not affected
Vulnerability L1tf: Not affected
Vulnerability Mds: Not affected
Vulnerability Meltdown: Not affected
Vulnerability Mmio stale data: Not affected
Vulnerability Reg file data sampling: Not affected
Vulnerability Retbleed: Not affected
Vulnerability Spec rstack overflow: Not affected
Vulnerability Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Vulnerability Spectre v1: Mitigation; __user pointer sanitization
Vulnerability Spectre v2: Mitigation; CSV2, BHB
Vulnerability Srbds: Not affected
Vulnerability Tsa: Not affected
Vulnerability Tsx async abort: Not affected
Vulnerability Vmscape: Not affected
```
</details>
Comparing multi-column-null-aware-anti-join (295c0561392e9fe23a401b3c59c37ceeba1f829f) to 696eaf5 (merge-base) [diff](https://github.com/apache/datafusion/compare/696eaf58d436faa6532aa0f5875b5a120b94d0eb..295c0561392e9fe23a401b3c59c37ceeba1f829f)
<details><summary>Run configuration</summary>
```yaml
run benchmark tpch
```
</details>
Results will be posted here when complete
---
[File an issue](https://github.com/adriangb/datafusion-benchmarking/issues) against this benchmark runner
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6023507557
> Won't get a chance to do a careful review right now, but the points I raised before have been addressed -- no objections to landing this from me!
Thank you @neilconway !
Could you remove the requested changes request because seems it will block merging? Thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6642511720
THis PR was flagged as introducing a regression
- https://github.com/apache/datafusion/issues/26569
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4132613647
Not sure if tpcds contains NA Anti Join, but it at least contains many join types
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on code in PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#discussion_r3101138607
##########
datafusion/sqllogictest/test_files/null_aware_anti_join.slt:
##########
@@ -394,16 +394,202 @@ query II rowsort
SELECT * FROM test_table WHERE (c1 NOT IN (SELECT c2 FROM test_table)) = true;
----
-# NOTE: The correlated subquery version from issue #10583:
-# SELECT * FROM test_table t1 WHERE c1 NOT IN (SELECT c2 FROM test_table t2 WHERE t1.c1 = t2.c1)
-# is not yet supported because it creates a multi-column join (correlation + NOT IN condition).
-# This is a known limitation - currently only supports single column null-aware anti joins.
-# This will be addressed in next Phase (multi-column support).
+#############
+## Test 19: Multi-column NOT IN with NULL in subquery
+#############
+
+statement ok
+CREATE TABLE multi_col_outer(a INT, b INT, value TEXT) AS VALUES
+(1, 2, 'x'),
+(3, 4, 'y'),
+(5, 6, 'z'),
+(NULL, 8, 'w');
+
+statement ok
+CREATE TABLE multi_col_inner_with_null(x INT, y INT) AS VALUES
+(1, 2),
+(NULL, 4);
+
+# Should return empty because subquery has NULL
+query IIT rowsort
+SELECT * FROM multi_col_outer
+WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_with_null);
+----
+
+#############
+## Test 20: Multi-column NOT IN without NULL
+#############
+
+statement ok
+CREATE TABLE multi_col_inner_no_null(x INT, y INT) AS VALUES
+(1, 2);
+
+# Should return (3, 4, 'y'), (5, 6, 'z')
+# The row (NULL, 8, 'w') is not returned because NULL is not comparable
+query IIT rowsort
+SELECT * FROM multi_col_outer
+WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_no_null);
+----
+3 4 y
+5 6 z
Review Comment:
Thanks for catching this. Let me take a look and fix it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Add multi-column support for null-aware anti joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-4270322292
We cross-validated all test cases (Test 1β25) against Postgres 16 running in a Docker container. We translated each .slt test into equivalent Postgres SQL and compared the output row-by-row against DataFusion's expected results. All 25 tests matched Postgres exactly, covering single-column NOT IN (Tests 1β18), multi-column NOT IN (Tests 19β23), correlated multi-column NOT IN (Test 24), and empty subquery (Test 25).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5829097506
## [Codecov](https://app.codecov.io/gh/apache/datafusion/pull/19857?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
:x: Patch coverage is `86.64547%` with `84 lines` in your changes missing coverage. Please review.
:white_check_mark: Project coverage is 82.49%. Comparing base ([`696eaf5`](https://app.codecov.io/gh/apache/datafusion/commit/696eaf58d436faa6532aa0f5875b5a120b94d0eb?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`8eb53c4`](https://app.codecov.io/gh/apache/datafusion/commit/8eb53c4c25876c5ae8372300ef6818a9186a836e?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
:warning: Report is 1 commits behind head on main.
| [Files with missing lines](https://app.codecov.io/gh/apache/datafusion/pull/19857?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Patch % | Lines |
|---|---|---|
| [datafusion/proto-models/src/generated/pbjson.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fproto-models%2Fsrc%2Fgenerated%2Fpbjson.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9wcm90by1tb2RlbHMvc3JjL2dlbmVyYXRlZC9wYmpzb24ucnM=) | 0.00% | [26 Missing :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/optimizer/src/analyzer/type\_coercion.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Fanalyzer%2Ftype_coercion.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL2FuYWx5emVyL3R5cGVfY29lcmNpb24ucnM=) | 73.33% | [17 Missing and 7 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [...on/optimizer/src/decorrelate\_predicate\_subquery.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Fdecorrelate_predicate_subquery.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL2RlY29ycmVsYXRlX3ByZWRpY2F0ZV9zdWJxdWVyeS5ycw==) | 88.17% | [6 Missing and 5 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [...tafusion/physical-plan/src/joins/hash\_join/exec.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fphysical-plan%2Fsrc%2Fjoins%2Fhash_join%2Fexec.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9waHlzaWNhbC1wbGFuL3NyYy9qb2lucy9oYXNoX2pvaW4vZXhlYy5ycw==) | 93.71% | [6 Missing and 5 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/expr/src/logical\_plan/invariants.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fexpr%2Fsrc%2Flogical_plan%2Finvariants.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9leHByL3NyYy9sb2dpY2FsX3BsYW4vaW52YXJpYW50cy5ycw==) | 70.00% | [2 Missing and 1 partial :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [...fusion/physical-plan/src/joins/hash\_join/stream.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fphysical-plan%2Fsrc%2Fjoins%2Fhash_join%2Fstream.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9waHlzaWNhbC1wbGFuL3NyYy9qb2lucy9oYXNoX2pvaW4vc3RyZWFtLnJz) | 94.64% | [2 Missing and 1 partial :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/expr/src/expr.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fexpr%2Fsrc%2Fexpr.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9leHByL3NyYy9leHByLnJz) | 93.54% | [1 Missing and 1 partial :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/core/src/physical\_planner.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fcore%2Fsrc%2Fphysical_planner.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9jb3JlL3NyYy9waHlzaWNhbF9wbGFubmVyLnJz) | 92.30% | [0 Missing and 1 partial :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/optimizer/src/eliminate\_join.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Feliminate_join.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL2VsaW1pbmF0ZV9qb2luLnJz) | 92.30% | [1 Missing :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [...physical-plan/src/joins/hash\_join/shared\_bounds.rs](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&filepath=datafusion%2Fphysical-plan%2Fsrc%2Fjoins%2Fhash_join%2Fshared_bounds.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9waHlzaWNhbC1wbGFuL3NyYy9qb2lucy9oYXNoX2pvaW4vc2hhcmVkX2JvdW5kcy5ycw==) | 98.50% | [1 Missing :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| ... and [1 more](https://app.codecov.io/gh/apache/datafusion/pull/19857?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | |
<details><summary>Additional details and impacted files</summary>
```diff
@@ Coverage Diff @@
## main #19857 +/- ##
========================================
Coverage 82.48% 82.49%
========================================
Files 1140 1140
Lines 438493 439005 +512
Branches 438493 439005 +512
========================================
+ Hits 361708 362143 +435
- Misses 54944 55006 +62
- Partials 21841 21856 +15
```
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/datafusion/pull/19857?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
<details><summary> :rocket: New features to boost your workflow: </summary>
- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
- :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6023199013
Thank you @comphead
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5901079308
run benchmark tpch tpcds
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6021823612
Thanks @comphead. ab3120cc9 addresses the review:
**Tests**
- `null_aware_multi_column.slt` now covers:
- `IS [NOT] NULL` over a tuple `IN`
- subqueries whose top is `DISTINCT`, `GROUP BY` or `ORDER BY ... LIMIT`
- `Utf8` elements
- a `LeftMark` tuple with an `i.k < o.k` correlation
- the session time zone case
- placeholders over two same-named subquery columns
- Added `fuzz_cases/null_aware_join_fuzz.rs`. It compares scalar and multi-column `NOT IN` / `IN`, in filter, `OR` and value contexts, with and without equality and non-equality correlations, against `NOT EXISTS` / `EXISTS` formulations of the same three-valued logic. It sweeps random tables with NULLs on both sides across batch sizes and partition counts.
**Docs**
- `subqueries.md` now documents the tuple form, allows one subquery column per tuple element in both places that said "a single column", and explains that `(1, NULL) IN (SELECT 1, NULL)` is NULL under row comparison while the list form is `true`. The examples are pinned in the SLT.
**Follow-ups**
- Filed #26088 (NOT NULL elements as scope keys) and #26089 (a multi-key null-aware `RightAnti` that builds on the subquery side), linked from the doc comment on `HashJoinExec::null_aware_value_keys`.
- Q10 to Q12 at two sizes (mean of three runs each):
| query | `NAJ_ROWS=10000` | `NAJ_ROWS=20000` |
|---|---|---|
| Q10, no NULLs | 0.32 ms | 0.38 ms |
| Q11, 1% NULLs in the subquery | 3.64 ms | 8.07 ms |
| Q12, 1% NULLs in the outer side | 0.56 ms | 0.76 ms |
**Nit**
- Placeholder inference now uses `Column::from(subquery_schema.qualified_field(i))`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-6023448639
Won't get a chance to do a careful review right now, but the points I raised before have been addressed -- no objections to landing this from me!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: support multi-column NOT IN subqueries with null-aware joins [datafusion]
Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #19857:
URL: https://github.com/apache/datafusion/pull/19857#issuecomment-5977064592
@sunchao Can you take a look? Thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org