fix: allow arbitrary operators with ANY and ALL on Postgres #1842
Conversation
In sqlparser PR apache#963 a check was introduced which limits which operators can be used with `ANY` and `ALL` expressions. Postgres can parse more (possibly _all_ binary operators, investigation pending) in this location. Postgres only seems to care that the operator yields a boolean - which is a semantic error, not a syntax (parse) error. Example (semantic error, not a parse error): ``` select 123 % ANY(array[246]); ERROR: op ANY/ALL (array) requires operator to yield boolean LINE 1: select 123 % ANY(array[246]); ^ ``` The following code in `src/parser/mod.rs:2893-2908` is where the allowlist of operators is enforced: ```rust if !matches!( op, BinaryOperator::Gt | BinaryOperator::Lt | BinaryOperator::GtEq | BinaryOperator::LtEq | BinaryOperator::Eq | BinaryOperator::NotEq ) { return parser_err!( format!( "Expected one of [=, >, <, =>, =<, !=] as comparison operator, found: {op}" ), tok.span.start ); }; ```
| @@ -3471,7 +3471,7 @@ impl<'a> Parser<'a> { | |||
| right | |||
| }; | |||
|
|
|||
| if !matches!( | |||
| if !dialect_of!(self is PostgreSqlDialect) && !matches!( | |||
There was a problem hiding this comment.
can we use the dialect trait for this logic (dialect_of is effectively deprecated internally)? something like if self.dialect.supports_arbitrary_comparison_in_subquery_operator()
|
Maybe we can remove the check entirely ? In my opinion, this should always be a logical error, not a syntax error. And when not sure, it's always better to accept parsing and let library users potentially reject the query based on applying their own rules on the AST. |
Ah indeed I think this would make more sense here, it should be fine to remove the check entirely |
|
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. |
In sqlparser PR #963 a check was introduced which limits which operators can be used with
ANYandALLexpressions.Postgres can parse more (possibly all binary operators, investigation pending) in this location. Postgres only seems to care that the operator yields a boolean - which is a semantic error, not a syntax (parse) error.
Example of semantic error in Postgres:
FWIW, the Postgres
pg_trgmextension provides support for the%where left and right args aretextand it returns aboolean- which makes it useable withANYandALL.The following code in
src/parser/mod.rs:2893-2908is where the allowlist of operators is enforced: