[CALCITE-1296] Different classes of datetime should be able to compare.

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Jinfeng Ni
• Created: 17/Jun/16 00:56
• Updated: 22/Feb/19 01:22

Description

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.

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>'

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:

select count(*) from dual
where Date '1990-01-01'  < TIMESTAMP '1990-01-01 00:01:02';
  2
  COUNT(*)
----------
	 1

Postgres:

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)

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