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 06355b6a61 [CALCITE-6999] Invalid unparse for TRIM in PrestoDialect
06355b6a61 is described below
commit 06355b6a6111529d13b9f1228a4cbe9f90fce5e6
Author: Yu Xu <[email protected]>
AuthorDate: Sun May 4 17:45:20 2025 +0800
[CALCITE-6999] Invalid unparse for TRIM in PrestoDialect
---
.../calcite/sql/dialect/BigQuerySqlDialect.java | 41 ++-------------------
.../calcite/sql/dialect/PrestoSqlDialect.java | 3 ++
.../apache/calcite/util/RelToSqlConverterUtil.java | 38 ++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 42 +++++++++++++++++-----
4 files changed, 77 insertions(+), 47 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
index 6f3c1dec8c..6e5ebeb4c0 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
@@ -32,18 +32,17 @@
import org.apache.calcite.sql.SqlIntervalLiteral;
import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlKind;
-import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlSetOperator;
import org.apache.calcite.sql.SqlSyntax;
import org.apache.calcite.sql.SqlWriter;
-import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.util.RelToSqlConverterUtil;
import org.apache.calcite.util.format.FormatModel;
import org.apache.calcite.util.format.FormatModels;
@@ -57,7 +56,6 @@
import java.util.regex.Pattern;
import static java.lang.Long.parseLong;
-import static java.util.Objects.requireNonNull;
/**
* A <code>SqlDialect</code> implementation for Google BigQuery's "Standard
SQL"
@@ -215,7 +213,7 @@ public BigQuerySqlDialect(SqlDialect.Context context) {
rightPrec);
break;
case TRIM:
- unparseTrim(writer, call, leftPrec, rightPrec);
+ RelToSqlConverterUtil.unparseTrimLR(writer, call, leftPrec, rightPrec);
break;
case ITEM:
if (call.getOperator().getName().equals("ITEM")) {
@@ -263,41 +261,6 @@ public BigQuerySqlDialect(SqlDialect.Context context) {
}
}
- /**
- * For usage of TRIM, LTRIM and RTRIM in BQ see
- * <a
href="https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#trim";>
- * BQ Trim Function</a>.
- */
- private static void unparseTrim(SqlWriter writer, SqlCall call, int leftPrec,
- int rightPrec) {
- final String operatorName;
- SqlLiteral trimFlag = call.operand(0);
- SqlLiteral valueToTrim = call.operand(1);
- switch (trimFlag.getValueAs(SqlTrimFunction.Flag.class)) {
- case LEADING:
- operatorName = "LTRIM";
- break;
- case TRAILING:
- operatorName = "RTRIM";
- break;
- default:
- operatorName = call.getOperator().getName();
- break;
- }
- final SqlWriter.Frame trimFrame = writer.startFunCall(operatorName);
- call.operand(2).unparse(writer, leftPrec, rightPrec);
-
- // If the trimmed character is a non-space character, add it to the target
SQL.
- // eg: TRIM(BOTH 'A' from 'ABCD'
- // Output Query: TRIM('ABC', 'A')
- String value = requireNonNull(valueToTrim.toValue(),
"valueToTrim.toValue()");
- if (!value.matches("\\s+")) {
- writer.literal(",");
- call.operand(1).unparse(writer, leftPrec, rightPrec);
- }
- writer.endFunCall(trimFrame);
- }
-
/** When indexing an array in BigQuery, an array subscript operator must
* surround the desired index. For the standard ITEM operator used by other
* dialects in Calcite, ITEM is not included in the unparsing. This helper
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
index 8da8850de4..a584b81199 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
@@ -210,6 +210,9 @@ private static void unparseUsingLimit(SqlWriter writer,
@Nullable SqlNode offset
.createCall(SqlParserPos.ZERO, call.getOperandList());
super.unparseCall(writer, lengthCall, leftPrec, rightPrec);
break;
+ case TRIM:
+ RelToSqlConverterUtil.unparseTrimLR(writer, call, leftPrec, rightPrec);
+ break;
default:
// Current impl is same with Postgresql.
PostgresqlSqlDialect.DEFAULT.unparseCall(writer, call, leftPrec,
rightPrec);
diff --git
a/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
b/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
index aaa1603bbe..15fa6fe24c 100644
--- a/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
+++ b/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
@@ -68,6 +68,44 @@ public static void unparseHiveTrim(
}
}
+ /**
+ * For usage of TRIM(LEADING 'A' FROM 'ABCA') convert to TRIM, LTRIM and
RTRIM,
+ * can refer to BigQuery and Presto documents:
+ * <a
href="https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#trim";>
+ * BigQuery Trim Function</a>,
+ * <a
href="https://prestodb.io/docs/current/functions/string.html#trim-string-varchar";>
+ * Presto Trim Function</a>.
+ */
+ public static void unparseTrimLR(SqlWriter writer, SqlCall call, int
leftPrec,
+ int rightPrec) {
+ final String operatorName;
+ SqlLiteral trimFlag = call.operand(0);
+ SqlLiteral valueToTrim = call.operand(1);
+ switch (trimFlag.getValueAs(SqlTrimFunction.Flag.class)) {
+ case LEADING:
+ operatorName = "LTRIM";
+ break;
+ case TRAILING:
+ operatorName = "RTRIM";
+ break;
+ default:
+ operatorName = call.getOperator().getName();
+ break;
+ }
+ final SqlWriter.Frame trimFrame = writer.startFunCall(operatorName);
+ call.operand(2).unparse(writer, leftPrec, rightPrec);
+
+ // If the trimmed character is a non-space character, add it to the target
SQL.
+ // eg: TRIM(BOTH 'A' from 'ABCD')
+ // Output Query: TRIM('ABC', 'A')
+ String value = requireNonNull(valueToTrim.toValue(),
"valueToTrim.toValue()");
+ if (!value.equals(" ")) {
+ writer.literal(",");
+ call.operand(1).unparse(writer, leftPrec, rightPrec);
+ }
+ writer.endFunCall(trimFrame);
+ }
+
/**
* Unparses IS TRUE,IS FALSE,IS NOT TRUE and IS NOT FALSE.
*
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 66766eb0b9..5650b326bd 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -2782,6 +2782,8 @@ private SqlDialect nonOrdinalDialect() {
* LTRIM or RTRIM</a>,
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3663";>[CALCITE-3663]
* Support for TRIM function in BigQuery dialect</a>, and
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6999";>[CALCITE-6999]
+ * Invalid unparse for TRIM in PrestoDialect</a>, and
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3771";>[CALCITE-3771]
* Support of TRIM function for SPARK dialect and improvement in HIVE
* Dialect</a>. */
@@ -2792,10 +2794,13 @@ private SqlDialect nonOrdinalDialect() {
+ "FROM `foodmart`.`reserve_employee`";
final String expectedBigQuery = "SELECT TRIM(' str ')\n"
+ "FROM foodmart.reserve_employee";
+ final String exptectedPresto = "SELECT TRIM(' str ')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query)
.withBigQuery().ok(expectedBigQuery)
.withHive().ok(expected)
- .withSpark().ok(expected);
+ .withSpark().ok(expected)
+ .withPresto().ok(exptectedPresto);
}
@Test void testHiveSparkAndBqTrimWithBoth() {
@@ -2890,8 +2895,11 @@ private SqlDialect nonOrdinalDialect() {
+ "from \"foodmart\".\"reserve_employee\"";
final String expected = "SELECT RTRIM('abcd', 'a')\n"
+ "FROM foodmart.reserve_employee";
+ final String expectedPresto = "SELECT RTRIM('abcd', 'a')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query)
- .withBigQuery().ok(expected);
+ .withBigQuery().ok(expected)
+ .withPresto().ok(expectedPresto);
}
@Test void testHiveAndSparkTrimWithTailingChar() {
@@ -9526,8 +9534,11 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"foodmart\".\"reserve_employee\"";
final String expectedStarRocks = "SELECT TRIM(' str ')\n"
+ "FROM `foodmart`.`reserve_employee`";
+ final String expectedPresto = "SELECT TRIM(' str ')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query).withStarRocks().ok(expectedStarRocks)
- .withDoris().ok(expectedStarRocks);
+ .withDoris().ok(expectedStarRocks)
+ .withPresto().ok(expectedPresto);
}
@Test void testTrimWithBoth() {
@@ -9535,8 +9546,11 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"foodmart\".\"reserve_employee\"";
final String expectedStarRocks = "SELECT TRIM(' str ')\n"
+ "FROM `foodmart`.`reserve_employee`";
+ final String expectedPresto = "SELECT TRIM(' str ')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query).withStarRocks().ok(expectedStarRocks)
- .withDoris().ok(expectedStarRocks);
+ .withDoris().ok(expectedStarRocks)
+ .withPresto().ok(expectedPresto);
}
@Test void testTrimWithLeading() {
@@ -9544,8 +9558,11 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"foodmart\".\"reserve_employee\"";
final String expectedStarRocks = "SELECT LTRIM(' str ')\n"
+ "FROM `foodmart`.`reserve_employee`";
+ final String expectedPresto = "SELECT LTRIM(' str ')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query).withStarRocks().ok(expectedStarRocks)
- .withDoris().ok(expectedStarRocks);
+ .withDoris().ok(expectedStarRocks)
+ .withPresto().ok(expectedPresto);
}
@Test void testTrimWithTailing() {
@@ -9553,8 +9570,11 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"foodmart\".\"reserve_employee\"";
final String expectedStarRocks = "SELECT RTRIM(' str ')\n"
+ "FROM `foodmart`.`reserve_employee`";
+ final String expectedPresto = "SELECT RTRIM(' str ')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query).withStarRocks().ok(expectedStarRocks)
- .withDoris().ok(expectedStarRocks);
+ .withDoris().ok(expectedStarRocks)
+ .withPresto().ok(expectedPresto);
}
@Test void testTrimWithBothChar() {
@@ -9562,8 +9582,11 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"foodmart\".\"reserve_employee\"";
final String expectedStarRocks = "SELECT REGEXP_REPLACE('abcda',
'^(a)*|(a)*$', '')\n"
+ "FROM `foodmart`.`reserve_employee`";
+ final String expectedPresto = "SELECT TRIM('abcda', 'a')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query).withStarRocks().ok(expectedStarRocks)
- .withDoris().ok(expectedStarRocks);
+ .withDoris().ok(expectedStarRocks)
+ .withPresto().ok(expectedPresto);
}
@Test void testTrimWithTailingChar() {
@@ -9580,8 +9603,11 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"foodmart\".\"reserve_employee\"";
final String expectedStarRocks = "SELECT REGEXP_REPLACE('abcd', '^(a)*',
'')\n"
+ "FROM `foodmart`.`reserve_employee`";
+ final String expectedPresto = "SELECT LTRIM('abcd', 'a')\n"
+ + "FROM \"foodmart\".\"reserve_employee\"";
sql(query).withStarRocks().ok(expectedStarRocks)
- .withDoris().ok(expectedStarRocks);
+ .withDoris().ok(expectedStarRocks)
+ .withPresto().ok(expectedPresto);
}
@Test void testSelectQueryWithRollup() {
