[DRILL-2313] Query fails when one of the operands is a DATE literal without an explicit cast

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.8.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Abhishek Girish
• Created: 25/Feb/15 20:05
• Updated: 01/Oct/15 22:10
• Resolved: 04/Sep/15 17:44

Description

For operations involving the date datatype, when one of the operands is a DATE literal without a cast, query fails.

The following query fails to validate:

SELECT
         *
FROM     
         date_dim
         
WHERE    d_date BETWEEN '2002-3-01' AND cast('2002-3-01' AS DATE) 

LIMIT 1;

Query failed: SqlValidatorException: Cannot apply 'BETWEEN' to arguments of type '<ANY> BETWEEN <CHAR(9)> AND <DATE>'. Supported form(s): '<COMPARABLE_TYPE> BETWEEN <COMPARABLE_TYPE> AND <COMPARABLE_TYPE>'

The following query executes fine:

SELECT
         *
FROM     
         date_dim
         
WHERE    d_date BETWEEN '2002-3-01' AND 
                  '2002-3-01'
LIMIT 1;

Both the queries execute fine on postgres

Issue Links

• relates to: DRILL-2332 Drill should be consistent with Implicit casting rules across data formats