This is an automated email from the ASF dual-hosted git repository.
xiong 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 b852db228c [CALCITE-6780] AbstractSqlTester fails to build query for
expression TRIM(string)
b852db228c is described below
commit b852db228c3cb6194c7e00057178a2e628f9be96
Author: Zhe Hu <[email protected]>
AuthorDate: Thu Jan 16 21:51:15 2025 +0800
[CALCITE-6780] AbstractSqlTester fails to build query for expression
TRIM(string)
---
.../apache/calcite/runtime/CalciteResource.java | 4 +--
.../org/apache/calcite/runtime/SqlFunctions.java | 2 +-
.../calcite/runtime/CalciteResource.properties | 2 +-
.../apache/calcite/sql/test/AbstractSqlTester.java | 5 ++++
.../org/apache/calcite/test/SqlOperatorTest.java | 34 ++++++++++++----------
5 files changed, 28 insertions(+), 19 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 0f0dbaacc5..d927ab0b3f 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -948,8 +948,8 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("Illegal arguments: The length of the keys array {0,number,#}
is not equal to the length of the values array {1,number,#} in MAP_FROM_ARRAYS
function")
ExInst<CalciteException> illegalArgumentsInMapFromArraysFunc(int arg0, int
arg1);
- @BaseMessage("Trim error: trim character must be exactly 1 character")
- ExInst<CalciteException> trimError();
+ @BaseMessage("Invalid argument ''{0}'': the length of the string describing
the trimmed character must be 1")
+ ExInst<CalciteException> trimError(String seek);
@BaseMessage("Invalid types for arithmetic: {0} {1} {2}")
ExInst<CalciteException> invalidTypesForArithmetic(String clazzName0, String
op,
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 1f8a125816..4b82aca272 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1818,7 +1818,7 @@ public static String trim(boolean left, boolean right,
String seek,
public static String trim(boolean left, boolean right, String seek,
String s, boolean strict) {
if (strict && seek.length() != 1) {
- throw RESOURCE.trimError().ex();
+ throw RESOURCE.trimError(seek).ex();
}
int j = s.length();
if (right) {
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index cda103f7df..462af08ac1 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -309,7 +309,7 @@ IllegalMapValuesWithNullKey=Illegal arguments for
'MAP_VALUES' function: using a
InvalidPrecisionForDecimalType=DECIMAL precision {0,number,#} must be between
1 and {1,number,#}
InvalidScaleForDecimalType=DECIMAL scale {0,number,#} must be between
{1,number,#} and {2,number,#}
IllegalArgumentsInMapFromArraysFunc=Illegal arguments: The length of the keys
array {0,number,#} is not equal to the length of the values array {1,number,#}
in MAP_FROM_ARRAYS function
-TrimError=Trim error: trim character must be exactly 1 character
+TrimError=Invalid argument ''{0}'': the length of the string describing the
trimmed character must be 1
InvalidTypesForArithmetic=Invalid types for arithmetic: {0} {1} {2}
InvalidTypesForComparison=Invalid types for comparison: {0} {1} {2}
CannotConvert=Cannot convert {0} to {1}
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
index 8f7e2699cd..7f7191f9c1 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/AbstractSqlTester.java
@@ -385,6 +385,11 @@ protected String buildQuery2(SqlTestFactory factory,
String expression) {
&& isNull(call.operand(0))) {
literalSet.add(call);
return call;
+ } else if (operator == SqlStdOperatorTable.TRIM) {
+ // see
https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6780
+ // don't extract trimmed literal for TRIM function
+ call.operand(2).accept(this);
+ return call;
} else if (ops.contains(operator)) {
// "Argument to function 'LOCALTIME' must be a
// literal"
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 bc68c4b47c..3512f6a9e4 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -11706,31 +11706,35 @@ void assertSubFunReturns(boolean binary, String s,
int start,
// SQL:2003 6.29.11 Trimming a CHAR yields a VARCHAR
f.checkString("trim('a' from 'aAa')", "A", "VARCHAR(3) NOT NULL");
f.checkString("trim(both 'a' from 'aAa')", "A", "VARCHAR(3) NOT NULL");
+ f.checkString("trim(' aAa ')", "aAa", "VARCHAR(5) NOT NULL");
+ f.checkString("trim(both ' ' from ' aAa ')", "aAa", "VARCHAR(5) NOT NULL");
f.checkString("trim(leading 'a' from 'aAa')", "Aa", "VARCHAR(3) NOT NULL");
f.checkString("trim(trailing 'a' from 'aAa')", "aA", "VARCHAR(3) NOT
NULL");
+ f.checkNull("trim(null)");
f.checkNull("trim(cast(null as varchar(1)) from 'a')");
f.checkNull("trim('a' from cast(null as varchar(1)))");
- // SQL:2003 6.29.9: trim string must have length=1. Failure occurs
- // at runtime.
- //
- // TODO: Change message to "Invalid argument\(s\) for
- // 'TRIM' function".
- // The message should come from a resource file, and should still
- // have the SQL error code 22027.
+ // SQL:2003 6.29.9 and SQL:2016 6.30.11: trim string must have length=1.
+ // Failure occurs at runtime.
f.checkFails("trim('xy' from 'abcde')",
- "Trim error: trim character must be exactly 1 character",
+ "Invalid argument 'xy': the length of the string describing "
+ + "the trimmed character must be 1",
true);
f.checkFails("trim('' from 'abcde')",
- "Trim error: trim character must be exactly 1 character",
+ "Invalid argument '': the length of the string describing "
+ + "the trimmed character must be 1",
true);
- final SqlOperatorFixture f1 =
f.withConformance(SqlConformanceEnum.MYSQL_5);
- f1.checkString("trim(leading 'eh' from 'hehe__hehe')", "__hehe",
- "VARCHAR(10) NOT NULL");
- f1.checkString("trim(trailing 'eh' from 'hehe__hehe')", "hehe__",
- "VARCHAR(10) NOT NULL");
- f1.checkString("trim('eh' from 'hehe__hehe')", "__", "VARCHAR(10) NOT
NULL");
+ final Consumer<SqlOperatorFixture> consumer = f1 -> {
+ f1.checkString("trim(leading 'eh' from 'hehe__hehe')", "__hehe",
+ "VARCHAR(10) NOT NULL");
+ f1.checkString("trim(trailing 'eh' from 'hehe__hehe')", "hehe__",
+ "VARCHAR(10) NOT NULL");
+ f1.checkString("trim('eh' from 'hehe__hehe')", "__", "VARCHAR(10) NOT
NULL");
+ };
+ final List<SqlConformanceEnum> conformanceEnums =
+ list(SqlConformanceEnum.MYSQL_5, SqlConformanceEnum.SQL_SERVER_2008);
+ f.forEachConformance(conformanceEnums, consumer);
}
@Test void testRtrimFunc() {
