Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Not A Problem
-
1.14.0
-
None
Description
(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.
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)
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).