You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "neilconway (via GitHub)" <gi...@apache.org> on 2026/04/03 22:25:55 UTC
[PR] Neilc/feat lateral outer joins [datafusion]
neilconway opened a new pull request, #21352:
URL: https://github.com/apache/datafusion/pull/21352
## Which issue does this PR close?
- Closes #21199.
## Rationale for this change
This PR adds support for LEFT join semantics for lateral joins. This is a bit tricky because of how it interacts with compensation for the "count bug". This might be easiest to illustrate with an example; consider this query (Q1):
```sql
SELECT t1.id, sub.cnt
FROM t1 LEFT JOIN LATERAL (
SELECT count(*) AS cnt FROM t2 WHERE t2.t1_id = t1.id
) sub ON sub.cnt > 0
ORDER BY t1.id;
```
The initial decorrelation (Q2) is
```sql
SELECT t1.id, sub.cnt
FROM t1 LEFT JOIN (
SELECT count(*) AS cnt, t2.t1_id, TRUE AS __always_true
FROM t2 GROUP BY t2.t1_id
) sub ON t1.id = sub.t1_id
```
Ignoring the user's original `ON` clause for now. This initial query is wrong, because `t1` rows that don't have a match in `t2` will get all-`NULL` values, not `0` for `count(*)` of an empty set. This is the "count bug", and we compensate for that by checking for rows when `__always_true` is `NULL`, and replacing the agg value with the default for that agg (Q3):
```sql
SELECT t1.id,
CASE WHEN sub.__always_true IS NULL THEN 0
ELSE sub.cnt END AS cnt
FROM ( /* Q2 */ )
```
Now we just need to handle the user's original `ON` clause. We can't add this to the rewritten `ON` clause in Q1, because we *don't* want the count-bug compensation to fire. But we also can't just add it to the `WHERE` clause, because we need left join semantics. So we can instead wrap another `CASE` that re-checks the `ON` condition and substitutes `NULL` for every right-side column:
```sql
SELECT t1.id,
CASE WHEN (cnt > 0) IS NOT TRUE THEN NULL
ELSE cnt END AS cnt
FROM ( /* Q3 */ )
```
## What changes are included in this PR?
* Implement lateral left join rewrite as described above
* Update expected tests and add more test cases
* Update documentation
## Are these changes tested?
Yes.
## Are there any user-facing changes?
Support for a new feature.
--
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 support for `LEFT JOIN LATERAL` [datafusion]
Posted by "mbutrovich (via GitHub)" <gi...@apache.org>.
mbutrovich merged PR #21352:
URL: https://github.com/apache/datafusion/pull/21352
--
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 support for `LEFT JOIN LATERAL` [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on PR #21352:
URL: https://github.com/apache/datafusion/pull/21352#issuecomment-4187679418
@crm26 Thanks for the review! And I'm glad to hear that you'll find lateral joins to be helpful. If you have more feedback on the feature in the future, please share 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 support for `LEFT JOIN LATERAL` [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on PR #21352:
URL: https://github.com/apache/datafusion/pull/21352#issuecomment-4229887732
I added a bunch more test cases, covering other edge cases of this feature. I also ran all the tests against DuckDB to confirm that the queries produce the same results.
--
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 support for `LEFT JOIN LATERAL` [datafusion]
Posted by "neilconway (via GitHub)" <gi...@apache.org>.
neilconway commented on PR #21352:
URL: https://github.com/apache/datafusion/pull/21352#issuecomment-4229874806
@mbutrovich Thanks, that's a great idea! I added a test for this case.
--
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 support for `LEFT JOIN LATERAL` [datafusion]
Posted by "mbutrovich (via GitHub)" <gi...@apache.org>.
mbutrovich commented on PR #21352:
URL: https://github.com/apache/datafusion/pull/21352#issuecomment-4226299367
I'm going to review this further, but one quick suggestion for a regression test: DuckDB recently shipped a bug ([duckdb/duckdb#21609](https://github.com/duckdb/duckdb/issues/21609)) where a `WHERE l.k IS NOT NULL` filter failed to eliminate NULL rows produced by LEFT JOIN LATERAL. It was fixed in 1.5.1, but it shows this is an easy edge case to get wrong. None of the current tests exercise interaction between a post-join WHERE filter and left-preserved NULL rows. Something like:
```sql
-- Outer WHERE filter must eliminate NULL-producing LEFT lateral rows
SELECT r.k AS rk, l.k AS lk
FROM src_r AS r
LEFT JOIN LATERAL (
SELECT l.k FROM src_l AS l WHERE l.k > r.k
) AS l ON TRUE
WHERE l.k IS NOT NULL;
```
where `src_r` and `src_l` both contain `'2001-01-01'` -- the lateral produces no match, LEFT JOIN fills NULLs, and the WHERE should return 0 rows. I verified this returns the correct result (0 rows) on this branch.
What I see so far looks great, but I'll keep digging. Thanks @neilconway!
--
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