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)