You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "jduo (via GitHub)" <gi...@apache.org> on 2024/05/27 18:00:22 UTC

[PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

jduo opened a new pull request, #3801:
URL: https://github.com/apache/calcite/pull/3801

   PostgreSQL allows use of the flags string at any parameter after the replacement string.


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687167899


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlPgRegexpReplaceFunction.java:
##########
@@ -0,0 +1,81 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * The PostgreSQL
+ * REGEXP_REPLACE(source_string, pattern, replacement [, pos, [, occurrence]] [, match_type])
+ * searches for a regular expression pattern and replaces every occurrence of the pattern
+ * with the specified string. It differs from the standard REGEXP_REPLACE in that there is

Review Comment:
   This class is not needed. It has been removed along with its parent class that is also no longer needed.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687154924


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,11 +563,86 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
-  /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
+  /** The "REGEXP_REPLACE(value, regexp)"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
-  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
-  public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, pos)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_4 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.STRING,
+              SqlTypeFamily.STRING, SqlTypeFamily.INTEGER),
+          0, SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, pos, occurrence)"
+   * function. Replaces all substrings of value that match regexp with

Review Comment:
   Added comments for each of the arguments.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2251297528

   From my pov the commits can be squashed


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2253187072

   I have squashed the commits.


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687154726


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,11 +563,86 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
-  /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
+  /** The "REGEXP_REPLACE(value, regexp)"

Review Comment:
   The comment has been updated.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2226176176

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [6 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&resolved=false&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&resolutions=WONTFIX)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&resolved=false&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [71.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [5.5% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2223702244

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [5 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&resolved=false&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&resolutions=WONTFIX)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&resolved=false&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [92.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [11.3% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1674706234


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,12 +563,58 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
+  /** The "REGEXP_REPLACE(value, regexp)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
   /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
   @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
   public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();

Review Comment:
   Remove the `SqlRegexpReplaceFunction` class.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1676332678


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,12 +563,58 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
+  /** The "REGEXP_REPLACE(value, regexp)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {REDSHIFT})

Review Comment:
   Enabled.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2243845621

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [7 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [73.7% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [4.8% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2246782814

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [5 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687154371


##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -630,6 +630,11 @@ public int regexpInstr(String value, String regex, int position,
       return matchIndex;
     }
 
+    /** SQL {@code REGEXP_REPLACE} function with 2 arguments. */
+    public String regexpReplace(String s, String regex) {
+      return regexpReplace(s, regex, "", 1, 0, null);

Review Comment:
   I think that could easily evolve into a 1 year project just by itself.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2223905686

   Updated with the following behaviour:
   
   ### Amazon Redshift
   * Can provide only two arguments. The replacement string will be the empty string.
   
   https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html
   
   ### PostgreSQL
   * With 3 arguments, only the first occurrence is replaced
   * With 4 arguments, the last argument is flags. If flags contains `g`, then all occurrences will be replaced.
   * Cannot provide more than 4 arguments
   
   ### BigQuery, MySQL, Oracle, Amazon Redshift
   * Must provide at least 3 arguments
   * No change to behaviour
   
   https://www.postgresql.org/docs/14/functions-matching.html#FUNCTIONS-POSIX-REGEXP


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1685010516


##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -630,6 +630,11 @@ public int regexpInstr(String value, String regex, int position,
       return matchIndex;
     }
 
+    /** SQL {@code REGEXP_REPLACE} function with 2 arguments. */
+    public String regexpReplace(String s, String regex) {
+      return regexpReplace(s, regex, "", 1, 0, null);

Review Comment:
   This is all predicated on regexpReplace doing the right thing.
   I guess that is not in the scope of this PR, but all these regular expression languages have slight differences from each other, so it's not obvious to me that regexpReplace implements faithfully what each of these databases expects.



##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,11 +563,86 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
-  /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
+  /** The "REGEXP_REPLACE(value, regexp)"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
-  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
-  public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, pos)"
+   * function. Replaces all substrings of value that match regexp with

Review Comment:
   what is pos?



##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,11 +563,86 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
-  /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
+  /** The "REGEXP_REPLACE(value, regexp)"

Review Comment:
   this comment cannot be right, since there is no "rep"



##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,11 +563,86 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
-  /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
+  /** The "REGEXP_REPLACE(value, regexp)"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
-  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
-  public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, pos)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_4 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.family(SqlTypeFamily.STRING, SqlTypeFamily.STRING,
+              SqlTypeFamily.STRING, SqlTypeFamily.INTEGER),
+          0, SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, pos, occurrence)"
+   * function. Replaces all substrings of value that match regexp with

Review Comment:
   please document all arguments in these comments



##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -5727,10 +5727,32 @@ private static void checkIf(SqlOperatorFixture f) {
     f.checkQuery("select regexp_instr('a9cadca5c4aecghi', 'a[0-9]c', 1, 3)");
   }
 
-  @Test void testRegexpReplaceFunc() {
+  @Test void testRegexpReplace2Func() {
     final SqlOperatorFixture f0 = fixture();
     final Consumer<SqlOperatorFixture> consumer = f -> {
-      f.setFor(SqlLibraryOperators.REGEXP_REPLACE);
+      f.setFor(SqlLibraryOperators.REGEXP_REPLACE_2);

Review Comment:
   I am assuming these were validated against postgres. If true, please add a comment.



##########
core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java:
##########
@@ -105,10 +105,6 @@ public abstract class SqlAbstractConformance implements SqlConformance {
     return SqlConformanceEnum.DEFAULT.isOffsetLimitAllowed();
   }
 
-  @Override public boolean isRegexReplaceCaptureGroupDollarIndexed() {

Review Comment:
   where did the functionality of this move?



##########
core/src/main/java/org/apache/calcite/sql/fun/SqlPgRegexpReplaceFunction.java:
##########
@@ -0,0 +1,81 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * The PostgreSQL
+ * REGEXP_REPLACE(source_string, pattern, replacement [, pos, [, occurrence]] [, match_type])
+ * searches for a regular expression pattern and replaces every occurrence of the pattern
+ * with the specified string. It differs from the standard REGEXP_REPLACE in that there is

Review Comment:
   And here too



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu merged PR #3801:
URL: https://github.com/apache/calcite/pull/3801


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1688658651


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,11 +563,86 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
-  /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
+  /** The "REGEXP_REPLACE(value, regexp)"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
-  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
-  public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, pos)"
+   * function. Replaces all substrings of value that match regexp with

Review Comment:
   Updated the Javadoc comment here.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687172299


##########
core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java:
##########
@@ -105,10 +105,6 @@ public abstract class SqlAbstractConformance implements SqlConformance {
     return SqlConformanceEnum.DEFAULT.isOffsetLimitAllowed();
   }
 
-  @Override public boolean isRegexReplaceCaptureGroupDollarIndexed() {

Review Comment:
   There is a specific library function for BigQuery. BigQuery only supports the 3 argument signature. BigQuery was the only engine that uses dollar signs rather than backslash.
   
   https://github.com/Bit-Quill/calcite/blob/calcite-6310-regex-replace/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java#L642
   
   https://github.com/Bit-Quill/calcite/blob/calcite-6310-regex-replace/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java#L590



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687175499


##########
testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java:
##########
@@ -5727,10 +5727,32 @@ private static void checkIf(SqlOperatorFixture f) {
     f.checkQuery("select regexp_instr('a9cadca5c4aecghi', 'a[0-9]c', 1, 3)");
   }
 
-  @Test void testRegexpReplaceFunc() {
+  @Test void testRegexpReplace2Func() {
     final SqlOperatorFixture f0 = fixture();
     final Consumer<SqlOperatorFixture> consumer = f -> {
-      f.setFor(SqlLibraryOperators.REGEXP_REPLACE);
+      f.setFor(SqlLibraryOperators.REGEXP_REPLACE_2);

Review Comment:
   I took another look and realized that it is only Amazon Redshift that supports a 2 argument signature. I have updated accordingly. I have also tested other engines.
   * With 4 arguments, all engines expect a signature of "string, string, string, int"
   * Oracle only supports the signature "string, string, string, int, int" for 5 arguments
   * MySQL and Amazon Redshift support two signature for 5 arguments
     * "string, string, string, int, int"
     * "string, string, string, int, string"
   
   Updated to better match the signatures each engine supports.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2240186161

   > If this PR is ready for review please make a sign
   
   Yes, it is ready. Please take a look when you have time.


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1687153303


##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -630,6 +630,11 @@ public int regexpInstr(String value, String regex, int position,
       return matchIndex;
     }
 
+    /** SQL {@code REGEXP_REPLACE} function with 2 arguments. */
+    public String regexpReplace(String s, String regex) {
+      return regexpReplace(s, regex, "", 1, 0, null);

Review Comment:
   You are correct, there may be functional differences between the implementation of `regexp_replace` here and what the engines actually have. I would like to leave that out of scope for this PR.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2226205343

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [6 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&resolved=false&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&resolutions=WONTFIX)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&resolved=false&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [71.2% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [5.5% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1676332438


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,12 +563,58 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
+  /** The "REGEXP_REPLACE(value, regexp)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
   /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
   @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
   public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();

Review Comment:
   Removed. This class was looking at the `SqlConformanceEnum` to decide on how capture groups are specified in the replacement. BigQuery uses `\\` and others use `$`. There is now a new LibraryOperator for BigQuery so that `\\` in the replacement is converted to `$` before doing the replacement.



##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,12 +563,58 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
+  /** The "REGEXP_REPLACE(value, regexp)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
   /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
   @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
   public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
 
+  /** The "REGEXP_REPLACE(value, regexp, rep [, flags])"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = REDSHIFT)
+  public static final SqlFunction REGEXP_REPLACE_PG_3 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, flags)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = REDSHIFT)
+  public static final SqlFunction REGEXP_REPLACE_PG_4 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The PostgreSQL variant of
+   * "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence]] [, matchType])"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction PG_REGEXP_REPLACE = new SqlPgRegexpReplaceFunction();

Review Comment:
   Removed.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1674706865


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,12 +563,58 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
+  /** The "REGEXP_REPLACE(value, regexp)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_2 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
+  public static final SqlFunction REGEXP_REPLACE_3 =
+      SqlBasicFunction.create("REGEXP_REPLACE", ReturnTypes.VARCHAR_NULLABLE,
+          OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
   /** The "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]])"
    * function. Replaces all substrings of value that match regexp with
    * {@code rep} and returns modified value. */
   @LibraryOperator(libraries = {BIG_QUERY, MYSQL, ORACLE, REDSHIFT})
   public static final SqlFunction REGEXP_REPLACE = new SqlRegexpReplaceFunction();
 
+  /** The "REGEXP_REPLACE(value, regexp, rep [, flags])"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = REDSHIFT)
+  public static final SqlFunction REGEXP_REPLACE_PG_3 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The "REGEXP_REPLACE(value, regexp, rep, flags)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {POSTGRESQL}, exceptLibraries = REDSHIFT)
+  public static final SqlFunction REGEXP_REPLACE_PG_4 =
+      new SqlBasicFunction("REGEXP_REPLACE", SqlKind.OTHER_FUNCTION,
+          SqlSyntax.FUNCTION, true, ReturnTypes.VARCHAR_NULLABLE, null,
+          OperandHandlers.DEFAULT, OperandTypes.STRING_STRING_STRING_STRING, 0,
+          SqlFunctionCategory.STRING, call -> SqlMonotonicity.NOT_MONOTONIC, false) { };
+
+  /** The PostgreSQL variant of
+   * "REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence]] [, matchType])"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {POSTGRESQL})
+  public static final SqlFunction PG_REGEXP_REPLACE = new SqlPgRegexpReplaceFunction();

Review Comment:
   Remove this, since it is not used.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2240145167

   If this PR is ready for review please make a sign


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2243872740

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [5 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "normanj-bitquill (via GitHub)" <gi...@apache.org>.
normanj-bitquill commented on code in PR #3801:
URL: https://github.com/apache/calcite/pull/3801#discussion_r1674707220


##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -563,12 +563,58 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding operatorBinding,
           OperandTypes.STRING_STRING_OPTIONAL_INTEGER_OPTIONAL_INTEGER_OPTIONAL_INTEGER,
           SqlFunctionCategory.STRING);
 
+  /** The "REGEXP_REPLACE(value, regexp)"
+   * function. Replaces all substrings of value that match regexp with
+   * {@code rep} and returns modified value. */
+  @LibraryOperator(libraries = {REDSHIFT})

Review Comment:
   Also enable for PostgreSQL.



-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "jduo (via GitHub)" <gi...@apache.org>.
jduo commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2133891217

   Initial PR with only validation implemented.


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] CALCITE-6310: Add REGEXP_REPLACE function (enabled in PostgreSQL library) [calcite]

Posted by "sonarcloud[bot] (via GitHub)" <gi...@apache.org>.
sonarcloud[bot] commented on PR #3801:
URL: https://github.com/apache/calcite/pull/3801#issuecomment-2253088997

   ## [![Quality Gate Passed](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/QualityGateBadge/qg-passed-20px.png 'Quality Gate Passed')](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801) **Quality Gate passed**  
   Issues  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [5 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/accepted-16px.png '') [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=3801&issueStatuses=ACCEPTED)
   
   Measures  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=3801&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [100.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_coverage&view=list)  
   ![](https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/passed-16px.png '') [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=3801&metric=new_duplicated_lines_density&view=list)  
     
   [See analysis details on SonarCloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=3801)
   
   


-- 
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: commits-unsubscribe@calcite.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org