You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by da...@apache.org on 2020/04/16 03:56:49 UTC
[calcite] 01/01: [CALCITE-3894] The Union operation between DATE
with TIMESTAMP returns a wrong result
This is an automated email from the ASF dual-hosted git repository.
danny0405 pushed a commit to branch CALCITE-3894
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit f34326382b28128dee9fb7fc4fd8f48ab111e271
Author: yuzhao.cyz <yu...@gmail.com>
AuthorDate: Thu Apr 16 11:48:40 2020 +0800
[CALCITE-3894] The Union operation between DATE with TIMESTAMP returns a wrong result
The RelDataTypeFactory#leastRestrictive finds the common type for IN,
CASE and SET operations. For common type with DATE and TIMESTAMP, it
returns DATE. The root cause is that rules in SqlTypeAssignmentRule
decide that DATE is assignable from TIMESTAMP and TIMESTAMP is
assignable from DATE, which is actually wrong. Although in Java, this
assignment makes sense but that does not mean it's true in SQL, because DATE
and TIMESTAMP have different time unit.
---
.../apache/calcite/sql/type/SqlTypeAssignmentRule.java | 2 --
.../test/java/org/apache/calcite/test/JdbcTest.java | 14 ++++++++++++++
.../apache/calcite/test/TypeCoercionConverterTest.java | 7 +++++++
.../java/org/apache/calcite/test/TypeCoercionTest.java | 18 ++++++++++++++++++
.../apache/calcite/test/TypeCoercionConverterTest.xml | 13 +++++++++++++
5 files changed, 52 insertions(+), 2 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeAssignmentRule.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeAssignmentRule.java
index 86be0fa..c345105 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeAssignmentRule.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeAssignmentRule.java
@@ -162,13 +162,11 @@ public class SqlTypeAssignmentRule implements SqlTypeMappingRule {
// DATE is assignable from...
rule.clear();
rule.add(SqlTypeName.DATE);
- rule.add(SqlTypeName.TIMESTAMP);
rules.add(SqlTypeName.DATE, rule);
// TIME is assignable from...
rule.clear();
rule.add(SqlTypeName.TIME);
- rule.add(SqlTypeName.TIMESTAMP);
rules.add(SqlTypeName.TIME, rule);
// TIME WITH LOCAL TIME ZONE is assignable from...
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index bbf3071..5e36781 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -7401,6 +7401,20 @@ public class JdbcTest {
.runs();
}
+ /**
+ * Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-3894">[CALCITE-3894]
+ * The Union operation between DATE with TIMESTAMP returns a wrong result</a>.
+ */
+ @Test public void testUnionDateTime() {
+ CalciteAssert.AssertThat assertThat = CalciteAssert.that();
+ String query = "select * from (\n"
+ + "select \"id\" from (VALUES(DATE '2018-02-03')) \"foo\"(\"id\")\n"
+ + "union\n"
+ + "select \"id\" from (VALUES(TIMESTAMP '2008-03-31 12:23:34')) \"foo\"(\"id\"))";
+ assertThat.query(query).returns("id=2008-03-31 12:23:34\nid=2018-02-03 00:00:00\n");
+ }
+
private static String sums(int n, boolean c) {
final StringBuilder b = new StringBuilder();
for (int i = 0; i < n; i++) {
diff --git a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
index 03a5fbb..029f44f 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
@@ -61,6 +61,13 @@ class TypeCoercionConverterTest extends SqlToRelTestBase {
+ "from (values (true, true, true))");
}
+ /** Test cases for {@link TypeCoercion#inOperationCoercion}. */
+ @Test void testInDateTimestamp() {
+ checkPlanEquals("select (t1_timestamp, t1_date)\n"
+ + "in ((DATE '2020-04-16', TIMESTAMP '2020-04-16 11:40:53'))\n"
+ + "from t1");
+ }
+
/** Test cases for
* {@link org.apache.calcite.sql.validate.implicit.TypeCoercionImpl#booleanEquality}. */
@Test void testBooleanEquality() {
diff --git a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
index b41c7f6..ddd0a47 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionTest.java
@@ -483,6 +483,12 @@ class TypeCoercionTest extends SqlValidatorTestCase {
+ "union select t1_int from t1")
.columnType("VARCHAR NOT NULL");
+ // date union timestamp
+ sql("select t1_date, t1_timestamp from t1\n"
+ + "union select t2_timestamp, t2_date from t2")
+ .type("RecordType(TIMESTAMP(0) NOT NULL T1_DATE,"
+ + " TIMESTAMP(0) NOT NULL T1_TIMESTAMP) NOT NULL");
+
// intersect
sql("select t1_int, t1_decimal, t1_smallint, t1_double from t1 "
+ "intersect select t2_varchar20, t2_decimal, t2_float, t2_bigint from t2 ")
@@ -610,6 +616,15 @@ class TypeCoercionTest extends SqlValidatorTestCase {
// timestamp int varchar
sql("select COALESCE(t1_timestamp, t1_int, t1_varchar20) from t1")
.type("RecordType(TIMESTAMP(0) NOT NULL EXPR$0) NOT NULL");
+ // timestamp date
+ sql("select COALESCE(t1_timestamp, t1_date) from t1")
+ .type("RecordType(TIMESTAMP(0) NOT NULL EXPR$0) NOT NULL");
+ // date timestamp
+ sql("select COALESCE(t1_timestamp, t1_date) from t1")
+ .type("RecordType(TIMESTAMP(0) NOT NULL EXPR$0) NOT NULL");
+ // null date timestamp
+ sql("select COALESCE(t1_timestamp, t1_date) from t1")
+ .type("RecordType(TIMESTAMP(0) NOT NULL EXPR$0) NOT NULL");
// case when
// smallint int char
@@ -630,6 +645,9 @@ class TypeCoercionTest extends SqlValidatorTestCase {
// bigint decimal
sql("select case when 1 > 0 then t2_bigint else t2_decimal end from t2")
.type("RecordType(DECIMAL(19, 0) NOT NULL EXPR$0) NOT NULL");
+ // date timestamp
+ sql("select case when 1 > 0 then t2_date else t2_timestamp end from t2")
+ .type("RecordType(TIMESTAMP(0) NOT NULL EXPR$0) NOT NULL");
}
/** Test case for {@link AbstractTypeCoercion#implicitCast} */
diff --git a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
index 9773f2b..d4d3585 100644
--- a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -31,6 +31,19 @@ LogicalProject(F0=[true], F1=[true], F2=[true])
]]>
</Resource>
</TestCase>
+ <TestCase name="testInDateTimestamp">
+ <Resource name="sql">
+ <![CDATA[select (t1_timestamp, t1_date)
+in ((DATE '2020-04-16', TIMESTAMP '2020-04-16 11:40:53'))
+from t1]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[AND(=($7, 2020-04-16 00:00:00), =(CAST($8):TIMESTAMP(0) NOT NULL, 2020-04-16 11:40:53))])
+ LogicalTableScan(table=[[CATALOG, SALES, T1]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testBooleanEquality">
<Resource name="sql">
<![CDATA[select