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 e536d39496 [CALCITE-7081] Invalid unparse for cast to nested type in
ClickHouse
e536d39496 is described below
commit e536d3949674cbbc0fd1162b3fccb211ca75789b
Author: xuzifu666 <[email protected]>
AuthorDate: Mon Jun 30 11:43:29 2025 +0800
[CALCITE-7081] Invalid unparse for cast to nested type in ClickHouse
---
.../calcite/sql/dialect/ClickHouseSqlDialect.java | 68 +++++++-------
.../apache/calcite/util/RelToSqlConverterUtil.java | 100 +++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 51 +++++++++++
3 files changed, 188 insertions(+), 31 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
index 630ef0cb74..c4bd7802bf 100644
---
a/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/ClickHouseSqlDialect.java
@@ -35,7 +35,6 @@
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
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.util.RelToSqlConverterUtil;
@@ -118,36 +117,43 @@ public ClickHouseSqlDialect(Context context) {
}
@Override public @Nullable SqlNode getCastSpec(RelDataType type) {
- if (type instanceof BasicSqlType) {
- SqlTypeName typeName = type.getSqlTypeName();
- switch (typeName) {
- case CHAR:
- return createSqlDataTypeSpecByName(
- String.format(Locale.ROOT, "FixedString(%s)",
- type.getPrecision()), typeName, type.isNullable());
- case VARCHAR:
- return createSqlDataTypeSpecByName("String", typeName,
type.isNullable());
- case TINYINT:
- return createSqlDataTypeSpecByName("Int8", typeName,
type.isNullable());
- case SMALLINT:
- return createSqlDataTypeSpecByName("Int16", typeName,
type.isNullable());
- case INTEGER:
- return createSqlDataTypeSpecByName("Int32", typeName,
type.isNullable());
- case BIGINT:
- return createSqlDataTypeSpecByName("Int64", typeName,
type.isNullable());
- case REAL:
- return createSqlDataTypeSpecByName("Float32", typeName,
type.isNullable());
- case FLOAT:
- case DOUBLE:
- return createSqlDataTypeSpecByName("Float64", typeName,
type.isNullable());
- case DATE:
- return createSqlDataTypeSpecByName("Date", typeName,
type.isNullable());
- case TIMESTAMP:
- case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
- return createSqlDataTypeSpecByName("DateTime", typeName,
type.isNullable());
- default:
- break;
- }
+ SqlTypeName typeName = type.getSqlTypeName();
+ switch (typeName) {
+ case CHAR:
+ return createSqlDataTypeSpecByName(
+ String.format(Locale.ROOT, "FixedString(%s)",
+ type.getPrecision()), typeName, type.isNullable());
+ case VARCHAR:
+ return createSqlDataTypeSpecByName("String", typeName,
type.isNullable());
+ case TINYINT:
+ return createSqlDataTypeSpecByName("Int8", typeName, type.isNullable());
+ case SMALLINT:
+ return createSqlDataTypeSpecByName("Int16", typeName, type.isNullable());
+ case INTEGER:
+ return createSqlDataTypeSpecByName("Int32", typeName, type.isNullable());
+ case BIGINT:
+ return createSqlDataTypeSpecByName("Int64", typeName, type.isNullable());
+ case REAL:
+ return createSqlDataTypeSpecByName("Float32", typeName,
type.isNullable());
+ case FLOAT:
+ case DOUBLE:
+ return createSqlDataTypeSpecByName("Float64", typeName,
type.isNullable());
+ case DATE:
+ return createSqlDataTypeSpecByName("Date", typeName, type.isNullable());
+ case TIMESTAMP:
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ return createSqlDataTypeSpecByName("DateTime", typeName,
type.isNullable());
+ case MAP:
+ return RelToSqlConverterUtil.getCastSpecClickHouseSqlMapType(this, type,
+ SqlParserPos.ZERO);
+ case ARRAY:
+ return RelToSqlConverterUtil.getCastSpecClickHouseSqlArrayType(this,
type,
+ SqlParserPos.ZERO);
+ case MULTISET:
+ throw new UnsupportedOperationException("ClickHouse dialect does not
support cast to "
+ + type.getSqlTypeName());
+ default:
+ break;
}
return super.getCastSpec(type);
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 15fa6fe24c..b165448615 100644
--- a/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
+++ b/core/src/main/java/org/apache/calcite/util/RelToSqlConverterUtil.java
@@ -16,20 +16,29 @@
*/
package org.apache.calcite.util;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCharStringLiteral;
+import org.apache.calcite.sql.SqlCollectionTypeNameSpec;
+import org.apache.calcite.sql.SqlDataTypeSpec;
+import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlMapTypeNameSpec;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlSpecialOperator;
+import org.apache.calcite.sql.SqlTypeNameSpec;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.fun.SqlTrimFunction;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.ArraySqlType;
+import org.apache.calcite.sql.type.MapSqlType;
+import org.apache.calcite.sql.type.SqlTypeName;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REGEXP_REPLACE_3;
@@ -328,4 +337,95 @@ public static void unparseBoolLiteralToCondition(SqlWriter
writer, boolean value
writer.literal(value ? "1" : "0");
writer.endList(frame);
}
+
+ /**
+ * Transformation Map type from {@code MAP<VARCHAR,VARCHAR>} to {@code
Map(VARCHAR,VARCHAR)}.
+ */
+ public static SqlDataTypeSpec getCastSpecClickHouseSqlMapType(SqlDialect
dialect,
+ RelDataType type, SqlParserPos pos) {
+ MapSqlType mapSqlType = (MapSqlType) type;
+ SqlDataTypeSpec keySpec = (SqlDataTypeSpec)
dialect.getCastSpec(mapSqlType.getKeyType());
+ SqlDataTypeSpec valueSpec =
+ (SqlDataTypeSpec) dialect.getCastSpec(mapSqlType.getValueType());
+ SqlDataTypeSpec nonNullKeySpec =
+ requireNonNull(keySpec, "keySpec");
+ SqlDataTypeSpec nonNullValueSpec =
+ requireNonNull(valueSpec, "valueSpec");
+ SqlMapTypeNameSpec sqlMapTypeNameSpec =
+ new ClickHouseSqlMapTypeNameSpec(nonNullKeySpec, nonNullValueSpec,
pos);
+ return new SqlDataTypeSpec(sqlMapTypeNameSpec,
+ SqlParserPos.ZERO);
+ }
+
+ /**
+ * Transformation Map type from {@code VARCHAR ARRAY} to {@code
Array(VARCHAR)}.
+ */
+ public static SqlDataTypeSpec getCastSpecClickHouseSqlArrayType(SqlDialect
dialect,
+ RelDataType type, SqlParserPos pos) {
+ ArraySqlType arraySqlType = (ArraySqlType) type;
+ SqlDataTypeSpec arrayValueSpec =
+ (SqlDataTypeSpec) dialect.getCastSpec(arraySqlType.getComponentType());
+ SqlDataTypeSpec nonNullarrayValueSpec =
+ requireNonNull(arrayValueSpec, "arrayValueSpec");
+ ClickHouseSqlArrayTypeNameSpec sqlArrayTypeNameSpec =
+ new
ClickHouseSqlArrayTypeNameSpec(nonNullarrayValueSpec.getTypeNameSpec(),
+ arraySqlType.getSqlTypeName(), pos);
+ return new SqlDataTypeSpec(sqlArrayTypeNameSpec, SqlParserPos.ZERO);
+ }
+
+ /**
+ * ClickHouseSqlMapTypeNameSpec to parse or unparse SQL MAP type to {@code
Map(VARCHAR, VARCHAR)}.
+ */
+ public static class ClickHouseSqlMapTypeNameSpec extends SqlMapTypeNameSpec {
+
+ /**
+ * Creates a {@code SqlMapTypeNameSpec}.
+ * example: MAP type would convert to Map(VARCHAR, VARCHAR).
+ *
+ * @param keyType key type of the Map
+ * @param valType value type of the Map
+ * @param pos the parser position, must not be null
+ */
+ public ClickHouseSqlMapTypeNameSpec(SqlDataTypeSpec keyType,
+ SqlDataTypeSpec valType, SqlParserPos pos) {
+ super(keyType, valType, pos);
+ }
+
+ @Override public void unparse(SqlWriter writer, int leftPrec, int
rightPrec) {
+ writer.print("Map");
+ SqlWriter.Frame frame =
+ writer.startList(SqlWriter.FrameTypeEnum.FUN_CALL, "(", ")");
+ writer.sep(","); // configures the writer
+ getKeyType().unparse(writer, leftPrec, rightPrec);
+ writer.sep(",");
+ getValType().unparse(writer, leftPrec, rightPrec);
+ writer.endList(frame);
+ }
+ }
+
+ /**
+ * A ClickHouseSqlArrayTypeNameSpec to parse or unparse SQL ARRAY type to
{@code Array(VARCHAR)}.
+ */
+ public static class ClickHouseSqlArrayTypeNameSpec extends
SqlCollectionTypeNameSpec {
+
+ /**
+ * Creates a {@code ClickHouseSqlArrayTypeNameSpec}.
+ * example: integer array would convert to Array(integer).
+ *
+ * @param elementTypeName Type of the collection element
+ * @param collectionTypeName Collection type name
+ * @param pos Parser position, must not be null
+ */
+ public ClickHouseSqlArrayTypeNameSpec(SqlTypeNameSpec elementTypeName,
+ SqlTypeName collectionTypeName, SqlParserPos pos) {
+ super(elementTypeName, collectionTypeName, pos);
+ }
+
+ @Override public void unparse(SqlWriter writer, int leftPrec, int
rightPrec) {
+ writer.print("Array");
+ SqlWriter.Frame frame =
writer.startList(SqlWriter.FrameTypeEnum.FUN_CALL, "(", ")");
+ this.getElementTypeName().unparse(writer, leftPrec, rightPrec);
+ writer.endList(frame);
+ }
+ }
}
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 b400b85a04..59a4147deb 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
@@ -2601,6 +2601,56 @@ private SqlDialect nonOrdinalDialect() {
sql(query4).withStarRocks().ok(expectedStarRocks4);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7081";>[CALCITE-7081]
+ * Invalid unparse for cast to nested type in ClickHouse</a>.
+ */
+ @Test void testCastNestedClickHouse() {
+ // All converted sql had been test passed in ClickHouse env.
+ final String query = "select cast(array['a','b','c']"
+ + " as varchar array)";
+ final String expectedClickHouse =
+ "SELECT CAST(array('a', 'b', 'c') AS Array(`String`))";
+ sql(query).withClickHouse().ok(expectedClickHouse);
+
+ final String query0 = "select cast(array['a','b','c',null]"
+ + " as varchar array)";
+ final String expectedClickHouse0 =
+ "SELECT CAST(array('a', 'b', 'c', NULL) AS Array(`Nullable(String)`))";
+ sql(query0).withClickHouse().ok(expectedClickHouse0);
+
+ final String query1 = "select cast(array[array['a'], array['b'],
array['c']]"
+ + " as varchar array array)";
+ final String expectedClickHouse1 =
+ "SELECT CAST(array(array('a'), array('b'), array('c')) AS
Array(Array(`String`)))";
+ sql(query1).withClickHouse().ok(expectedClickHouse1);
+
+ final String query2 = "select
cast(array[MAP['a','1'],MAP['b','2'],MAP['c','3']]"
+ + " as MAP<varchar,varchar> array)";
+ final String expectedClickHouse2 =
+ "SELECT CAST(array(map('a', '1'), map('b', '2'), map('c', '3'))"
+ + " AS Array(Map(`String`, `Nullable(String)`)))";
+ sql(query2).withClickHouse().ok(expectedClickHouse2);
+
+ final String query3 = "select cast(MAP['a',ARRAY[1,2,3]]"
+ + " as MAP<varchar,integer array>)";
+ final String expectedClickHouse3 =
+ "SELECT CAST(map('a', array(1, 2, 3)) AS Map(`String`,
Array(`Nullable(Int32)`)))";
+ sql(query3).withClickHouse().ok(expectedClickHouse3);
+
+ final String query4 = "select cast(MAP['a',ARRAY[1.0,2.0,3.0]]"
+ + " as MAP<varchar,real array>)";
+ final String expectedClickHouse4 =
+ "SELECT CAST(map('a', array(1.0, 2.0, 3.0)) AS Map(`String`,
Array(`Nullable(Float32)`)))";
+ sql(query4).withClickHouse().ok(expectedClickHouse4);
+
+ final String query5 = "select cast(MAP['a',MAP['b','c']]"
+ + " as MAP<varchar,MAP<varchar,varchar>>)";
+ final String expectedClickHouse5 =
+ "SELECT CAST(map('a', map('b', 'c')) AS Map(`String`, Map(`String`,
`Nullable(String)`)))";
+ sql(query5).withClickHouse().ok(expectedClickHouse5);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6088";>[CALCITE-6088]
* SqlItemOperator fails in RelToSqlConverter</a>. */
@@ -10206,6 +10256,7 @@ private void checkLiteral2(String expression, String
expected) {
sql(query2)
.withPhoenix().throws_("Phoenix dialect does not support cast to
MULTISET")
.withStarRocks().throws_("StarRocks dialect does not support cast to
MULTISET")
+ .withClickHouse().throws_("ClickHouse dialect does not support cast to
MULTISET")
.withHive().throws_("Hive dialect does not support cast to MULTISET");
String query3 = "SELECT CAST(MAP[1.0,2.0,3.0,4.0] AS MAP<FLOAT, REAL>)
FROM \"employee\"";
