feat: MySQL NOT RLIKE, NOT REGEXP expressions

- fixes #1553 - remove RegExpMySQLOperator, replaced by flavoured `LIKE` expression Signed-off-by: Andreas Reichel <andreas@manticore-projects.com>
JSQLParser · manticore-projects · Jun 15, 2023 · Dec 11, 2022 · Dec 22, 2022 · Jan 8, 2023
commit f132547f56a1edd6e6a0e1454b53ac2e422a835b
diff --git a/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitor.java b/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitor.java
@@ -44,7 +44,6 @@
 import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
 import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
 import net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator;
-import net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator;
 import net.sf.jsqlparser.expression.operators.relational.SimilarToExpression;
 import net.sf.jsqlparser.schema.Column;
 import net.sf.jsqlparser.statement.select.AllColumns;
@@ -168,8 +167,6 @@ public interface ExpressionVisitor {
 
     void visit(JsonOperator jsonExpr);
 
-    void visit(RegExpMySQLOperator regExpMySQLOperator);
-
     void visit(UserVariable var);
 
     void visit(NumericBind bind);

diff --git a/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitorAdapter.java b/src/main/java/net/sf/jsqlparser/expression/ExpressionVisitorAdapter.java
@@ -44,7 +44,6 @@
 import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
 import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
 import net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator;
-import net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator;
 import net.sf.jsqlparser.expression.operators.relational.SimilarToExpression;
 import net.sf.jsqlparser.schema.Column;
 import net.sf.jsqlparser.statement.select.AllColumns;
@@ -433,11 +432,6 @@ public void visit(JsonOperator expr) {
         visitBinaryExpression(expr);
     }
 
-    @Override
-    public void visit(RegExpMySQLOperator expr) {
-        visitBinaryExpression(expr);
-    }
-
     @Override
     public void visit(UserVariable var) {
 

diff --git a/src/main/java/net/sf/jsqlparser/expression/operators/relational/LikeExpression.java b/src/main/java/net/sf/jsqlparser/expression/operators/relational/LikeExpression.java
@@ -14,10 +14,18 @@
 import net.sf.jsqlparser.expression.ExpressionVisitor;
 
 public class LikeExpression extends BinaryExpression {
+    public enum KeyWord {
+        LIKE, ILIKE, RLIKE, REGEXP;
+
+        public static KeyWord from(String keyword) {
+            return Enum.valueOf(KeyWord.class, keyword.toUpperCase());
+        }
+    }
 
     private boolean not = false;
+    private boolean useBinary = false;
     private Expression escapeExpression = null;
-    private boolean caseInsensitive = false;
+    private KeyWord likeKeyWord = KeyWord.LIKE;
 
     public boolean isNot() {
         return not;
@@ -27,23 +35,33 @@ public void setNot(boolean b) {
         not = b;
     }
 
+    public boolean isUseBinary() {
+        return useBinary;
+    }
+
+    public LikeExpression setUseBinary(boolean useBinary) {
+        this.useBinary = useBinary;
+        return this;
+    }
+
     @Override
     public void accept(ExpressionVisitor expressionVisitor) {
         expressionVisitor.visit(this);
     }
 
+    @Deprecated
     @Override
     public String getStringExpression() {
-        return caseInsensitive ? "ILIKE" : "LIKE";
+        return likeKeyWord.toString();
     }
 
     @Override
     public String toString() {
-        String retval = getLeftExpression() + " " + (not ? "NOT " : "") + getStringExpression() + " " + getRightExpression();
+        String retval = getLeftExpression() + " " + (not ? "NOT " : "")
+                + likeKeyWord + " " + (useBinary ? "BINARY " : "") + getRightExpression();
         if (escapeExpression != null) {
-            retval += " ESCAPE "  + escapeExpression ;
+            retval += " ESCAPE " + escapeExpression;
         }
-
         return retval;
     }
 
@@ -55,19 +73,36 @@ public void setEscape(Expression escapeExpression) {
         this.escapeExpression = escapeExpression;
     }
 
+    @Deprecated
     public boolean isCaseInsensitive() {
-        return caseInsensitive;
+        return likeKeyWord == KeyWord.ILIKE;
     }
 
+    @Deprecated
     public void setCaseInsensitive(boolean caseInsensitive) {
-        this.caseInsensitive = caseInsensitive;
+        this.likeKeyWord = KeyWord.ILIKE;
+    }
+
+    public KeyWord getLikeKeyWord() {
+        return likeKeyWord;
+    }
+
+    public LikeExpression setLikeKeyWord(KeyWord likeKeyWord) {
+        this.likeKeyWord = likeKeyWord;
+        return this;
+    }
+
+    public LikeExpression setLikeKeyWord(String likeKeyWord) {
+        this.likeKeyWord = KeyWord.from(likeKeyWord);
+        return this;
     }
 
     public LikeExpression withEscape(Expression escape) {
         this.setEscape(escape);
         return this;
     }
 
+    @Deprecated
     public LikeExpression withCaseInsensitive(boolean caseInsensitive) {
         this.setCaseInsensitive(caseInsensitive);
         return this;

diff --git a/src/main/java/net/sf/jsqlparser/expression/operators/relational/RegExpMySQLOperator.java b/src/main/java/net/sf/jsqlparser/expression/operators/relational/RegExpMySQLOperator.java
diff --git a/src/main/java/net/sf/jsqlparser/util/TablesNamesFinder.java b/src/main/java/net/sf/jsqlparser/util/TablesNamesFinder.java
@@ -96,7 +96,6 @@
 import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
 import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
 import net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator;
-import net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator;
 import net.sf.jsqlparser.expression.operators.relational.SimilarToExpression;
 import net.sf.jsqlparser.schema.Column;
 import net.sf.jsqlparser.schema.Table;
@@ -659,11 +658,6 @@ public void visit(RegExpMatchOperator rexpr) {
         visitBinaryExpression(rexpr);
     }
 
-    @Override
-    public void visit(RegExpMySQLOperator rexpr) {
-        visitBinaryExpression(rexpr);
-    }
-
     @Override
     public void visit(JsonExpression jsonExpr) {
 

diff --git a/src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java b/src/main/java/net/sf/jsqlparser/util/deparser/ExpressionDeParser.java
@@ -98,7 +98,6 @@
 import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
 import net.sf.jsqlparser.expression.operators.relational.OldOracleJoinBinaryExpression;
 import net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator;
-import net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator;
 import net.sf.jsqlparser.expression.operators.relational.SimilarToExpression;
 import net.sf.jsqlparser.expression.operators.relational.SupportsOldOracleJoinSyntax;
 import net.sf.jsqlparser.schema.Column;
@@ -330,8 +329,17 @@ public void visit(JdbcParameter jdbcParameter) {
 
     @Override
     public void visit(LikeExpression likeExpression) {
-        visitBinaryExpression(likeExpression, (likeExpression.isNot() ? " NOT" : "")
-                + (likeExpression.isCaseInsensitive() ? " ILIKE " : " LIKE "));
+        likeExpression.getLeftExpression().accept(this);
+        buffer.append(" ");
+        if (likeExpression.isNot()) {
+            buffer.append("NOT ");
+        }
+        buffer.append(likeExpression.getLikeKeyWord()).append(" ");
+        if (likeExpression.isUseBinary()) {
+            buffer.append("BINARY ");
+        }
+        likeExpression.getRightExpression().accept(this);
+
         Expression escape = likeExpression.getEscape();
         if (escape != null) {
             buffer.append(" ESCAPE ");
@@ -847,10 +855,6 @@ public void visit(RegExpMatchOperator rexpr) {
         visitBinaryExpression(rexpr, " " + rexpr.getStringExpression() + " ");
     }
 
-    @Override
-    public void visit(RegExpMySQLOperator rexpr) {
-        visitBinaryExpression(rexpr, " " + rexpr.getStringExpression() + " ");
-    }
 
     @Override
     public void visit(JsonExpression jsonExpr) {

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/validator/ExpressionValidator.java b/src/main/java/net/sf/jsqlparser/util/validation/validator/ExpressionValidator.java
@@ -99,7 +99,6 @@
 import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
 import net.sf.jsqlparser.expression.operators.relational.OldOracleJoinBinaryExpression;
 import net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator;
-import net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator;
 import net.sf.jsqlparser.expression.operators.relational.SimilarToExpression;
 import net.sf.jsqlparser.expression.operators.relational.SupportsOldOracleJoinSyntax;
 import net.sf.jsqlparser.parser.feature.Feature;
@@ -481,11 +480,6 @@ public void visit(RegExpMatchOperator rexpr) {
         visitBinaryExpression(rexpr, " " + rexpr.getStringExpression() + " ");
     }
 
-    @Override
-    public void visit(RegExpMySQLOperator rexpr) {
-        visitBinaryExpression(rexpr, " " + rexpr.getStringExpression() + " ");
-    }
-
     @Override
     public void visit(JsonExpression jsonExpr) {
         validateOptionalExpression(jsonExpr.getExpression());

diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -3270,8 +3270,6 @@ Expression RegularCondition() #RegularCondition:
     | token=<OP_NOTEQUALSBANG> { result = new NotEqualsTo(token.image); }
     | "@@" { result = new Matches(); }
     | "~" { result = new RegExpMatchOperator(RegExpMatchOperatorType.MATCH_CASESENSITIVE); }
-    | [<K_NOT> { not=true; } ] <K_REGEXP> [ LOOKAHEAD(2) <K_BINARY> { binary=true; } ] { result = new RegExpMySQLOperator(not, binary?RegExpMatchOperatorType.MATCH_CASESENSITIVE:RegExpMatchOperatorType.MATCH_CASEINSENSITIVE); }
-    | <K_RLIKE> [ LOOKAHEAD(2) <K_BINARY> { binary=true; } ] { result = new RegExpMySQLOperator(binary?RegExpMatchOperatorType.MATCH_CASESENSITIVE:RegExpMatchOperatorType.MATCH_CASEINSENSITIVE).useRLike(); }
     | "~*" { result = new RegExpMatchOperator(RegExpMatchOperatorType.MATCH_CASEINSENSITIVE); }
     | "!~" { result = new RegExpMatchOperator(RegExpMatchOperatorType.NOT_MATCH_CASESENSITIVE); }
     | "!~*" { result = new RegExpMatchOperator(RegExpMatchOperatorType.NOT_MATCH_CASEINSENSITIVE); }
@@ -3400,7 +3398,15 @@ Expression LikeExpression(Expression leftExpression) #LikeExpression:
     Token token;
 }
 {
-    [<K_NOT> { result.setNot(true); } ] ( <K_LIKE> | <K_ILIKE> { result.setCaseInsensitive(true); } ) rightExpression=SimpleExpression()
+    [<K_NOT> { result.setNot(true); } ]
+    (
+        token = <K_LIKE>
+        | token = <K_ILIKE>
+        | token = <K_RLIKE>
+        | token = <K_REGEXP>
+    ) { result.setLikeKeyWord( LikeExpression.KeyWord.from(token.image)); }
+    [ <K_BINARY> {result.setUseBinary(true); } ]
+    rightExpression=SimpleExpression()
     [ LOOKAHEAD(2) <K_ESCAPE>
         (
             LOOKAHEAD(2)  token = <S_CHAR_LITERAL> { result.setEscape( new StringValue( token.image ) ); }

diff --git a/src/test/java/net/sf/jsqlparser/expression/operators/relational/LikeExpressionTest.java b/src/test/java/net/sf/jsqlparser/expression/operators/relational/LikeExpressionTest.java
@@ -13,6 +13,7 @@
 import net.sf.jsqlparser.expression.Expression;
 import net.sf.jsqlparser.expression.StringValue;
 import net.sf.jsqlparser.parser.CCJSqlParserUtil;
+import net.sf.jsqlparser.test.TestUtils;
 import org.junit.jupiter.api.Test;
 
 import static org.junit.jupiter.api.Assertions.*;
@@ -32,12 +33,20 @@ public void testLikeNotIssue660() {
 
     @Test
     public void testSetEscapeAndGetStringExpression() throws JSQLParserException {
-        LikeExpression instance  = (LikeExpression) CCJSqlParserUtil.parseExpression("name LIKE 'J%$_%'");
+        LikeExpression instance =
+                (LikeExpression) CCJSqlParserUtil.parseExpression("name LIKE 'J%$_%'");
         // escape character should be $
         Expression instance2 = new StringValue("$");
         instance.setEscape(instance2);
 
-        // match all records with names that start with letter ’J’ and have the ’_’ character in them
+        // match all records with names that start with letter ’J’ and have the ’_’ character in
+        // them
         assertEquals("name LIKE 'J%$_%' ESCAPE '$'", instance.toString());
     }
+
+    @Test
+    void testNotRLikeIssue1553() throws JSQLParserException {
+        String sqlStr = "select * from test where id  not rlike '111'";
+        TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
+    }
 }
diff --git a/src/test/java/net/sf/jsqlparser/statement/builder/ReflectionModelTest.java b/src/test/java/net/sf/jsqlparser/statement/builder/ReflectionModelTest.java
@@ -118,8 +118,6 @@ public class ReflectionModelTest {
             new net.sf.jsqlparser.expression.operators.relational.NotEqualsTo(),
             new net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator(
                     RegExpMatchOperatorType.MATCH_CASEINSENSITIVE),
-            new net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator(
-                    RegExpMatchOperatorType.NOT_MATCH_CASESENSITIVE),
             new net.sf.jsqlparser.expression.operators.relational.SimilarToExpression(),
             new net.sf.jsqlparser.schema.Column(), new net.sf.jsqlparser.schema.Database("db"),
             new net.sf.jsqlparser.schema.Sequence(),