Skip to content

Representing infix REGEXP #863

Closed
Closed

Description

@max-sixty

MySQL & Sqlite have unusual regex operators:

 SELECT 'a' REGEXP '^[a-d]';

i.e. it's an infix function.

We are big users (and big fans!) of sqlparser-rs in PRQL We have a PR PRQL/prql#2458 for implementing regex in the other dialects.

Could I ask — is there some way of representing an infix function in sqlparser-rs AST?

Activity

  1. alamb commented on May 2, 2023

    @alamb
    Contributor
  2. max-sixty commented on May 2, 2023

    @max-sixty
    ContributorAuthor

    Thanks for the swift response @alamb

    👋 How about https://docs.rs/sqlparser/0.33.0/sqlparser/ast/enum.Expr.html#variant.BinaryOp ?

    The only one there which is custom is PGCustomBinaryOperator. The official docs of that don't allow for words (i.e. REGEXP):

    The operator name is a sequence of up to NAMEDATALEN-1 (63 by default) characters from the following list:

        • / < > = ~ ! @ # % ^ & | ` ?

    ...though maybe sqlparser-rs don't enforce that and we can squeeze it in?

  3. alamb commented on May 2, 2023

    @alamb
    Contributor

    ...though maybe sqlparser-rs don't enforce that and we can squeeze it in?

    Since sqlparser-rs parses many different dialects I don't think there is any reason to limit sqlparser to posgres's limitations

    I think https://github.com/sqlparser-rs/sqlparser-rs#syntax-vs-semantics may be relevant here

  4. max-sixty commented on May 4, 2023

    @max-sixty
    ContributorAuthor

    I think https://github.com/sqlparser-rs/sqlparser-rs#syntax-vs-semantics may be relevant here

    OK — I had thought that allowed characters might be syntax rather than semantics, but very happy to take it!

    Thanks @alamb , I'll close

  5. max-sixty commented on May 7, 2023

    @max-sixty
    ContributorAuthor

    Sorry, actually I spoke too soon — that seems to write OPERATOR when writing it out.

    This would be the diff I need — happy to put a PR in — but:

    • It doesn't parse anything — I'm not sure how to parse SELECT 'a' REGEXP '^[a-d]'; with REGEXP as the operator... (but fine for my narrow purpose)
    • Where would I add tests?
    diff --git a/src/ast/operator.rs b/src/ast/operator.rs
    index 75877c9..2981799 100644
    --- a/src/ast/operator.rs
    +++ b/src/ast/operator.rs
    @@ -85,6 +85,7 @@ pub enum BinaryOperator {
         BitwiseOr,
         BitwiseAnd,
         BitwiseXor,
    +    Custom(String),
         PGBitwiseXor,
         PGBitwiseShiftLeft,
         PGBitwiseShiftRight,
    @@ -122,6 +123,7 @@ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
                 BinaryOperator::BitwiseOr => f.write_str("|"),
                 BinaryOperator::BitwiseAnd => f.write_str("&"),
                 BinaryOperator::BitwiseXor => f.write_str("^"),
    +            BinaryOperator::Custom(s) => f.write_str(s),
                 BinaryOperator::PGBitwiseXor => f.write_str("#"),
                 BinaryOperator::PGBitwiseShiftLeft => f.write_str("<<"),
                 BinaryOperator::PGBitwiseShiftRight => f.write_str(">>"),

    Thanks!

  6. added a commit that references this issue on May 7, 2023
  7. ankrgyl commented on May 9, 2023

    @ankrgyl
    Contributor

    @max-sixty is my understanding correct that in #868 you're adding a "catch all" custom operator and intend to capture the (built-in) regex syntax in MySQL that way?

    If so, why not add a regex operator and then for the MySQL/sqlite dialects parse it as a binary op?

  8. eitsupi commented on May 9, 2023

    @eitsupi
    Contributor

    Given something like MySQL's DIV operator, I am wondering if merging #868 makes sense.
    https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html

    SELECT 5 DIV 2, -5 DIV 2, 5 DIV -2, -5 DIV -2;
  9. max-sixty commented on May 10, 2023

    @max-sixty
    ContributorAuthor

    If so, why not add a regex operator and then for the MySQL/sqlite dialects parse it as a binary op?

    Initial effort at #874 !

  10. added a commit that references this issue on May 17, 2023
  11. added a commit that references this issue on Nov 6, 2023
  12. LucaCappelletti94 commented on Sep 22, 2026

    @LucaCappelletti94
    Contributor

    Closing as solved by #1017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions