[
https://issues.apache.org/jira/browse/CALCITE-1296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15335349#comment-15335349
]
Jinfeng Ni commented on CALCITE-1296:
-------------------------------------
Right. I'm talking about comparison of DATE and TIMESTAMP.
For implicit conversion during insert, it's not in the scope for this case
(This case is about comparison operator).
ps: I did a quick check Oracle's implicit conversion, and it seems to allow
insert DATE into a TIMESTAMP column.
{code}
create table t1 (c1 timestamp);
Table created.
SQL> insert into t1 values(date '1990-01-01');
1 row created.
{code}
> Different classes of datetime should be able to compare.
> --------------------------------------------------------
>
> Key: CALCITE-1296
> URL: https://issues.apache.org/jira/browse/CALCITE-1296
> Project: Calcite
> Issue Type: Improvement
> Reporter: Jinfeng Ni
> Assignee: Julian Hyde
>
> This is follow-up from a discussion in DRILL-4525. Currently, Calcite does
> not allow the comparison between date vs timestamp; LHS and RHS have to have
> the same type.
> {code}
> select CAST('1990-01-01' AS DATE) < CAST('2001-01-01' AS TIMESTAMP) FROM
> (VALUES(1, 2)) AS T(A,B);
> Mar 24, 2016 8:15:53 AM
> org.apache.calcite.sql.validate.SqlValidatorException <init>
> SEVERE: org.apache.calcite.sql.validate.SqlValidatorException: Cannot
> apply '<' to arguments of type '<DATE> < <TIMESTAMP(0)>'. Supported form(s):
> '<COMPARABLE_TYPE> < <COMPARABLE_TYPE>'
> {code}
> This behavior is different from Oracle and Postgres. Both of them allow
> implicit cast between date and timestamp, and hence allow the comparison.
> It seems to make sense to allow different classes of datetime to be
> comparable in Calcite.
> Oracle:
> {code}
> select count(*) from dual
> where Date '1990-01-01' < TIMESTAMP '1990-01-01 00:01:02';
> 2
> COUNT(*)
> ----------
> 1
> {code}
> Postgres:
> {code}
> select CAST('1990-01-01' AS DATE) < CAST('2001-01-01' AS TIMESTAMP) FROM
> (VALUES(1, 2)) AS T(A,B);
> ?column?
> ----------
> t
> (1 row)
> {code}
> In particular, Oracle doc has the following description [1].
> "
> Datetime Comparisons
> When you compare date and timestamp values, Oracle converts the data to the
> more precise datatype before doing the comparison. For example, if you
> compare data of TIMESTAMP WITH TIME ZONE datatype with data of TIMESTAMP
> datatype, Oracle converts the TIMESTAMP data to TIMESTAMP WITH TIME ZONE,
> using the session time zone.
> The order of precedence for converting date and timestamp data is as follows:
> 1. DATE
> 2. TIMESTAMP
> 3. TIMESTAMP WITH LOCAL TIME ZONE
> 4. TIMESTAMP WITH TIME ZONE
> For any pair of datatypes, Oracle converts the datatype that has a smaller
> number in the preceding list to the datatype with the larger number.
> "
> [1]
> https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006333
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
