This is an automated email from the ASF dual-hosted git repository.
zhehu 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 d83122b86e [CALCITE-6791] Search pattern during matching in REPLACE
function should be case insensitive in MSSQL
d83122b86e is described below
commit d83122b86e8030ffb12314ca5e7414a19b95f218
Author: Zhe Hu <[email protected]>
AuthorDate: Tue Jan 21 09:45:51 2025 +0800
[CALCITE-6791] Search pattern during matching in REPLACE function should be
case insensitive in MSSQL
---
.../calcite/adapter/enumerable/RexImpTable.java | 21 ++++++++++++++++++-
.../org/apache/calcite/runtime/SqlFunctions.java | 9 ++++++--
.../calcite/sql/fun/SqlStdOperatorTable.java | 8 +++++++-
.../org/apache/calcite/util/BuiltInMethod.java | 2 +-
.../org/apache/calcite/test/CoreQuidemTest.java | 9 ++++++++
.../org/apache/calcite/test/SqlFunctionsTest.java | 14 ++++++++-----
core/src/test/resources/sql/functions.iq | 22 ++++++++++++++++++++
site/_docs/reference.md | 2 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 24 ++++++++++++++++++++++
9 files changed, 100 insertions(+), 11 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 3c071376b8..1a84edc3d6 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -73,6 +73,7 @@
import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlUserDefinedAggFunction;
import org.apache.calcite.sql.validate.SqlUserDefinedFunction;
import org.apache.calcite.sql.validate.SqlUserDefinedTableFunction;
@@ -699,7 +700,7 @@ void populate1() {
defineMethod(RPAD, BuiltInMethod.RPAD.method, NullPolicy.STRICT);
defineMethod(STARTS_WITH, BuiltInMethod.STARTS_WITH.method,
NullPolicy.STRICT);
defineMethod(ENDS_WITH, BuiltInMethod.ENDS_WITH.method,
NullPolicy.STRICT);
- defineMethod(REPLACE, BuiltInMethod.REPLACE.method, NullPolicy.STRICT);
+ define(REPLACE, new ReplaceImplementor());
defineMethod(TRANSLATE3, BuiltInMethod.TRANSLATE3.method,
NullPolicy.STRICT);
defineMethod(CHR, BuiltInMethod.CHAR_FROM_UTF8.method,
NullPolicy.STRICT);
defineMethod(CHARACTER_LENGTH, BuiltInMethod.CHAR_LENGTH.method,
@@ -4967,4 +4968,22 @@ private static class ToTimestampPgImplementor extends
AbstractRexCallImplementor
return Expressions.call(target, method, operand0, operand1);
}
}
+
+ /** Implementor for the {@code REPLACE} function for Oracle, PostgreSQL and
+ * Microsoft SQL Server. And search pattern for SQL Server is
case-insensitive. */
+ private static class ReplaceImplementor extends AbstractRexCallImplementor {
+ ReplaceImplementor() {
+ super("replace", NullPolicy.STRICT, false);
+ }
+
+ @Override Expression implementSafe(RexToLixTranslator translator, RexCall
call,
+ List<Expression> argValueList) {
+ boolean isCaseSensitive = translator.conformance !=
SqlConformanceEnum.SQL_SERVER_2008;
+ final Expression operand0 = argValueList.get(0);
+ final Expression operand1 = argValueList.get(1);
+ final Expression operand2 = argValueList.get(2);
+ return Expressions.call(BuiltInMethod.REPLACE.method,
+ operand0, operand1, operand2, Expressions.constant(isCaseSensitive));
+ }
+ }
}
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 4b82aca272..e664c1a6f3 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -5781,11 +5781,16 @@ public static String translate3(String s, String
search, String replacement) {
}
/** SQL {@code REPLACE(string, search, replacement)} function. */
- public static String replace(String s, String search, String replacement) {
+ public static String replace(String s, String search, String replacement,
+ boolean isCaseSensitive) {
if (search.isEmpty()) {
return s;
}
- return s.replace(search, replacement);
+ if (isCaseSensitive) {
+ return s.replace(search, replacement);
+ }
+ // for MSSQL's REPLACE function, search pattern is case-insensitive during
matching
+ return org.apache.commons.lang3.StringUtils.replaceIgnoreCase(s, search,
replacement);
}
/** Helper for "array element reference". Caller has already ensured that
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 977db8ac1f..204900e7f4 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1697,7 +1697,13 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
public static final SqlFunction SUBSTRING = new SqlSubstringFunction();
/** The {@code REPLACE(string, search, replace)} function. Not standard SQL,
- * but in Oracle, PostgreSQL and Microsoft SQL Server. */
+ * but in Oracle, PostgreSQL and Microsoft SQL Server.
+ *
+ * <p>REPLACE behaves a little different in Microsoft SQL Server,
+ * whose search pattern is case-insensitive during matching.
+ *
+ * <p>For example, {@code REPLACE(('ciAao', 'a', 'ciao'))} returns
"ciAciaoo" in both
+ * Oracle and PostgreSQL, but returns "ciciaociaoo" in Microsoft SQL Server.
*/
public static final SqlFunction REPLACE =
SqlBasicFunction.create("REPLACE", ReturnTypes.VARCHAR_NULLABLE,
OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 6f3fbd71c7..8eceedff48 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -586,7 +586,7 @@ public enum BuiltInMethod {
TRIM(SqlFunctions.class, "trim", boolean.class, boolean.class, String.class,
String.class, boolean.class),
REPLACE(SqlFunctions.class, "replace", String.class, String.class,
- String.class),
+ String.class, boolean.class),
TRANSLATE_WITH_CHARSET(SqlFunctions.class, "translateWithCharset",
String.class, String.class),
TRANSLATE3(SqlFunctions.class, "translate3", String.class, String.class,
String.class),
LTRIM(SqlFunctions.class, "ltrim", String.class),
diff --git a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
index abcd2fd6ef..9d19ec653e 100644
--- a/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
+++ b/core/src/test/java/org/apache/calcite/test/CoreQuidemTest.java
@@ -132,6 +132,15 @@ public static void main(String[] args) throws Exception {
SqlConformanceEnum.ORACLE_10)
.with(CalciteAssert.Config.SCOTT)
.connect();
+ case "scott-mssql":
+ // Same as "scott", but uses SQL_SERVER_2008 conformance.
+ return CalciteAssert.that()
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ ExtensionDdlExecutor.class.getName() + "#PARSER_FACTORY")
+ .with(CalciteConnectionProperty.CONFORMANCE,
+ SqlConformanceEnum.SQL_SERVER_2008)
+ .with(CalciteAssert.Config.SCOTT)
+ .connect();
case "steelwheels":
return CalciteAssert.that()
.with(CalciteConnectionProperty.PARSER_FACTORY,
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 6e55a052d8..439560d9c3 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -560,11 +560,15 @@ static <E> List<E> list() {
}
@Test void testReplace() {
- assertThat(replace("", "ciao", "ci"), is(""));
- assertThat(replace("ciao", "ciao", ""), is(""));
- assertThat(replace("ciao", "", "ciao"), is("ciao"));
- assertThat(replace("ci ao", " ", "ciao"), is("ciciaoao"));
- assertThat(replace("hello world", "o", ""), is("hell wrld"));
+ assertThat(replace("", "ciao", "ci", true), is(""));
+ assertThat(replace("ciao", "ciao", "", true), is(""));
+ assertThat(replace("ciao", "", "ciao", true), is("ciao"));
+ assertThat(replace("ci ao", " ", "ciao", true), is("ciciaoao"));
+ assertThat(replace("ciAao", "a", "ciao", true), is("ciAciaoo"));
+ assertThat(replace("ciAao", "A", "ciao", true), is("ciciaoao"));
+ assertThat(replace("ciAao", "a", "ciao", false), is("ciciaociaoo"));
+ assertThat(replace("ciAao", "A", "ciao", false), is("ciciaociaoo"));
+ assertThat(replace("hello world", "o", "", true), is("hell wrld"));
}
@Test void testRegexpReplace() {
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index b9ba622f33..1d6ea00768 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -886,6 +886,28 @@ from t;
!ok
+# [CALCITE-6791] Search pattern during matching in REPLACE function should be
case insensitive in MSSQL
+!use scott-mssql
+select replace('ciAao', 'a', 'ciao');
++-------------+
+| EXPR$0 |
++-------------+
+| ciciaociaoo |
++-------------+
+(1 row)
+
+!ok
+
+select replace('ciAao', 'A', 'ciao');
++-------------+
+| EXPR$0 |
++-------------+
+| ciciaociaoo |
++-------------+
+(1 row)
+
+!ok
+
# concat in BigQuery
!use post-big-query
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 6d1148e764..48d2d62a97 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1914,7 +1914,7 @@ #### String
| {fn LOCATE(string1, string2 [, integer])} | Returns the position in
*string2* of the first occurrence of *string1*. Searches from the beginning of
*string2*, unless *integer* is specified.
| {fn LEFT(string, length)} | Returns the leftmost *length* characters from
*string*
| {fn LTRIM(string)} | Returns *string* with leading space characters removed
-| {fn REPLACE(string, search, replacement)} | Returns a string in which all
the occurrences of *search* in *string* are replaced with *replacement*;
returns unchanged *string* if *search* is an empty string(''); if *replacement*
is the empty string, the occurrences of *search* are removed
+| {fn REPLACE(string, search, replacement)} | Returns a string in which all
the occurrences of *search* in *string* are replaced with *replacement*;
returns unchanged *string* if *search* is an empty string(''); if *replacement*
is the empty string, the occurrences of *search* are removed. Matching between
*search* and *string* is case-insensitive under SQL Server semantics
| {fn REVERSE(string)} | Returns *string* with the order of the characters
reversed
| {fn RIGHT(string, length)} | Returns the rightmost *length* characters from
*string*
| {fn RTRIM(string)} | Returns *string* with trailing space characters removed
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 3512f6a9e4..1fbded454a 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4832,6 +4832,30 @@ static void checkRlikeFails(SqlOperatorFixture f) {
* REPLACE function returns wrong result when search pattern is an empty
string</a>. */
@Test void testReplaceFunc() {
final SqlOperatorFixture f = fixture();
+ checkReplaceFunc(f);
+ // case-sensitive
+ f.checkString("REPLACE('ciAao', 'a', 'ciao')", "ciAciaoo",
+ "VARCHAR NOT NULL");
+ f.checkString("REPLACE('ciAao', 'A', 'ciao')", "ciciaoao",
+ "VARCHAR NOT NULL");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6791";>[CALCITE-6791]
+ * Search pattern during matching in REPLACE function should be case
insensitive
+ * in MSSQL</a>. */
+ @Test void testReplaceMSSQLFunc() {
+ final SqlOperatorFixture f = fixture();
+ checkReplaceFunc(f);
+ // case-insensitive
+ SqlOperatorFixture f1 =
f.withConformance(SqlConformanceEnum.SQL_SERVER_2008);
+ f1.checkString("REPLACE('ciAao', 'a', 'ciao')", "ciciaociaoo",
+ "VARCHAR NOT NULL");
+ f1.checkString("REPLACE('ciAao', 'A', 'ciao')", "ciciaociaoo",
+ "VARCHAR NOT NULL");
+ }
+
+ private static void checkReplaceFunc(SqlOperatorFixture f) {
f.setFor(SqlStdOperatorTable.REPLACE, VmName.EXPAND);
f.checkString("REPLACE('ciao', 'ciao', '')", "",
"VARCHAR NOT NULL");
