[
https://issues.apache.org/jira/browse/DRILL-6859?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16696480#comment-16696480
]
Volodymyr Vysotskyi commented on DRILL-6859:
--------------------------------------------
[~ben-zvi], can we resolve this Jira?
> 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
> Assignee: Volodymyr Vysotskyi
> Priority: Major
> 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)
