Jinfeng Ni created CALCITE-1296:
-----------------------------------
Summary: 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)
