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 1ae39588d5 [CALCITE-6988] DuckDB dialect implementation
1ae39588d5 is described below
commit 1ae39588d53268811470b8451713d2647c17fca3
Author: Yu Xu <[email protected]>
AuthorDate: Mon Apr 28 14:56:32 2025 +0800
[CALCITE-6988] DuckDB dialect implementation
---
.../java/org/apache/calcite/sql/SqlDialect.java | 3 +
.../apache/calcite/sql/SqlDialectFactoryImpl.java | 3 +
.../calcite/sql/dialect/DuckDBSqlDialect.java | 162 +++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 96 ++++++++++--
4 files changed, 251 insertions(+), 13 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 e6525a1d21..2902bf1421 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -277,6 +277,8 @@ public static DatabaseProduct getProduct(
return DatabaseProduct.CLICKHOUSE;
case "DBMS:CLOUDSCAPE":
return DatabaseProduct.DERBY;
+ case "DUCKDB":
+ return DatabaseProduct.DUCKDB;
case "EXASOL":
return DatabaseProduct.EXASOL;
case "FIREBOLT":
@@ -1427,6 +1429,7 @@ public enum DatabaseProduct {
ORACLE("Oracle", "\"", NullCollation.HIGH),
DERBY("Apache Derby", null, NullCollation.HIGH),
DB2("IBM DB2", null, NullCollation.HIGH),
+ DUCKDB("DUCKDB", null, NullCollation.LAST),
DORIS("Doris", "`", NullCollation.LOW),
EXASOL("Exasol", "\"", NullCollation.LOW),
FIREBIRD("Firebird", null, NullCollation.HIGH),
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 9e845d0821..1553a23bbb 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java
@@ -24,6 +24,7 @@
import org.apache.calcite.sql.dialect.Db2SqlDialect;
import org.apache.calcite.sql.dialect.DerbySqlDialect;
import org.apache.calcite.sql.dialect.DorisSqlDialect;
+import org.apache.calcite.sql.dialect.DuckDBSqlDialect;
import org.apache.calcite.sql.dialect.ExasolSqlDialect;
import org.apache.calcite.sql.dialect.FirebirdSqlDialect;
import org.apache.calcite.sql.dialect.FireboltSqlDialect;
@@ -185,6 +186,8 @@ public class SqlDialectFactoryImpl implements
SqlDialectFactory {
return Db2SqlDialect.DEFAULT;
case DERBY:
return DerbySqlDialect.DEFAULT;
+ case DUCKDB:
+ return DuckDBSqlDialect.DEFAULT;
case DORIS:
return DorisSqlDialect.DEFAULT;
case EXASOL:
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/DuckDBSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/DuckDBSqlDialect.java
new file mode 100644
index 0000000000..e7d3333ba3
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/DuckDBSqlDialect.java
@@ -0,0 +1,162 @@
+/*
+ * 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.RelDataTypeSystem;
+import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+/**
+ * A <code>SqlDialect</code> implementation for the DuckDB database.
+ */
+public class DuckDBSqlDialect extends SqlDialect {
+ public static final RelDataTypeSystem TYPE_SYSTEM =
+ new RelDataTypeSystemImpl() {
+
+ // We can refer to document of DuckDB 1.2.x:
+ //
https://duckdb.org/docs/stable/sql/data_types/numeric#fixed-point-decimals
+ @Override public int getMaxPrecision(SqlTypeName typeName) {
+ switch (typeName) {
+ case DECIMAL:
+ return 38;
+ default:
+ return super.getMaxPrecision(typeName);
+ }
+ }
+
+ @Override public int getMaxScale(SqlTypeName typeName) {
+ switch (typeName) {
+ case DECIMAL:
+ return 38;
+ default:
+ return super.getMaxScale(typeName);
+ }
+ }
+
+ @Override public int getMaxNumericScale() {
+ return getMaxScale(SqlTypeName.DECIMAL);
+ }
+ };
+
+ public static final SqlDialect.Context DEFAULT_CONTEXT =
SqlDialect.EMPTY_CONTEXT
+ .withDatabaseProduct(SqlDialect.DatabaseProduct.DUCKDB)
+ .withIdentifierQuoteString("\"")
+ // Refer to document:
https://duckdb.org/docs/stable/sql/query_syntax/orderby.html
+ .withNullCollation(NullCollation.LAST)
+ .withDataTypeSystem(TYPE_SYSTEM);
+
+ public static final SqlDialect DEFAULT = new
DuckDBSqlDialect(DEFAULT_CONTEXT);
+
+ /** Creates a DuckDBSqlDialect. */
+ public DuckDBSqlDialect(SqlDialect.Context context) {
+ super(context);
+ }
+
+ @Override public boolean supportsApproxCountDistinct() {
+ return true;
+ }
+
+ @Override public boolean supportsAliasedValues() {
+ return false;
+ }
+
+ @Override public void unparseCall(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ switch (call.getKind()) {
+ 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 FLOOR:
+ if (call.operandCount() != 2) {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
+ }
+ unparseFloor(writer, call);
+ break;
+ case CHAR_LENGTH:
+ SqlCall lengthCall = SqlLibraryOperators.LENGTH
+ .createCall(SqlParserPos.ZERO, call.getOperandList());
+ super.unparseCall(writer, lengthCall, leftPrec, rightPrec);
+ break;
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ }
+ }
+
+ private static void unparseFloor(SqlWriter writer, SqlCall call) {
+ SqlLiteral node = call.operand(1);
+ TimeUnitRange unit = node.getValueAs(TimeUnitRange.class);
+
+ String format;
+ switch (unit) {
+ case YEAR:
+ format = "year";
+ break;
+ case MONTH:
+ format = "month";
+ break;
+ case WEEK:
+ format = "week";
+ break;
+ case DAY:
+ format = "day";
+ break;
+ case HOUR:
+ format = "hour";
+ break;
+ case MINUTE:
+ format = "minute";
+ break;
+ case SECOND:
+ format = "second";
+ break;
+ case MILLISECOND:
+ format = "milliseconds";
+ break;
+ case MICROSECOND:
+ format = "microseconds";
+ break;
+ default:
+ throw new AssertionError("DUCKDB does not support FLOOR for time unit: "
+ + unit);
+ }
+
+ // Refer to document:
https://duckdb.org/docs/stable/sql/functions/date#date_truncpart-date
+ writer.print("DATETRUNC");
+ SqlWriter.Frame frame = writer.startList("(", ")");
+ writer.print("'" + format + "'");
+ writer.sep(",", true);
+ call.operand(0).unparse(writer, 0, 0);
+ 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 77a41f894d..66766eb0b9 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
@@ -58,6 +58,7 @@
import org.apache.calcite.sql.dialect.BigQuerySqlDialect;
import org.apache.calcite.sql.dialect.CalciteSqlDialect;
import org.apache.calcite.sql.dialect.ClickHouseSqlDialect;
+import org.apache.calcite.sql.dialect.DuckDBSqlDialect;
import org.apache.calcite.sql.dialect.HiveSqlDialect;
import org.apache.calcite.sql.dialect.JethroDataSqlDialect;
import org.apache.calcite.sql.dialect.MssqlSqlDialect;
@@ -5351,52 +5352,92 @@ private void checkLiteral2(String expression, String
expected) {
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6949";>[CALCITE-6949]
- * ClickHouse not support floor date to
SECOND/MILLISECOND/MICROSECOND/NANOSECOND</a>. */
- @Test void testFloorClickHouse() {
+ * ClickHouse not support floor date to
SECOND/MILLISECOND/MICROSECOND/NANOSECOND</a>,
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6988";>[CALCITE-6988]
+ * DuckDB dialect implementation</a>.
+ * */
+ @Test void testFloorFunction() {
String query = "SELECT floor(\"hire_date\" TO YEAR) FROM \"employee\"";
String expectedClickHouse = "SELECT toStartOfYear(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query).withClickHouse().ok(expectedClickHouse);
+ String expectedDuckDB = "SELECT DATETRUNC('year', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query)
+ .withClickHouse().ok(expectedClickHouse)
+ .withDuckDB().ok(expectedDuckDB);
+
String query1 = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\"";
String expectedClickHouse1 = "SELECT toStartOfMonth(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query1).withClickHouse().ok(expectedClickHouse1);
+ String expectedDuckDB1 = "SELECT DATETRUNC('month', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query1)
+ .withClickHouse().ok(expectedClickHouse1)
+ .withDuckDB().ok(expectedDuckDB1);
String query2 = "SELECT floor(\"hire_date\" TO WEEK) FROM \"employee\"";
String expectedClickHouse2 = "SELECT toMonday(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query2).withClickHouse().ok(expectedClickHouse2);
+ String expectedDuckDB2 = "SELECT DATETRUNC('week', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query2)
+ .withClickHouse().ok(expectedClickHouse2)
+ .withDuckDB().ok(expectedDuckDB2);
String query3 = "SELECT floor(\"hire_date\" TO DAY) FROM \"employee\"";
String expectedClickHouse3 = "SELECT toDate(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query3).withClickHouse().ok(expectedClickHouse3);
+ String expectedDuckDB3 = "SELECT DATETRUNC('day', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query3)
+ .withClickHouse().ok(expectedClickHouse3)
+ .withDuckDB().ok(expectedDuckDB3);
String query4 = "SELECT floor(\"hire_date\" TO HOUR) FROM \"employee\"";
String expectedClickHouse4 = "SELECT toStartOfHour(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query4).withClickHouse().ok(expectedClickHouse4);
+ String expectedDuckDB4 = "SELECT DATETRUNC('hour', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query4)
+ .withClickHouse().ok(expectedClickHouse4)
+ .withDuckDB().ok(expectedDuckDB4);
String query5 = "SELECT floor(\"hire_date\" TO MINUTE) FROM \"employee\"";
String expectedClickHouse5 = "SELECT toStartOfMinute(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query5).withClickHouse().ok(expectedClickHouse5);
+ String expectedDuckDB5 = "SELECT DATETRUNC('minute', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query5)
+ .withClickHouse().ok(expectedClickHouse5)
+ .withDuckDB().ok(expectedDuckDB5);
String query6 = "SELECT floor(\"hire_date\" TO SECOND) FROM \"employee\"";
String expectedClickHouse6 = "SELECT toStartOfSecond(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query6).withClickHouse().ok(expectedClickHouse6);
+ String expectedDuckDB6 = "SELECT DATETRUNC('second', \"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query6)
+ .withClickHouse().ok(expectedClickHouse6)
+ .withDuckDB().ok(expectedDuckDB6);
String query7 = "SELECT floor(\"hire_date\" TO MILLISECOND) FROM
\"employee\"";
String expectedClickHouse7 = "SELECT toStartOfMillisecond(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query7).withClickHouse().ok(expectedClickHouse7);
+ String expectedDuckDB7 = "SELECT DATETRUNC('milliseconds',
\"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query7)
+ .withClickHouse().ok(expectedClickHouse7)
+ .withDuckDB().ok(expectedDuckDB7);
String query8 = "SELECT floor(\"hire_date\" TO MICROSECOND) FROM
\"employee\"";
String expectedClickHouse8 = "SELECT toStartOfMicrosecond(`hire_date`)\n"
+ "FROM `foodmart`.`employee`";
- sql(query8).withClickHouse().ok(expectedClickHouse8);
+ String expectedDuckDB8 = "SELECT DATETRUNC('microseconds',
\"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query8)
+ .withClickHouse().ok(expectedClickHouse8)
+ .withDuckDB().ok(expectedDuckDB8);
String query9 = "SELECT floor(\"hire_date\" TO NANOSECOND) FROM
\"employee\"";
String expectedClickHouse9 = "SELECT toStartOfNanosecond(`hire_date`)\n"
@@ -5948,6 +5989,18 @@ private void checkLiteral2(String expression, String
expected) {
.ok(expected);
}
+ @Test void testDuckDBDecimalPrecision() {
+ final String sql = "SELECT CAST(1.23 AS DECIMAL(38, 37))";
+ final String expected = "SELECT 1.2300000000000000000000000000000000000";
+ sql(sql).withDuckDBModifiedDecimalTypeSystem()
+ .ok(expected);
+
+ final String sql1 = "SELECT CAST('1.23000000000000000123' AS DECIMAL(38,
20))";
+ final String expected1 = "SELECT 1.23000000000000000123";
+ sql(sql1).withDuckDBModifiedDecimalTypeSystem()
+ .ok(expected1);
+ }
+
/** Test for <a
href="https://issues.apache.org/jira/browse/CALCITE-6974";>[CALCITE-6974]
* Default typesystem has incorrect limits for DECIMAL for
Presto/MySQL/Phoenix</a>. */
@Test void testDecimalPrecision() {
@@ -7832,9 +7885,11 @@ private void checkLiteral2(String expression, String
expected) {
// since it seems to be used across more dialects.
final String expectedSnowflake = "SELECT LENGTH(\"brand_name\")\n"
+ "FROM \"foodmart\".\"product\"";
+ final String expectedDuckDB = expectedSnowflake;
Sql sql = sql(query).withLibrary(SqlLibrary.BIG_QUERY);
sql.withBigQuery().ok(expectedBigQuery);
sql.withSnowflake().ok(expectedSnowflake);
+ sql.withDuckDB().ok(expectedDuckDB);
}
/** Test case for
@@ -8411,6 +8466,8 @@ private void checkLiteral2(String expression, String
expected) {
final String expectedClickhouse = "SELECT UNIQ(`product_id`)\n"
+ "FROM `foodmart`.`product`";
final String expectedHive = "SELECT COUNT(DISTINCT `product_id`)\nFROM
`foodmart`.`product`";
+ final String expectedDuckDB = "SELECT
APPROX_COUNT_DISTINCT(\"product_id\")\n"
+ + "FROM \"foodmart\".\"product\"";
sql(query).ok(expectedExact)
.withHive().ok(expectedHive)
@@ -8423,7 +8480,8 @@ private void checkLiteral2(String expression, String
expected) {
.withStarRocks().ok(expectedStarRocksSql)
.withMssql().ok(expectedMssql)
.withPhoenix().ok(expectedPhoenix)
- .withClickHouse().ok(expectedClickhouse);
+ .withClickHouse().ok(expectedClickhouse)
+ .withDuckDB().ok(expectedDuckDB);
}
@Test void testRowValueExpression() {
@@ -9346,6 +9404,7 @@ private void checkLiteral2(String expression, String
expected) {
final String expectedHive = "SELECT MAP ('k1', 'v1', 'k2', 'v2')";
final String expectedDoris = "SELECT MAP ('k1', 'v1', 'k2', 'v2')";
final String expectedClickHouse = "SELECT map('k1', 'v1', 'k2', 'v2')";
+ final String expectedDuckDB = "SELECT MAP { 'k1' : 'v1', 'k2' : 'v2' }";
sql(query)
.withPresto().ok(expectedPresto)
.withTrino().ok(expectedTrino)
@@ -9353,7 +9412,8 @@ private void checkLiteral2(String expression, String
expected) {
.withDoris().ok(expectedDoris)
.withSpark().ok(expectedSpark)
.withHive().ok(expectedHive)
- .withClickHouse().ok(expectedClickHouse);
+ .withClickHouse().ok(expectedClickHouse)
+ .withDuckDB().ok(expectedDuckDB);
}
@Test void testMapValueConstructorWithArray() {
@@ -9885,6 +9945,10 @@ Sql withClickHouse() {
return dialect(DatabaseProduct.CLICKHOUSE.getDialect());
}
+ Sql withDuckDB() {
+ return dialect(DatabaseProduct.DUCKDB.getDialect());
+ }
+
Sql withDerby() {
return dialect(DatabaseProduct.DERBY.getDialect());
}
@@ -10061,6 +10125,12 @@ Sql withClickHouseModifiedDecimalTypeSystem() {
return dialect(clickHouseSqlDialect);
}
+ Sql withDuckDBModifiedDecimalTypeSystem() {
+ final DuckDBSqlDialect duckDBSqlDialect =
+ new DuckDBSqlDialect(DuckDBSqlDialect.DEFAULT_CONTEXT);
+ return dialect(duckDBSqlDialect);
+ }
+
Sql withPrestoModifiedDecimalTypeSystem() {
final PrestoSqlDialect prestoSqlDialect =
new PrestoSqlDialect(PrestoSqlDialect.DEFAULT_CONTEXT);
