[ https://issues.apache.org/jira/browse/DRILL-2747?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jinfeng Ni updated DRILL-2747: ------------------------------ Assignee: Mehant Baid (was: Jinfeng Ni) > Implicit cast in filters fails if enclosed literal has leading or trailing > spaces > --------------------------------------------------------------------------------- > > Key: DRILL-2747 > URL: https://issues.apache.org/jira/browse/DRILL-2747 > Project: Apache Drill > Issue Type: Bug > Components: SQL Parser > Reporter: Abhishek Girish > Assignee: Mehant Baid > Fix For: 1.2.0 > > > Implicit cast in filters do not return results if literals contain leading or > trailing spaces. > *Drill:* > {code:sql} > > select d_date_sk, d_day_name from date_dim where d_date is not null and > > d_date between '1900-01-10' and ' 1900-01-20' limit 1 ; > +------------+------------+ > | d_date_sk | d_day_name | > +------------+------------+ > +------------+------------+ > No rows selected (0.221 seconds) > {code} > Prefixing the literal with "date" happens to solve the issue: > {code:sql} > > select d_date_sk, d_day_name from date_dim where d_date is not null and > > d_date between date '1900-01-10' and date ' 1900-01-20' limit 1 ; > +------------+------------+ > | d_date_sk | d_day_name | > +------------+------------+ > | 2415030 | Tuesday | > +------------+------------+ > 1 row selected (0.128 seconds) > {code} > *Postgres:* > {code:sql} > # select d_date_sk, d_day_name from date_dim where d_date is not null and > d_date between '1900-01-10' and ' 1900-01-20' limit 1 ; > d_date_sk | d_day_name > -----------+------------ > 2415030 | Tuesday > (1 row) > {code} > Update: > In case of String literals with numeric value, leading or trailing spaces > causes the query to fail with NumberFormatException: > *Drill:* > {code:sql} > > select d_date_sk, d_day_name from date_dim where d_week_seq in ('1','2','3 > > ') order by d_week_seq limit 3; > Query failed: RemoteRpcException: Failure while running fragment., 3 [ > a06d8711-5e70-46a8-b4f7-100e8380f610 on abhi6.qa.lab:31010 ] > [ a06d8711-5e70-46a8-b4f7-100e8380f610 on abhi6.qa.lab:31010 ] > Error: exception while executing query: Failure while executing query. > (state=,code=0) > {code} > *Postgres:* > {code:sql} > # select d_date_sk, d_day_name from date_dim where d_week_seq in ('1','2','3 > ') order by d_week_seq limit 3; > d_date_sk | d_day_name > -----------+------------ > 2415023 | Tuesday > 2415024 | Wednesday > 2415022 | Monday > (3 rows) > {code:sql} -- This message was sent by Atlassian JIRA (v6.3.4#6332)