Feature request: support non-equal quantified subquery

[kaiwangchen]

Is your feature request related to a problem or challenge?

Here is an example:

create table t (c1 int, c2 int);
insert into t values (1, 2);
select c1 from t where c2 > any(select c2 - 1 from t);

Error during planning: Unsupported AnyOp: '>', only '=' is supported

Describe the solution you'd like

D create table t (c1 int, c2 int);
D insert into t values (1, 2);
D select c1 from t where c2 > any(select c2 - 1 from t);
┌───────┐
│  c1   │
│ int32 │
├───────┤
│   1   │
└───────┘

mysql> create table t (c1 int, c2 int);
mysql> insert into t values (1, 2);
mysql> select c1 from t where c2 > any(select c2 - 1 from t);
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Describe alternatives you've considered

No response

Additional context

No response

[waynexia]

FWIW, = any is currently implemented via array_contains udf. To implement a more general ANY feature (like align with PostgreSQL), treating ANY as a logical plan node with subquery, and converting it to EXISTS/JOIN might be more suitable

datafusion/datafusion/functions-nested/src/planner.rs

Lines 124 to 137 in 00e7952

	fn plan_any(&self, expr: RawBinaryExpr) -> Result<PlannerResult<RawBinaryExpr>> {
	if expr.op == BinaryOperator::Eq {
	Ok(PlannerResult::Planned(Expr::ScalarFunction(
	ScalarFunction::new_udf(
	array_has_udf(),
	// left and right are reversed here so `needle=any(haystack)` -> `array_has(haystack, needle)`
	vec![expr.right, expr.left],
	),
	)))
	} else {
	plan_err!("Unsupported AnyOp: '{}', only '=' is supported", expr.op)
	}
	}
	}