You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "buraksenn (via GitHub)" <gi...@apache.org> on 2026/04/20 08:40:32 UTC
[PR] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
buraksenn opened a new pull request, #21743:
URL: https://github.com/apache/datafusion/pull/21743
## Which issue does this PR close?
Related with #2547 and #2548 but does not close
## Rationale for this change
In #21416 I've aligned ALL operator NULL semantics to Postgres while supporting additional operators. I've implemented ANY operator but missed that part. Initially that PR included these changes but with the suggestion from @Jefffrey I've separated those changes and opened this PR.
## What changes are included in this PR?
- Refactor ANY operator to use same logic with ALL operator
- align null semantics with postgres
| Query | PostgreSQL | This PR | DuckDB |
|---|---|---|---|
| `5 = ANY(NULL::INT[])` | `NULL` | `NULL` | `false` |
| `5 <> ANY(NULL::INT[])` | `NULL` | `NULL` | `false` |
| `5 > ANY(NULL::INT[])` | `NULL` | `NULL` | `false` |
| `5 < ANY(NULL::INT[])` | `NULL` | `NULL` | `false` |
| `5 >= ANY(NULL::INT[])` | `NULL` | `NULL` | `false` |
| `5 <= ANY(NULL::INT[])` | `NULL` | `NULL` | `false` |
#### I'll explore a followup implementation on ANY and ALL as UDFs instead of this case approach to see if it will perform faster. I've wanted to open this PR to correct out the NULL behavior
## Are these changes tested?
Yes existing and additional slt tests.
## Are there any user-facing changes?
Yes users will be able to see null semantics are same as postgres
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4412252130
I think at this point in DataFusion's life (where it has a bunch of existing users with real customers, etc) we need to be careful about changing semantics of already existing functions
If there is some clear benefit or the old behavior is clearly wrong, that is one thing
However, just changing the semantics to be slightly more compatible with Postgres I don't think is a great reason to break downstream users
I think this is somewhat covered in https://datafusion.apache.org/contributor-guide/api-health.html#when-to-make-breaking-api-changes but that doesn't explicitly mention SQL. I'll try and propose adding some SQL specific changes to it too
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "cetra3 (via GitHub)" <gi...@apache.org>.
cetra3 commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4417121996
I've raised a PR that adjusts this slightly: https://github.com/apache/datafusion/pull/22102
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "buraksenn (via GitHub)" <gi...@apache.org>.
buraksenn commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4287102076
> Will we need a note in the upgrade guide for these changes?
Previously ANY only supported `=` operator and `ALL` did not exist for subquery comparisons. My changes to extending these two are not released yet. Thus, only affect is that when an array has unmatched NULLs we'll return NULL instead of false.
this case in the table:
```
5 = ANY(NULL::INT[]) Result: false -> null
```
I'm not sure if I'm equipped to answer this but I don't think it would be necessary. Otherwise, we can mention the case above for the behavior change (to align with PG)
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4418184785
Yes I forgot to consider that ANY/ALL was already implemented for `=` so that was actually a breaking change when introducing these semantics. It sounds like we should preserve the semantics we had for `=` for the other operators `>`, `<>`, etc.
I'll try take a look at #22102 soon
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4453823929
I tried to document the fact that SQL is a public API as well in the docs in a follow on PR
- https://github.com/apache/datafusion/pull/22179
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "cetra3 (via GitHub)" <gi...@apache.org>.
cetra3 commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4393377774
I know this is already merged, but this one PR has broken a lot of our code internally, due to the structural changes around this. (i.e, https://github.com/apache/datafusion/issues/22053)
Is there any chance we can make this an optimization pass that can switch off this behaviour, rather than something that is always baked in.
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "berkaysynnada (via GitHub)" <gi...@apache.org>.
berkaysynnada merged PR #21743:
URL: https://github.com/apache/datafusion/pull/21743
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4481295071
Given this is a regression and thus I think would block the release, I propose we revert this change until we can sort out a good long term fix
- https://github.com/apache/datafusion/pull/22345
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "cetra3 (via GitHub)" <gi...@apache.org>.
cetra3 commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4402298063
> > I know this is already merged, but this one PR has broken a lot of our code internally, due to the structural changes around this. (i.e, #22053)
> > Is there any chance we can make this an optimization pass that can switch off this behaviour, rather than something that is always baked in.
>
> Could you elaborate on what broke (beyond the linked issue) and which behaviour you're referring to here? The support of `ANY <> array()` itself, or the translation of it to case statements in order to follow postgres semantics?
I've investigated this more. The shape to the `LogicalPlan` has actually broken *a lot* of things. Stats min/max pruning, page-index pruning, and bloom filter pruning are all effectively disabled for any query using `ANY`
I've raised an issue about it: https://github.com/apache/datafusion/issues/22073
--
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] [Minor]: unify ANY/ALL planning and align ANY NULL semantics with PG [datafusion]
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on PR #21743:
URL: https://github.com/apache/datafusion/pull/21743#issuecomment-4396080267
> I know this is already merged, but this one PR has broken a lot of our code internally, due to the structural changes around this. (i.e, #22053)
>
> Is there any chance we can make this an optimization pass that can switch off this behaviour, rather than something that is always baked in.
Could you elaborate on what broke (beyond the linked issue) and which behaviour you're referring to here? The support of `ANY <> array()` itself, or the translation of it to case statements in order to follow postgres semantics?
--
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