This is an automated email from the ASF dual-hosted git repository.
guohongyu 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 6ba31302be [CALCITE-6257] StarRocks dialect implementation
6ba31302be is described below
commit 6ba31302be35f19e0ccf289a1c6705a79bf87587
Author: YiwenWu <yiwenwu1...@gmail.com>
AuthorDate: Thu Feb 22 19:29:37 2024 +0800
[CALCITE-6257] StarRocks dialect implementation
---
.../java/org/apache/calcite/sql/SqlDialect.java | 1 +
.../apache/calcite/sql/SqlDialectFactoryImpl.java | 3 +
.../calcite/sql/dialect/StarRocksSqlDialect.java | 133 ++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 271 ++++++++++++++++++---
4 files changed, 380 insertions(+), 28 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 03602a5486..30c912dcb8 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -1393,6 +1393,7 @@ public class SqlDialect {
VERTICA("Vertica", "\"", NullCollation.HIGH),
SQLSTREAM("SQLstream", "\"", NullCollation.HIGH),
SPARK("Spark", null, NullCollation.LOW),
+ STARROCKS("StarRocks", "`", NullCollation.LOW),
/** Paraccel, now called Actian Matrix. Redshift is based on this, so
* presumably the dialect capabilities are similar. */
diff --git
a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
index e44b6f0435..b22f8fb466 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
@@ -47,6 +47,7 @@ import org.apache.calcite.sql.dialect.PrestoSqlDialect;
import org.apache.calcite.sql.dialect.RedshiftSqlDialect;
import org.apache.calcite.sql.dialect.SnowflakeSqlDialect;
import org.apache.calcite.sql.dialect.SparkSqlDialect;
+import org.apache.calcite.sql.dialect.StarRocksSqlDialect;
import org.apache.calcite.sql.dialect.SybaseSqlDialect;
import org.apache.calcite.sql.dialect.TeradataSqlDialect;
import org.apache.calcite.sql.dialect.VerticaSqlDialect;
@@ -230,6 +231,8 @@ public class SqlDialectFactoryImpl implements
SqlDialectFactory {
return SnowflakeSqlDialect.DEFAULT;
case SPARK:
return SparkSqlDialect.DEFAULT;
+ case STARROCKS:
+ return StarRocksSqlDialect.DEFAULT;
case SYBASE:
return SybaseSqlDialect.DEFAULT;
case TERADATA:
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/StarRocksSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/StarRocksSqlDialect.java
new file mode 100644
index 0000000000..8e9950da7a
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/StarRocksSqlDialect.java
@@ -0,0 +1,133 @@
+/*
+ * 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.dialect;
+
+import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.config.NullCollation;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlAbstractDateTimeLiteral;
+import org.apache.calcite.sql.SqlAlienSystemTypeNameSpec;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlDataTypeSpec;
+import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlFloorFunction;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import static org.apache.calcite.util.RelToSqlConverterUtil.unparseHiveTrim;
+
+/**
+ * A <code>SqlDialect</code> implementation for the StarRocks database.
+ */
+public class StarRocksSqlDialect extends MysqlSqlDialect {
+
+ public static final SqlDialect.Context DEFAULT_CONTEXT =
SqlDialect.EMPTY_CONTEXT
+ .withDatabaseProduct(SqlDialect.DatabaseProduct.STARROCKS)
+ .withIdentifierQuoteString("`")
+ .withNullCollation(NullCollation.LOW);
+
+ public static final SqlDialect DEFAULT = new
StarRocksSqlDialect(DEFAULT_CONTEXT);
+
+ /**
+ * Creates a StarRocksSqlDialect.
+ */
+ public StarRocksSqlDialect(Context context) {
+ super(context);
+ }
+
+ @Override public boolean supportsGroupByWithRollup() {
+ return false;
+ }
+
+ @Override public boolean supportsTimestampPrecision() {
+ return false;
+ }
+
+ @Override public boolean supportsApproxCountDistinct() {
+ return true;
+ }
+
+ @Override public void unparseCall(SqlWriter writer, SqlCall call, int
leftPrec, int rightPrec) {
+ switch (call.getKind()) {
+ case ARRAY_VALUE_CONSTRUCTOR:
+ final SqlWriter.Frame arrayFrame = writer.startList("[", "]");
+ for (SqlNode operand : call.getOperandList()) {
+ writer.sep(",");
+ operand.unparse(writer, leftPrec, rightPrec);
+ }
+ writer.endList(arrayFrame);
+ break;
+ case MAP_VALUE_CONSTRUCTOR:
+ writer.keyword(call.getOperator().getName());
+ final SqlWriter.Frame mapFrame = writer.startList("{", "}");
+ for (int i = 0; i < call.operandCount(); i++) {
+ String sep = i % 2 == 0 ? "," : ":";
+ writer.sep(sep);
+ call.operand(i).unparse(writer, leftPrec, rightPrec);
+ }
+ writer.endList(mapFrame);
+ break;
+ case TRIM:
+ unparseHiveTrim(writer, call, leftPrec, rightPrec);
+ break;
+ case FLOOR:
+ if (call.operandCount() != 2) {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
+ }
+ final SqlLiteral timeUnitNode = call.operand(1);
+ final TimeUnitRange timeUnit =
timeUnitNode.getValueAs(TimeUnitRange.class);
+ SqlCall newCall =
+ SqlFloorFunction.replaceTimeUnitOperand(call, timeUnit.name(),
+ timeUnitNode.getParserPosition());
+ SqlFloorFunction.unparseDatetimeFunction(writer, newCall, "DATE_TRUNC",
false);
+ break;
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ break;
+ }
+ }
+
+ @Override public @Nullable SqlNode getCastSpec(RelDataType type) {
+ switch (type.getSqlTypeName()) {
+ case TIMESTAMP:
+ return new SqlDataTypeSpec(
+ new SqlAlienSystemTypeNameSpec(
+ "DATETIME",
+ type.getSqlTypeName(),
+ SqlParserPos.ZERO),
+ SqlParserPos.ZERO);
+ default:
+ return super.getCastSpec(type);
+ }
+ }
+
+ @Override public void unparseDateTimeLiteral(SqlWriter writer,
+ SqlAbstractDateTimeLiteral literal, int leftPrec, int rightPrec) {
+ if (literal.getTypeName() == SqlTypeName.TIMESTAMP) {
+ writer.literal("DATETIME '" + literal.toFormattedString() + "'");
+ } else {
+ super.unparseDateTimeLiteral(writer, literal, leftPrec, rightPrec);
+ }
+ }
+
+}
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 394aac0780..1de16cb69e 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
@@ -189,6 +189,7 @@ class RelToSqlConverterTest {
.put(DatabaseProduct.ORACLE.getDialect(), DatabaseProduct.ORACLE)
.put(DatabaseProduct.POSTGRESQL.getDialect(),
DatabaseProduct.POSTGRESQL)
.put(DatabaseProduct.PRESTO.getDialect(), DatabaseProduct.PRESTO)
+ .put(DatabaseProduct.STARROCKS.getDialect(), DatabaseProduct.STARROCKS)
.build();
}
@@ -542,14 +543,18 @@ class RelToSqlConverterTest {
+ "FROM `foodmart`.`product`";
final String expectedPresto = "SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"product\"";
+ final String expectedStarRocks = "SELECT COUNT(*)\n"
+ + "FROM `foodmart`.`product`";
sql(sql0)
.ok(expected)
.withMysql().ok(expectedMysql)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
sql(sql1)
.ok(expected)
.withMysql().ok(expectedMysql)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testSelectQueryWithGroupByEmpty2() {
@@ -854,10 +859,16 @@ class RelToSqlConverterTest {
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY ROLLUP(`product_class_id`, `brand_name`)\n"
+ "ORDER BY `product_class_id` NULLS LAST, `brand_name` NULLS LAST";
+ final String expectedStarRocks = "SELECT `product_class_id`,
`brand_name`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`, `brand_name`)\n"
+ + "ORDER BY `product_class_id` IS NULL, `product_class_id`,
`brand_name` IS NULL, "
+ + "`brand_name`";
sql(query)
.ok(expected)
.withMysql().ok(expectedMysql)
- .withMysql8().ok(expectedMysql8);
+ .withMysql8().ok(expectedMysql8)
+ .withStarRocks().ok(expectedStarRocks);
}
/** As {@link #testSelectQueryWithGroupByRollup()},
@@ -876,9 +887,15 @@ class RelToSqlConverterTest {
+ "FROM `foodmart`.`product`\n"
+ "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP) AS `t0`\n"
+ "ORDER BY `brand_name`, `product_class_id`";
+ final String expectedStarRocks = "SELECT `product_class_id`,
`brand_name`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`, `brand_name`)\n"
+ + "ORDER BY `brand_name` IS NULL, `brand_name`, `product_class_id` IS
NULL, "
+ + "`product_class_id`";
sql(query)
.ok(expected)
- .withMysql().ok(expectedMysql);
+ .withMysql().ok(expectedMysql)
+ .withStarRocks().ok(expectedStarRocks);
}
/** Test case for
@@ -974,10 +991,15 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\")\n"
+ "ORDER BY \"product_class_id\", 2";
+ final String expectedStarRocks = "SELECT `product_class_id`, COUNT(*) AS
`C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`)\n"
+ + "ORDER BY `product_class_id` IS NULL, `product_class_id`, COUNT(*)
IS NULL, 2";
sql(query)
.ok(expected)
.withMysql().ok(expectedMysql)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
/** As {@link #testSelectQueryWithSingletonCube()}, but no ORDER BY
@@ -995,10 +1017,14 @@ class RelToSqlConverterTest {
final String expectedPresto = "SELECT \"product_class_id\", COUNT(*) AS
\"C\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\")";
+ final String expectedStarRocks = "SELECT `product_class_id`, COUNT(*) AS
`C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`)";
sql(query)
.ok(expected)
.withMysql().ok(expectedMysql)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
/** Cannot rewrite if ORDER BY contains a column not in GROUP BY (in this
@@ -1021,9 +1047,15 @@ class RelToSqlConverterTest {
+ "ORDER BY `product_class_id` IS NULL, `product_class_id`,"
+ " `brand_name` IS NULL, `brand_name`,"
+ " COUNT(*) IS NULL, 3";
+ final String expectedStarRocks = "SELECT `product_class_id`, `brand_name`,
COUNT(*) AS `C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`, `brand_name`)\n"
+ + "ORDER BY `product_class_id` IS NULL, `product_class_id`,
`brand_name` IS NULL, "
+ + "`brand_name`, COUNT(*) IS NULL, 3";
sql(query)
.ok(expected)
- .withMysql().ok(expectedMysql);
+ .withMysql().ok(expectedMysql)
+ .withStarRocks().ok(expectedStarRocks);
}
/** As {@link #testSelectQueryWithSingletonCube()}, but with LIMIT. */
@@ -1046,10 +1078,15 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\")\n"
+ "LIMIT 5";
+ final String expectedStarRocks = "SELECT `product_class_id`, COUNT(*) AS
`C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`)\n"
+ + "LIMIT 5";
sql(query)
.ok(expected)
.withMysql().ok(expectedMysql)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
/**
@@ -1705,6 +1742,10 @@ class RelToSqlConverterTest {
+ "GROUP BY product_id) t1";
final String expectedSpark = expectedHive;
final String expectedExasol = expectedBigQuery;
+ final String expectedStarRocks = "SELECT SUM(`net_weight1`) AS
`net_weight_converted`\n"
+ + "FROM (SELECT SUM(`net_weight`) AS `net_weight1`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY `product_id`) AS `t1`";
sql(query)
.withBigQuery().ok(expectedBigQuery)
.withExasol().ok(expectedExasol)
@@ -1713,7 +1754,8 @@ class RelToSqlConverterTest {
.withOracle().ok(expectedOracle)
.withPostgresql().ok(expectedPostgresql)
.withSpark().ok(expectedSpark)
- .withVertica().ok(expectedVertica);
+ .withVertica().ok(expectedVertica)
+ .withStarRocks().ok(expectedStarRocks);
}
/** Test case for
@@ -2544,9 +2586,13 @@ class RelToSqlConverterTest {
final String expectedPresto = "SELECT *\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "WHERE (\"hire_date\" - INTERVAL '19800' SECOND) >
CAST(\"hire_date\" AS TIMESTAMP)";
+ final String expectedStarRocks = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` - INTERVAL '19800' SECOND) > CAST(`hire_date` AS
DATETIME)";
sql(query)
.withSpark().ok(expectedSpark)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testExasolCastToTimestamp() {
@@ -2652,7 +2698,12 @@ class RelToSqlConverterTest {
+ "FROM foodmart.product\n"
+ "LIMIT 100\n"
+ "OFFSET 10";
- sql(query).withHive().ok(expected);
+ final String expectedStarRocks = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LIMIT 100\n"
+ + "OFFSET 10";
+ sql(query).withHive().ok(expected)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testPositionFunctionForHive() {
@@ -3405,17 +3456,19 @@ class RelToSqlConverterTest {
final String expectedClickHouse = "SELECT `product_id`\n"
+ "FROM `foodmart`.`product`\n"
+ "LIMIT 10, 100";
- sql(query)
- .ok(expected)
- .withClickHouse().ok(expectedClickHouse);
-
final String expectedPresto = "SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "OFFSET 10\n"
+ "LIMIT 100";
+ final String expectedStarRocks = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LIMIT 100\n"
+ + "OFFSET 10";
sql(query)
.ok(expected)
- .withPresto().ok(expectedPresto);
+ .withClickHouse().ok(expectedClickHouse)
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testSelectQueryWithLimitOffsetClause() {
@@ -3432,8 +3485,14 @@ class RelToSqlConverterTest {
+ "ORDER BY net_weight NULLS LAST\n"
+ "LIMIT 100\n"
+ "OFFSET 10";
+ final String expectedStarRocks = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "ORDER BY `net_weight` IS NULL, `net_weight`\n"
+ + "LIMIT 100\n"
+ + "OFFSET 10";
sql(query).ok(expected)
- .withBigQuery().ok(expectedBigQuery);
+ .withBigQuery().ok(expectedBigQuery)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testSelectQueryWithParameters() {
@@ -4506,13 +4565,16 @@ class RelToSqlConverterTest {
String expectedPresto = "SELECT DATE_TRUNC('MINUTE', \"hire_date\")\n"
+ "FROM \"foodmart\".\"employee\"";
String expectedFirebolt = expectedPostgresql;
+ String expectedStarRocks = "SELECT DATE_TRUNC('MINUTE', `hire_date`)\n"
+ + "FROM `foodmart`.`employee`";
sql(query)
.withClickHouse().ok(expectedClickHouse)
.withFirebolt().ok(expectedFirebolt)
.withHsqldb().ok(expectedHsqldb)
.withOracle().ok(expectedOracle)
.withPostgresql().ok(expectedPostgresql)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testFetchMssql() {
@@ -4523,21 +4585,26 @@ class RelToSqlConverterTest {
}
@Test void testFetchOffset() {
- String query = "SELECT * FROM \"employee\" LIMIT 1 OFFSET 1";
- String expectedMssql = "SELECT *\n"
+ final String query = "SELECT * FROM \"employee\" LIMIT 1 OFFSET 1";
+ final String expectedMssql = "SELECT *\n"
+ "FROM [foodmart].[employee]\n"
+ "OFFSET 1 ROWS\n"
+ "FETCH NEXT 1 ROWS ONLY";
- String expectedSybase = "SELECT TOP (1) START AT 1 *\n"
+ final String expectedSybase = "SELECT TOP (1) START AT 1 *\n"
+ "FROM foodmart.employee";
final String expectedPresto = "SELECT *\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "OFFSET 1\n"
+ "LIMIT 1";
+ final String expectedStarRocks = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "LIMIT 1\n"
+ + "OFFSET 1";
sql(query)
.withMssql().ok(expectedMssql)
.withSybase().ok(expectedSybase)
- .withPresto().ok(expectedPresto);
+ .withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testFloorMssqlMonth() {
@@ -4803,6 +4870,8 @@ class RelToSqlConverterTest {
final String expectedFirebolt = expectedPresto;
final String expectedMysql = "SELECT SUBSTRING(`brand_name`, 2)\n"
+ "FROM `foodmart`.`product`";
+ final String expectedStarRocks = "SELECT SUBSTRING(`brand_name`, 2)\n"
+ + "FROM `foodmart`.`product`";
sql(query)
.withBigQuery().ok(expectedBigQuery)
.withClickHouse().ok(expectedClickHouse)
@@ -4815,7 +4884,8 @@ class RelToSqlConverterTest {
.withPostgresql().ok(expectedPostgresql)
.withPresto().ok(expectedPresto)
.withRedshift().ok(expectedRedshift)
- .withSnowflake().ok(expectedSnowflake);
+ .withSnowflake().ok(expectedSnowflake)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testSubstringWithFor() {
@@ -4838,6 +4908,8 @@ class RelToSqlConverterTest {
+ "FROM `foodmart`.`product`";
final String expectedMssql = "SELECT SUBSTRING([brand_name], 2, 3)\n"
+ "FROM [foodmart].[product]";
+ final String expectedStarRocks = "SELECT SUBSTRING(`brand_name`, 2, 3)\n"
+ + "FROM `foodmart`.`product`";
sql(query)
.withBigQuery().ok(expectedBigQuery)
.withClickHouse().ok(expectedClickHouse)
@@ -4848,7 +4920,8 @@ class RelToSqlConverterTest {
.withPostgresql().ok(expectedPostgresql)
.withPresto().ok(expectedPresto)
.withRedshift().ok(expectedRedshift)
- .withSnowflake().ok(expectedSnowflake);
+ .withSnowflake().ok(expectedSnowflake)
+ .withStarRocks().ok(expectedStarRocks);
}
/** Test case for
@@ -6638,9 +6711,14 @@ class RelToSqlConverterTest {
+ "FROM foodmart.store\n"
+ "CROSS JOIN foodmart.employee\n"
+ "CROSS JOIN foodmart.department";
+ final String expectedStarRocks = "SELECT *\n"
+ + "FROM `foodmart`.`store`,\n"
+ + "`foodmart`.`employee`,\n"
+ + "`foodmart`.`department`";
sql(sql)
.withMysql().ok(expectedMysql)
- .withSpark().ok(expectedSpark);
+ .withSpark().ok(expectedSpark)
+ .withStarRocks().ok(expectedStarRocks);
}
/** As {@link #testCommaCrossJoin3way()}, but shows that if there is a
@@ -6855,10 +6933,14 @@ class RelToSqlConverterTest {
final String expectedSpark = "SELECT COUNT(*)\n"
+ "FROM foodmart.product\n"
+ "GROUP BY ROLLUP(product_id, product_class_id)";
+ final String expectedStarRocks = "SELECT COUNT(*)\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_id`, `product_class_id`)";
sql(query)
.ok(expected)
.withPresto().ok(expected)
- .withSpark().ok(expectedSpark);
+ .withSpark().ok(expectedSpark)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testJsonType() {
@@ -7266,13 +7348,16 @@ class RelToSqlConverterTest {
+ "FROM \"foodmart\".\"product\"";
final String expectedPrestoSql = "SELECT APPROX_DISTINCT(\"product_id\")\n"
+ "FROM \"foodmart\".\"product\"";
+ final String expectedStarRocksSql = "SELECT
APPROX_COUNT_DISTINCT(`product_id`)\n"
+ + "FROM `foodmart`.`product`";
sql(query).ok(expectedExact)
.withHive().ok(expectedApprox)
.withSpark().ok(expectedApprox)
.withBigQuery().ok(expectedApprox)
.withOracle().ok(expectedApproxQuota)
.withSnowflake().ok(expectedApproxQuota)
- .withPresto().ok(expectedPrestoSql);
+ .withPresto().ok(expectedPrestoSql)
+ .withStarRocks().ok(expectedStarRocksSql);
}
@Test void testRowValueExpression() {
@@ -7883,7 +7968,9 @@ class RelToSqlConverterTest {
final String query = "SELECT MAP['k1', 'v1', 'k2', 'v2']";
final String expectedPresto = "SELECT MAP (ARRAY['k1', 'k2'], ARRAY['v1',
'v2'])\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
- sql(query).withPresto().ok(expectedPresto);
+ final String expectedStarRocks = "SELECT MAP { 'k1' : 'v1', 'k2' : 'v2' }";
+ sql(query).withPresto().ok(expectedPresto)
+ .withStarRocks().ok(expectedStarRocks);
}
@Test void testMapValueConstructorWithArray() {
@@ -7893,6 +7980,130 @@ class RelToSqlConverterTest {
sql(query).withPresto().ok(expectedPresto);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6257";>[CALCITE-6257]
+ * StarRocks dialect implementation </a>.
+ */
+ @Test void testCastToTimestamp() {
+ final String query = "select * from \"employee\" where \"hire_date\" - "
+ + "INTERVAL '19800' SECOND(5) > cast(\"hire_date\" as TIMESTAMP) ";
+ final String expectedStarRocks = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` - INTERVAL '19800' SECOND) > CAST(`hire_date` AS
DATETIME)";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testArrayValueConstructor() {
+ final String query = "SELECT ARRAY[1, 2, 3]";
+ final String expectedStarRocks = "SELECT[1, 2, 3]";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithBothSpecialCharacter() {
+ final String query = "SELECT TRIM(BOTH '$@*A' from
'$@*AABC$@*AADCAA$@*A')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT
REGEXP_REPLACE('$@*AABC$@*AADCAA$@*A',"
+ + " '^(\\$\\@\\*A)*|(\\$\\@\\*A)*$', '')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testUnparseSqlIntervalQualifier() {
+ final String sql0 = "select * from \"employee\" where \"hire_date\" - "
+ + "INTERVAL '19800' SECOND(5) > TIMESTAMP '2005-10-17 00:00:00' ";
+ final String expect0 = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` - INTERVAL '19800' SECOND) > DATETIME
'2005-10-17 00:00:00'";
+ sql(sql0).withStarRocks().ok(expect0);
+
+ final String sql1 = "select * from \"employee\" where \"hire_date\" + "
+ + "INTERVAL '10' HOUR > TIMESTAMP '2005-10-17 00:00:00' ";
+ final String expect1 = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` + INTERVAL '10' HOUR) > DATETIME '2005-10-17
00:00:00'";
+ sql(sql1).withStarRocks().ok(expect1);
+
+ final String sql2 = "select * from \"employee\" where \"hire_date\" + "
+ + "INTERVAL '1' YEAR > TIMESTAMP '2005-10-17 00:00:00' ";
+ final String expect2 = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` + INTERVAL '1' YEAR) > DATETIME '2005-10-17
00:00:00'";
+ sql(sql2).withStarRocks().ok(expect2);
+
+ final String sql3 = "select * from \"employee\" "
+ + "where \"hire_date\" + INTERVAL '39' MINUTE"
+ + " > TIMESTAMP '2005-10-17 00:00:00' ";
+ final String expect3 = "SELECT *\n"
+ + "FROM `foodmart`.`employee`\n"
+ + "WHERE (`hire_date` + INTERVAL '39' MINUTE) > DATETIME '2005-10-17
00:00:00'";
+ sql(sql3).withStarRocks().ok(expect3);
+ }
+
+ @Test void testTrim() {
+ final String query = "SELECT TRIM(' str ')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT TRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithBoth() {
+ final String query = "SELECT TRIM(both ' ' from ' str ')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT TRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithLeading() {
+ final String query = "SELECT TRIM(LEADING ' ' from ' str ')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT LTRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithTailing() {
+ final String query = "SELECT TRIM(TRAILING ' ' from ' str ')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT RTRIM(' str ')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithBothChar() {
+ final String query = "SELECT TRIM(both 'a' from 'abcda')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT REGEXP_REPLACE('abcda',
'^(a)*|(a)*$', '')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithTailingChar() {
+ final String query = "SELECT TRIM(TRAILING 'a' from 'abcd')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT REGEXP_REPLACE('abcd', '(a)*$',
'')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testTrimWithLeadingChar() {
+ final String query = "SELECT TRIM(LEADING 'a' from 'abcd')\n"
+ + "from \"foodmart\".\"reserve_employee\"";
+ final String expectedStarRocks = "SELECT REGEXP_REPLACE('abcd', '^(a)*',
'')\n"
+ + "FROM `foodmart`.`reserve_employee`";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
+ @Test void testSelectQueryWithRollup() {
+ final String query = "select \"product_class_id\", \"product_id\",
count(*) "
+ + "from \"product\" group by rollup(\"product_class_id\",
\"product_id\")";
+ final String expectedStarRocks = "SELECT `product_class_id`, `product_id`,
COUNT(*)\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ROLLUP(`product_class_id`, `product_id`)";
+ sql(query).withStarRocks().ok(expectedStarRocks);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
@@ -8041,6 +8252,10 @@ class RelToSqlConverterTest {
return dialect(DatabaseProduct.SPARK.getDialect());
}
+ Sql withStarRocks() {
+ return dialect(DatabaseProduct.STARROCKS.getDialect());
+ }
+
Sql withPostgresqlModifiedTypeSystem() {
// Postgresql dialect with max length for varchar set to 256
final PostgresqlSqlDialect postgresqlSqlDialect =
