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/03/09 19:49:11 UTC
[PR] Support '>', '<', '>=', '<=' in any operator [datafusion]
buraksenn opened a new pull request, #20830:
URL: https://github.com/apache/datafusion/pull/20830
## Which issue does this PR close?
- Closes #2548.
## Rationale for this change
ANY operator only supports equality check
## What changes are included in this PR?
Adds support for other expressions and add tests
## Are these changes tested?
Added slt tests for this and they all pass
## Are there any user-facing changes?
Yes user's can now use any operator with this new expressions
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#discussion_r2907955425
##########
datafusion/sqllogictest/test_files/array.slt:
##########
@@ -7003,8 +7003,76 @@ select count(*) from arrays where 'X'=any(column3);
----
0
-query error DataFusion error: Error during planning: Unsupported AnyOp: '>', only '=' is supported
-select count(*) from arrays where 'X'>any(column3);
+# any operator with comparison operators
+# Use inline arrays so the test data is visible and the needle (5)
+# falls within the range of some arrays but not others.
+statement ok
+CREATE TABLE any_op_test AS VALUES
+ (1, make_array(1, 2, 3)),
+ (2, make_array(4, 5, 6)),
+ (3, make_array(7, 8, 9)),
+ (4, make_array(3, 5, 7));
+
+# 5 > ANY(arr): true when array_min < 5
+# row1: min=1 < 5 ✓, row2: min=4 < 5 ✓, row3: min=7 < 5 ✗, row4: min=3 < 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 > any(column2) order by column1;
+----
+1 [1, 2, 3]
+2 [4, 5, 6]
+4 [3, 5, 7]
+
+# 5 >= ANY(arr): true when array_min <= 5
+# row1: min=1 <= 5 ✓, row2: min=4 <= 5 ✓, row3: min=7 <= 5 ✗, row4: min=3 <= 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 >= any(column2) order by column1;
+----
+1 [1, 2, 3]
+2 [4, 5, 6]
+4 [3, 5, 7]
+
+# 5 < ANY(arr): true when array_max > 5
+# row1: max=3 > 5 ✗, row2: max=6 > 5 ✓, row3: max=9 > 5 ✓, row4: max=7 > 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 < any(column2) order by column1;
+----
+2 [4, 5, 6]
+3 [7, 8, 9]
+4 [3, 5, 7]
+
+# 5 <= ANY(arr): true when array_max >= 5
+# row1: max=3 >= 5 ✗, row2: max=6 >= 5 ✓, row3: max=9 >= 5 ✓, row4: max=7 >= 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 <= any(column2) order by column1;
+----
+2 [4, 5, 6]
+3 [7, 8, 9]
+4 [3, 5, 7]
+
+# 5 <> ANY(arr): true when array_min != 5 OR array_max != 5
+# row1: [1,2,3] min=1!=5 ✓, row2: [4,5,6] min=4!=5 ✓, row3: [7,8,9] min=7!=5 ✓, row4: [3,5,7] min=3!=5 ✓
+query I?
+select column1, column2 from any_op_test where 5 <> any(column2) order by column1;
+----
+1 [1, 2, 3]
+2 [4, 5, 6]
+3 [7, 8, 9]
+4 [3, 5, 7]
+
+# For a single-element array where the element equals the needle, <> should return false
+query B
+select 5 <> any(make_array(5));
+----
+false
+
+# For a uniform array [5,5,5], <> should also return false
+query B
+select 5 <> any(make_array(5, 5, 5));
+----
+false
Review Comment:
The new SLT coverage doesn't currently exercise edge cases like `ANY` over an empty array (`make_array()`) or an array with only NULL elements. These cases are important because the implementation uses array_min/array_max, which return NULL for empty/all-null arrays, and could change the truth value of `x op ANY(arr)` compared to expected existential semantics. Please add a couple of scalar queries asserting the expected boolean result for each operator on empty/all-null arrays.
##########
datafusion/sql/src/expr/mod.rs:
##########
@@ -612,16 +612,24 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
planner_context,
),
_ => {
- if compare_op != BinaryOperator::Eq {
- plan_err!(
- "Unsupported AnyOp: '{compare_op}', only '=' is supported"
- )
- } else {
- let left_expr =
- self.sql_to_expr(*left, schema, planner_context)?;
- let right_expr =
- self.sql_to_expr(*right, schema, planner_context)?;
- Ok(array_has(right_expr, left_expr))
+ let left_expr = self.sql_to_expr(*left, schema, planner_context)?;
+ let right_expr = self.sql_to_expr(*right, schema, planner_context)?;
+ match compare_op {
+ BinaryOperator::Eq => Ok(array_has(right_expr, left_expr)),
+ BinaryOperator::NotEq => Ok(array_min(right_expr.clone())
+ .not_eq(left_expr.clone())
+ .or(array_max(right_expr).not_eq(left_expr))),
+ BinaryOperator::Gt => Ok(array_min(right_expr).lt(left_expr)),
Review Comment:
The ANY comparison rewrite using array_min/array_max will return NULL (unknown) for empty arrays and for arrays whose elements are all NULL (because array_min/array_max return NULL in those cases). For existential ANY semantics (and consistent with the existing `=` ANY implementation via `array_has`, which returns false for empty/all-null arrays), these cases should evaluate to false (while still returning NULL when the array itself is NULL). Consider guarding with a list-null check and treating NULL results from array_min/array_max as false (e.g., via CASE/COALESCE) across these match arms.
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#discussion_r2907975070
##########
datafusion/sql/src/expr/mod.rs:
##########
@@ -612,16 +612,24 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
planner_context,
),
_ => {
- if compare_op != BinaryOperator::Eq {
- plan_err!(
- "Unsupported AnyOp: '{compare_op}', only '=' is supported"
- )
- } else {
- let left_expr =
- self.sql_to_expr(*left, schema, planner_context)?;
- let right_expr =
- self.sql_to_expr(*right, schema, planner_context)?;
- Ok(array_has(right_expr, left_expr))
+ let left_expr = self.sql_to_expr(*left, schema, planner_context)?;
+ let right_expr = self.sql_to_expr(*right, schema, planner_context)?;
+ match compare_op {
+ BinaryOperator::Eq => Ok(array_has(right_expr, left_expr)),
+ BinaryOperator::NotEq => Ok(array_min(right_expr.clone())
+ .not_eq(left_expr.clone())
+ .or(array_max(right_expr).not_eq(left_expr))),
+ BinaryOperator::Gt => Ok(array_min(right_expr).lt(left_expr)),
Review Comment:
Indeed:
```
❯ datafusion-cli
DataFusion CLI v52.1.0
> select array_min(null::int[]);
+-----------------+
| array_min(NULL) |
+-----------------+
| NULL |
+-----------------+
1 row(s) fetched.
Elapsed 0.006 seconds
```
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#issuecomment-4026957317
@buraksenn copilot has some good feedback - could you take a look?
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#discussion_r2907975070
##########
datafusion/sql/src/expr/mod.rs:
##########
@@ -612,16 +612,24 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
planner_context,
),
_ => {
- if compare_op != BinaryOperator::Eq {
- plan_err!(
- "Unsupported AnyOp: '{compare_op}', only '=' is supported"
- )
- } else {
- let left_expr =
- self.sql_to_expr(*left, schema, planner_context)?;
- let right_expr =
- self.sql_to_expr(*right, schema, planner_context)?;
- Ok(array_has(right_expr, left_expr))
+ let left_expr = self.sql_to_expr(*left, schema, planner_context)?;
+ let right_expr = self.sql_to_expr(*right, schema, planner_context)?;
+ match compare_op {
+ BinaryOperator::Eq => Ok(array_has(right_expr, left_expr)),
+ BinaryOperator::NotEq => Ok(array_min(right_expr.clone())
+ .not_eq(left_expr.clone())
+ .or(array_max(right_expr).not_eq(left_expr))),
+ BinaryOperator::Gt => Ok(array_min(right_expr).lt(left_expr)),
Review Comment:
Indeed:
```> select array_min(null::int[]) < 5;
+----------------------------+
| array_min(NULL) < Int64(5) |
+----------------------------+
| NULL |
+----------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
```
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#issuecomment-4097909574
Thanks again @buraksenn and @adriangb
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "buraksenn (via GitHub)" <gi...@apache.org>.
buraksenn commented on PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#issuecomment-4029148941
> @buraksenn copilot has some good feedback - could you take a look?
Indeed, I did not think of NULL cases. Thanks @adriangb. I think last commit addresses these
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#discussion_r2945292564
##########
datafusion/sql/src/expr/mod.rs:
##########
@@ -612,16 +612,29 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
planner_context,
),
_ => {
- if compare_op != BinaryOperator::Eq {
- plan_err!(
- "Unsupported AnyOp: '{compare_op}', only '=' is supported"
- )
- } else {
- let left_expr =
- self.sql_to_expr(*left, schema, planner_context)?;
- let right_expr =
- self.sql_to_expr(*right, schema, planner_context)?;
- Ok(array_has(right_expr, left_expr))
+ let left_expr = self.sql_to_expr(*left, schema, planner_context)?;
+ let right_expr = self.sql_to_expr(*right, schema, planner_context)?;
+ match compare_op {
+ BinaryOperator::Eq => Ok(array_has(right_expr, left_expr)),
+ BinaryOperator::NotEq => Ok(array_min(right_expr.clone())
+ .not_eq(left_expr.clone())
+ .or(array_max(right_expr).not_eq(left_expr))
+ .is_true()),
+ BinaryOperator::Gt => {
+ Ok(array_min(right_expr).lt(left_expr).is_true())
+ }
+ BinaryOperator::Lt => {
+ Ok(array_max(right_expr).gt(left_expr).is_true())
+ }
+ BinaryOperator::GtEq => {
+ Ok(array_min(right_expr).lt_eq(left_expr).is_true())
+ }
+ BinaryOperator::LtEq => {
+ Ok(array_max(right_expr).gt_eq(left_expr).is_true())
Review Comment:
The `.is_true()` wrapping in these comparison arms turns a NULL comparison result into FALSE. That incorrectly changes results for queries like `SELECT NULL > ANY([1,2])` (should be NULL) and `SELECT 5 > ANY(NULL::INT[])` (should be NULL), and it differs from `=` ANY which preserves NULLs via `array_has`. Suggest guarding specifically for empty/all-null arrays (where `array_min/array_max` returns NULL) instead of applying `IS TRUE` to the whole comparison.
##########
datafusion/sqllogictest/test_files/array.slt:
##########
@@ -7003,8 +7003,186 @@ select count(*) from arrays where 'X'=any(column3);
----
0
-query error DataFusion error: Error during planning: Unsupported AnyOp: '>', only '=' is supported
-select count(*) from arrays where 'X'>any(column3);
+# any operator with comparison operators
+# Use inline arrays so the test data is visible and the needle (5)
+# falls within the range of some arrays but not others.
+statement ok
+CREATE TABLE any_op_test AS VALUES
+ (1, make_array(1, 2, 3)),
+ (2, make_array(4, 5, 6)),
+ (3, make_array(7, 8, 9)),
+ (4, make_array(3, 5, 7));
+
+# 5 > ANY(arr): true when array_min < 5
+# row1: min=1 < 5 ✓, row2: min=4 < 5 ✓, row3: min=7 < 5 ✗, row4: min=3 < 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 > any(column2) order by column1;
+----
+1 [1, 2, 3]
+2 [4, 5, 6]
+4 [3, 5, 7]
+
+# 5 >= ANY(arr): true when array_min <= 5
+# row1: min=1 <= 5 ✓, row2: min=4 <= 5 ✓, row3: min=7 <= 5 ✗, row4: min=3 <= 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 >= any(column2) order by column1;
+----
+1 [1, 2, 3]
+2 [4, 5, 6]
+4 [3, 5, 7]
+
+# 5 < ANY(arr): true when array_max > 5
+# row1: max=3 > 5 ✗, row2: max=6 > 5 ✓, row3: max=9 > 5 ✓, row4: max=7 > 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 < any(column2) order by column1;
+----
+2 [4, 5, 6]
+3 [7, 8, 9]
+4 [3, 5, 7]
+
+# 5 <= ANY(arr): true when array_max >= 5
+# row1: max=3 >= 5 ✗, row2: max=6 >= 5 ✓, row3: max=9 >= 5 ✓, row4: max=7 >= 5 ✓
+query I?
+select column1, column2 from any_op_test where 5 <= any(column2) order by column1;
+----
+2 [4, 5, 6]
+3 [7, 8, 9]
+4 [3, 5, 7]
+
+# 5 <> ANY(arr): true when array_min != 5 OR array_max != 5
+# row1: [1,2,3] min=1!=5 ✓, row2: [4,5,6] min=4!=5 ✓, row3: [7,8,9] min=7!=5 ✓, row4: [3,5,7] min=3!=5 ✓
+query I?
+select column1, column2 from any_op_test where 5 <> any(column2) order by column1;
+----
+1 [1, 2, 3]
+2 [4, 5, 6]
+3 [7, 8, 9]
+4 [3, 5, 7]
+
+# For a single-element array where the element equals the needle, <> should return false
+query B
+select 5 <> any(make_array(5));
+----
+false
+
+# For a uniform array [5,5,5], <> should also return false
+query B
+select 5 <> any(make_array(5, 5, 5));
+----
+false
+
+# Empty array: all operators should return false (no elements satisfy the condition)
+query B
+select 5 = any(make_array());
+----
+false
+
+query B
+select 5 <> any(make_array());
+----
+false
+
Review Comment:
The new SLT section covers empty arrays and arrays containing NULL elements, but it doesn’t cover NULL *operands* or a NULL *array* input (e.g. `NULL > ANY(make_array(1,2,3))` and `5 > ANY(NULL::INT[])`). Since the planner rewrite should preserve NULL semantics (consistent with `= ANY` via `array_has`), please add assertions for these NULL cases for the newly supported operators.
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #20830:
URL: https://github.com/apache/datafusion/pull/20830#issuecomment-4085727284
Thank you @buraksenn
--
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] Support '>', '<', '>=', '<=', '<>' in any operator [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #20830:
URL: https://github.com/apache/datafusion/pull/20830
--
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