Description
Currently, expressions such as ... LIKE ALL(...) are parsed as an Expr::Like with the pattern being an Expr::Function with a name of "ALL". It seems preferable to parse them as an Expr::AllOp with compare_op: BinaryOperator::Like.
However, with #569, the various string matching operators (LIKE, ILIKE) were turned from binary operators into special Expr variants, so that is not currently possible. I believe this change also indirectly led to #863 being raised, which was seemingly worked around using the bandaid of a catch-all "custom" binary operator. (REGEXP and RLIKE were later added in the same manner, as Expr variants instead of binary operators, in #1017.)
I cannot tell from the PRs or discussions why these were turned into special Expr variants instead of left as binary operators, but it seems that an improved representation of LIKE ALL, NOT ILIKE ANY, etc. would require either turning them back into binary operators, or somehow changing AllOp from using compare_op: BinaryOperator to... something else. I can't currently think of an alternative approach.
On the other hand, perhaps the representation of ALL/ANY with special Expr variants is actually unnecessary, and they should actually parse as something like Expr::BinaryOp { left, op, right: Expr::All(_)}. Either way, ideally a = ANY(...) and a LIKE ANY(...) would end up with similar ASTs.
Appendix: current parsing behavior
$ echo "SELECT 'abc' ILIKE ALL('{aBC, _bc}');" | cargo run --example cli - --postgres
Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.06s
Running `target/debug/examples/cli - --postgres`
Parsing from stdin using PostgreSqlDialect
2025-03-18T22:58:41.683Z DEBUG [sqlparser::parser] Parsing sql 'SELECT 'abc' ILIKE ALL('{aBC, _bc}');
'...
2025-03-18T22:58:41.687Z DEBUG [sqlparser::parser] parsing expr
2025-03-18T22:58:41.687Z DEBUG [sqlparser::parser] prefix: Value(ValueWithSpan { value: SingleQuotedString("abc"), span: Span(Location(1,8)..Location(1,13)) })
2025-03-18T22:58:41.688Z DEBUG [sqlparser::dialect::postgresql] get_next_precedence() TokenWithSpan { token: Word(Word { value: "ILIKE", quote_style: None, keyword: ILIKE }), span: Span(Location(1,14)..Location(1,19)) }
2025-03-18T22:58:41.688Z DEBUG [sqlparser::dialect] get_next_precedence_full() TokenWithSpan { token: Word(Word { value: "ILIKE", quote_style: None, keyword: ILIKE }), span: Span(Location(1,14)..Location(1,19)) }
2025-03-18T22:58:41.688Z DEBUG [sqlparser::parser] next precedence: 60
2025-03-18T22:58:41.688Z DEBUG [sqlparser::parser] parsing expr
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] parsing expr
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] prefix: Value(ValueWithSpan { value: SingleQuotedString("{aBC, _bc}"), span: Span(Location(1,24)..Location(1,36)) })
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect::postgresql] get_next_precedence() TokenWithSpan { token: RParen, span: Span(Location(1,36)..Location(1,37)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect] get_next_precedence_full() TokenWithSpan { token: RParen, span: Span(Location(1,36)..Location(1,37)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] next precedence: 0
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] parsing expr
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] prefix: Value(ValueWithSpan { value: SingleQuotedString("{aBC, _bc}"), span: Span(Location(1,24)..Location(1,36)) })
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect::postgresql] get_next_precedence() TokenWithSpan { token: RParen, span: Span(Location(1,36)..Location(1,37)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect] get_next_precedence_full() TokenWithSpan { token: RParen, span: Span(Location(1,36)..Location(1,37)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] next precedence: 0
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] prefix: Function(Function { name: ObjectName([Identifier(Ident { value: "ALL", quote_style: None, span: Span(Location(1,20)..Location(1,23)) })]), uses_odbc_syntax: false, parameters: None, args: List(FunctionArgumentList { duplicate_treatment: None, args: [Unnamed(Expr(Value(ValueWithSpan { value: SingleQuotedString("{aBC, _bc}"), span: Span(Location(1,24)..Location(1,36)) })))], clauses: [] }), filter: None, null_treatment: None, over: None, within_group: [] })
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect::postgresql] get_next_precedence() TokenWithSpan { token: SemiColon, span: Span(Location(1,37)..Location(1,38)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect] get_next_precedence_full() TokenWithSpan { token: SemiColon, span: Span(Location(1,37)..Location(1,38)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] next precedence: 0
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect::postgresql] get_next_precedence() TokenWithSpan { token: SemiColon, span: Span(Location(1,37)..Location(1,38)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::dialect] get_next_precedence_full() TokenWithSpan { token: SemiColon, span: Span(Location(1,37)..Location(1,38)) }
2025-03-18T22:58:41.689Z DEBUG [sqlparser::parser] next precedence: 0
Round-trip:
'SELECT 'abc' ILIKE ALL('{aBC, _bc}')'
Parse results:
[
Query(
Query {
with: None,
body: Select(
Select {
select_token: TokenWithSpan {
token: Word(
Word {
value: "SELECT",
quote_style: None,
keyword: SELECT,
},
),
span: Span(Location(1,1)..Location(1,7)),
},
distinct: None,
top: None,
top_before_distinct: false,
projection: [
UnnamedExpr(
ILike {
negated: false,
any: false,
expr: Value(
ValueWithSpan {
value: SingleQuotedString(
"abc",
),
span: Span(Location(1,8)..Location(1,13)),
},
),
pattern: Function(
Function {
name: ObjectName(
[
Identifier(
Ident {
value: "ALL",
quote_style: None,
span: Span(Location(1,20)..Location(1,23)),
},
),
],
),
uses_odbc_syntax: false,
parameters: None,
args: List(
FunctionArgumentList {
duplicate_treatment: None,
args: [
Unnamed(
Expr(
Value(
ValueWithSpan {
value: SingleQuotedString(
"{aBC, _bc}",
),
span: Span(Location(1,24)..Location(1,36)),
},
),
),
),
],
clauses: [],
},
),
filter: None,
null_treatment: None,
over: None,
within_group: [],
},
),
escape_char: None,
},
),
],
into: None,
from: [],
lateral_views: [],
prewhere: None,
selection: None,
group_by: Expressions(
[],
[],
),
cluster_by: [],
distribute_by: [],
sort_by: [],
having: None,
named_window: [],
qualify: None,
window_before_qualify: false,
value_table_mode: None,
connect_by: None,
flavor: Standard,
},
),
order_by: None,
limit_clause: None,
fetch: None,
locks: [],
for_clause: None,
settings: None,
format_clause: None,
},
),
]