This is an automated email from the ASF dual-hosted git repository.
jhyde 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 d3ab0bc8e4 [CALCITE-6150] JDBC adapter for ClickHouse generates
incorrect SQL for certain units in the EXTRACT function
d3ab0bc8e4 is described below
commit d3ab0bc8e4d4c9ebc0fc4e33ce478d276f5d11e4
Author: chen768959 <[email protected]>
AuthorDate: Fri Dec 1 20:24:50 2023 +0800
[CALCITE-6150] JDBC adapter for ClickHouse generates incorrect SQL for
certain units in the EXTRACT function
Close apache/calcite#3557
---
.../calcite/sql/dialect/ClickHouseSqlDialect.java | 23 ++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 69 ++++++++++++++++++++++
2 files changed, 92 insertions(+)
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 bd4b2a052c..802f562c3d 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
@@ -175,6 +175,29 @@ public class ClickHouseSqlDialect extends SqlDialect {
super.unparseCall(writer, call, leftPrec, rightPrec);
}
break;
+ case EXTRACT:
+ SqlLiteral node = call.operand(0);
+ TimeUnitRange unit = node.getValueAs(TimeUnitRange.class);
+ String funName;
+ switch (unit) {
+ case DOW:
+ funName = "DAYOFWEEK";
+ break;
+ case DOY:
+ funName = "DAYOFYEAR";
+ break;
+ case WEEK:
+ funName = "toWeek";
+ break;
+ default:
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
+ }
+ writer.print(funName);
+ final SqlWriter.Frame frame = writer.startList("(", ")");
+ call.operand(1).unparse(writer, 0, 0);
+ writer.endList(frame);
+ break;
default:
super.unparseCall(writer, call, 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 79b6b0b983..8f10a1386a 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
@@ -2264,6 +2264,75 @@ class RelToSqlConverterTest {
sql(query).withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6150";>[CALCITE-6150]
+ * JDBC adapter for ClickHouse generates incorrect SQL for certain units in
+ * the EXTRACT function</a>. Also tests other units in other dialects. */
+ @Test void testExtract() {
+ final String sql = "SELECT\n"
+ + "EXTRACT(YEAR FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(QUARTER FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(MONTH FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(WEEK FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(DOY FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(DAY FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(DOW FROM DATE '2023-12-01'),\n"
+ + "EXTRACT(HOUR FROM TIMESTAMP '2023-12-01 00:00:00'),\n"
+ + "EXTRACT(MINUTE FROM TIMESTAMP '2023-12-01 00:00:00'),\n"
+ + "EXTRACT(SECOND FROM TIMESTAMP '2023-12-01 00:00:00')";
+ final String expectedClickHouse = "SELECT "
+ + "EXTRACT(YEAR FROM toDate('2023-12-01')), "
+ + "EXTRACT(QUARTER FROM toDate('2023-12-01')), "
+ + "EXTRACT(MONTH FROM toDate('2023-12-01')), "
+ + "toWeek(toDate('2023-12-01')), "
+ + "DAYOFYEAR(toDate('2023-12-01')), "
+ + "EXTRACT(DAY FROM toDate('2023-12-01')), "
+ + "DAYOFWEEK(toDate('2023-12-01')), "
+ + "EXTRACT(HOUR FROM toDateTime('2023-12-01 00:00:00')), "
+ + "EXTRACT(MINUTE FROM toDateTime('2023-12-01 00:00:00')), "
+ + "EXTRACT(SECOND FROM toDateTime('2023-12-01 00:00:00'))";
+ final String expectedHive = "SELECT "
+ + "EXTRACT(YEAR FROM DATE '2023-12-01'), "
+ + "EXTRACT(QUARTER FROM DATE '2023-12-01'), "
+ + "EXTRACT(MONTH FROM DATE '2023-12-01'), "
+ + "EXTRACT(WEEK FROM DATE '2023-12-01'), "
+ + "EXTRACT(DOY FROM DATE '2023-12-01'), "
+ + "EXTRACT(DAY FROM DATE '2023-12-01'), "
+ + "EXTRACT(DOW FROM DATE '2023-12-01'), "
+ + "EXTRACT(HOUR FROM TIMESTAMP '2023-12-01 00:00:00'), "
+ + "EXTRACT(MINUTE FROM TIMESTAMP '2023-12-01 00:00:00'), "
+ + "EXTRACT(SECOND FROM TIMESTAMP '2023-12-01 00:00:00')";
+ final String expectedPostgresql = "SELECT "
+ + "EXTRACT(YEAR FROM DATE '2023-12-01'), "
+ + "EXTRACT(QUARTER FROM DATE '2023-12-01'), "
+ + "EXTRACT(MONTH FROM DATE '2023-12-01'), "
+ + "EXTRACT(WEEK FROM DATE '2023-12-01'), "
+ + "EXTRACT(DOY FROM DATE '2023-12-01'), "
+ + "EXTRACT(DAY FROM DATE '2023-12-01'), "
+ + "EXTRACT(DOW FROM DATE '2023-12-01'), "
+ + "EXTRACT(HOUR FROM TIMESTAMP '2023-12-01 00:00:00'), "
+ + "EXTRACT(MINUTE FROM TIMESTAMP '2023-12-01 00:00:00'), "
+ + "EXTRACT(SECOND FROM TIMESTAMP '2023-12-01 00:00:00')\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
+ final String expectedHsqldb = "SELECT "
+ + "EXTRACT(YEAR FROM DATE '2023-12-01'), "
+ + "EXTRACT(QUARTER FROM DATE '2023-12-01'), "
+ + "EXTRACT(MONTH FROM DATE '2023-12-01'), "
+ + "EXTRACT(WEEK FROM DATE '2023-12-01'), "
+ + "EXTRACT(DOY FROM DATE '2023-12-01'), "
+ + "EXTRACT(DAY FROM DATE '2023-12-01'), "
+ + "EXTRACT(DOW FROM DATE '2023-12-01'), "
+ + "EXTRACT(HOUR FROM TIMESTAMP '2023-12-01 00:00:00'), "
+ + "EXTRACT(MINUTE FROM TIMESTAMP '2023-12-01 00:00:00'), "
+ + "EXTRACT(SECOND FROM TIMESTAMP '2023-12-01 00:00:00')\n"
+ + "FROM (VALUES (0)) AS t (ZERO)";
+ sql(sql)
+ .withClickHouse().ok(expectedClickHouse)
+ .withHive().ok(expectedHive)
+ .withPostgresql().ok(expectedPostgresql)
+ .withHsqldb().ok(expectedHsqldb);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3220";>[CALCITE-3220]
* HiveSqlDialect should transform the SQL-standard TRIM function to TRIM,
