[
https://issues.apache.org/jira/browse/CALCITE-3894?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17084520#comment-17084520
]
Danny Chen edited comment on CALCITE-3894 at 4/16/20, 4:10 AM:
---------------------------------------------------------------
Finally i got the reason:
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, 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.
Fix the rules in SqlTypeAssignmentRule and let the implicit type
coercion rule get involved.
was (Author: danny0405):
Finally i got the reason:
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.
> The Union operation between DATE with TIMESTAMP returns a wrong result
> ----------------------------------------------------------------------
>
> Key: CALCITE-3894
> URL: https://issues.apache.org/jira/browse/CALCITE-3894
> Project: Calcite
> Issue Type: Bug
> Reporter: TANG Wen-hui
> Assignee: TANG Wen-hui
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testUnionTime() {
> 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=2108-03-12\nid=2018-02-03\n");
> }{code}
> The test with wrong result passed.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
