You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "mvzink (via GitHub)" <gi...@apache.org> on 2025/03/18 23:17:00 UTC
[I] Improve representation of `LIKE ALL` and variants [datafusion-sqlparser-rs]
mvzink opened a new issue, #1770:
URL: https://github.com/apache/datafusion-sqlparser-rs/issues/1770
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. 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,
},
),
]
```
--
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.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: [I] Improve representation of `LIKE ALL` and variants [datafusion-sqlparser-rs]
Posted by "mvzink (via GitHub)" <gi...@apache.org>.
mvzink commented on issue #1770:
URL: https://github.com/apache/datafusion-sqlparser-rs/issues/1770#issuecomment-2734987324
Also note that `LIKE ANY` etc. are handled with a special `any` flag on `Expr::Like`. It would also make things more uniform to do the same with `all`, but one of the other options I mentioned would probably be better.
--
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