[
https://issues.apache.org/jira/browse/DRILL-2747?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jacques Nadeau updated DRILL-2747:
----------------------------------
Component/s: (was: Query Planning & Optimization)
SQL Parser
Fix Version/s: 1.2.0
> 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: Jinfeng Ni
> 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)
