[jira] [Commented] (DRILL-2747) Implicit cast in filters fails if enclosed literal has leading or trailing spaces

["Daniel Barclay (Drill) (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-2747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14492654#comment-14492654 ] 

Daniel Barclay (Drill) commented on DRILL-2747:
-----------------------------------------------

> Prefixing the literal with "date" ...

Note that that's changing from a string literal to a DATE literal.

DATE and TIMESTAMP literals have specific rules about trimming spaces from the quoted part.  

The example without "DATE" is presumably using implicit casting, from CHAR to DATE, which has a different set of rules and code path.

Presumably, the problem is that we don't define casting from character string to date to process the string the same way as DATE literals process their quoted strings. 





> 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: Query Planning & Optimization
>            Reporter: Abhishek Girish
>            Assignee: Jinfeng Ni
>
> 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)