[ https://issues.apache.org/jira/browse/CALCITE-1296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15335133#comment-15335133 ]
Julian Hyde commented on CALCITE-1296: -------------------------------------- Sounds reasonable. I think you're just talking about comparison of DATE and TIMESTAMP, since Calcite doesn't support the other kinds of TIMESTAMP. Does Oracle allow implicit conversion, e.g. inserting a DATE into a TIMESTAMP column? If so, is this in scope for this case? > 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)