Representing infix `REGEXP`

[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?

[alamb]

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

[max-sixty]

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?

[alamb]

...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

[max-sixty]

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

[max-sixty]

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!

[ankrgyl]

@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?

[eitsupi]

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;

[max-sixty]

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 !