[
https://issues.apache.org/jira/browse/DRILL-6859?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16690528#comment-16690528
]
Volodymyr Vysotskyi commented on DRILL-6859:
--------------------------------------------
Yes, it comes from the Calcite, but Calcite strictly follows SQL specification
for this case.
A string literal from the query {{'2000-4-01'}} which correspond to the date
contains month value 4, but according to the SQL spec. 6.1 <data type> valid
values for the month is [01, 12].
So either it should be modified to {{'2000-04-01'}} or it should be wrapped
into the function which allows specifying date patterns ({{[TO_DATE (expression
[, 'format'])|https://drill.apache.org/docs/data-type-conversion/#to_date]}},
{{SQL_TO_DATE (expression [, 'format'])}}, etc.)
> 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
> 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)
