[jira] [Created] (DRILL-6859) BETWEEN dates with a slightly malformed DATE string returns false

["Boaz Ben-Zvi (JIRA)" <ji...@apache.org>]

Boaz Ben-Zvi created DRILL-6859:
-----------------------------------

             Summary: BETWEEN dates with a slightly malformed DATE string returns false
                 Key: DRILL-6859
                 URL: https://issues.apache.org/jira/browse/DRILL-6859
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
    Affects Versions: 1.14.0
            Reporter: Boaz Ben-Zvi
             Fix For: Future


(This may be a Calcite issue ....)

In the following query using BETWEEN with dates, the "month" is specified as "4", instead of "04", which causes the BETWEEN clause to evaluate to FALSE. Note that rewriting the clause with less-than etc. does work correctly.
{code:java}
0: jdbc:drill:zk=local> select count(*) from `date_dim` dd where dd.d_date BETWEEN '2000-4-01' and ( Cast('2000-4-01' AS DATE) + INTERVAL '60' day) ;
+---------+
| EXPR$0  |
+---------+
| 0       |
+---------+
1 row selected (0.184 seconds)
0: jdbc:drill:zk=local> select count(*) from `date_dim` dd where dd.d_date BETWEEN '2000-04-01' and ( Cast('2000-4-01' AS DATE) + INTERVAL '60' day) limit 10;
+---------+
| EXPR$0  |
+---------+
| 61      |
+---------+
1 row selected (0.209 seconds)
0: jdbc:drill:zk=local> select count(*) from `date_dim` dd where dd.d_date >= '2000-4-01' and dd.d_date <= '2000-5-31';
+---------+
| EXPR$0  |
+---------+
| 61      |
+---------+
1 row selected (0.227 seconds)
{code}

The physical plan for the second (good) case implements the BETWEEN clause with a FILTER on top of the scanner. For the first (failed) case, there is a "limit 0" on top of the scanner.

(This query was extracted from TPC-DS 95, used over Parquet files).




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)