This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 78957058c3 [CALCITE-6923] REGEXP_REPLACE_PG_...: backward references
behave differently than in postgres
78957058c3 is described below
commit 78957058c3dbb67bfc1cfc1b53b5bfa171d2eeb6
Author: Ulrich Kramer <[email protected]>
AuthorDate: Mon Apr 7 10:12:01 2025 +0200
[CALCITE-6923] REGEXP_REPLACE_PG_...: backward references behave
differently than in postgres
---
babel/src/test/resources/sql/big-query.iq | 10 ++++++++++
babel/src/test/resources/sql/postgresql.iq | 5 +++++
.../main/java/org/apache/calcite/runtime/SqlFunctions.java | 12 +++++++++---
.../test/java/org/apache/calcite/test/SqlFunctionsTest.java | 7 +++++++
.../main/java/org/apache/calcite/test/SqlOperatorTest.java | 4 ++++
5 files changed, 35 insertions(+), 3 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index 888f61afdd..e587d50267 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -1458,6 +1458,16 @@ SELECT REGEXP_REPLACE("1=00--20=0", "(-)", "#");
!ok
+SELECT REGEXP_REPLACE("# Heading", "^# ([a-zA-Z0-9\\s]+$)", "<h1>\\1</h1>");
++------------------+
+| EXPR$0 |
++------------------+
+| <h1>Heading</h1> |
++------------------+
+(1 row)
+
+!ok
+
#####################################################################
# REGEXP_SUBSTR(value, regexp[, position[, occurrence]])
#
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index 7cea0ab532..383dbfad1c 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -1257,4 +1257,9 @@ X
X def GHI
!ok
+SELECT regexp_replace('ABC def GHI', '([a-z]+).*', '\1', 'i') AS x;
+X
+ABC
+!ok
+
# End postgresql.iq
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 7609a6a38b..3367a9a77f 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -749,20 +749,25 @@ public String regexpReplace(String s, String regex,
String replacement,
/** SQL {@code REGEXP_REPLACE} function for PostgreSQL with 3 arguments. */
public String regexpReplacePg(String s, String regex, String replacement) {
- return regexpReplace(s, regex, replacement, 1, 1, null);
+ return regexpReplaceNonDollarIndexed(s, regex, replacement, 1, 1, null);
}
/** SQL {@code REGEXP_REPLACE} function for PostgreSQL with 4 arguments. */
public String regexpReplacePg(String s, String regex, String replacement,
String matchType) {
// Translate g flag to occurrence
final int occurrence = matchType.contains("g") ? 0 : 1;
- return regexpReplace(s, regex, replacement, 1, occurrence, matchType);
+ return regexpReplaceNonDollarIndexed(s, regex, replacement, 1,
occurrence, matchType);
}
/** SQL {@code REGEXP_REPLACE} function with 3 arguments with
* {@code \\} based indexing for capturing groups. */
public String regexpReplaceNonDollarIndexed(String s, String regex,
String replacement) {
+ return regexpReplaceNonDollarIndexed(s, regex, replacement, 1, 0, null);
+ }
+
+ private String regexpReplaceNonDollarIndexed(String s, String regex,
+ String replacement, int pos, int occurrence, @Nullable String
matchType) {
// Modify double-backslash capturing group indices in replacement
argument,
// retrieved from cache when available.
String indexedReplacement;
@@ -776,9 +781,10 @@ public String regexpReplaceNonDollarIndexed(String s,
String regex,
}
// Call generic regexp replace method with modified replacement pattern
- return regexpReplace(s, regex, indexedReplacement, 1, 0, null);
+ return regexpReplace(s, regex, indexedReplacement, pos, occurrence,
matchType);
}
+
private static int makeRegexpFlags(String stringFlags) {
int flags = 0;
for (int i = 0; i < stringFlags.length(); ++i) {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 13f5a88744..d68d4b2988 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -616,6 +616,13 @@ static <E> List<E> list() {
assertThat(e.getMessage(),
is("Invalid input for REGEXP_REPLACE: 'WWW'"));
}
+
+ assertThat(f.regexpReplacePg("abc", "a(.*)c", "x\\1x", "i"),
+ is("xbx"));
+ assertThat(f.regexpReplace("abc", "a(.*)c", "x$1x"),
+ is("xbx"));
+ assertThat(f.regexpReplace("abc", "a(.*)c", "x\\1x"),
+ is("x1x"));
}
@Test void testReplaceNonDollarIndexedString() {
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index b02a4d5d35..d04a35e3ac 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -6400,6 +6400,8 @@ private static void checkIf(SqlOperatorFixture f) {
"VARCHAR NOT NULL");
}
+
+
@Test void testRegexpReplace4Func() {
final SqlOperatorFixture f0 = fixture();
final Consumer<SqlOperatorFixture> consumer = f -> {
@@ -6502,6 +6504,8 @@ private static void checkIf(SqlOperatorFixture f) {
"VARCHAR NOT NULL");
f.checkString("regexp_replace('abc\t\ndef\t\nghi', '\\w+', '+')",
"+\t\ndef\t\nghi",
"VARCHAR NOT NULL");
+ f.checkString("regexp_replace('abc', 'a(.*)c', 'x\\1x')", "xbx",
+ "VARCHAR NOT NULL");
f.checkQuery("select regexp_replace('a b c', 'b', 'X')");
}
