[jira] [Closed] (DRILL-573) SUBSTR/SUBSTRING functions of a number requires explicit cast

["Krystal (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DRILL-573?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Krystal closed DRILL-573.
-------------------------

Bug was verified.

> SUBSTR/SUBSTRING functions of a number requires explicit cast
> -------------------------------------------------------------
>
>                 Key: DRILL-573
>                 URL: https://issues.apache.org/jira/browse/DRILL-573
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>    Affects Versions: 1.0.0
>            Reporter: Krystal
>             Fix For: 0.4.0
>
>
> Unlike Oracle, drill requires explicit cast a number to string when used in a substr or substring function.  
> Here is the test data:
> select * from voter where voter_id=11;
>  voter_id | name | age | registration | contributions | voterzone |     create_time     
> --------+------+-----+--------------+---------------+-----------+---------------------
>      11 |      |  58 | republican   |        578.08 |     16161 | 2014-08-27 06:35:33
> In drill, an explicit cast is required:
> 0: jdbc:drill:schema=dfs> select substr(cast(contributions as varchar(8)), 3, 5) from voter where voter_id=11;
> +------------+
> |   EXPR$0   |
> +------------+
> | 8.08       |
> +------------+
> If explicit cast is not given, drill returns result in some form of binary data:
> 0: jdbc:drill:schema=dfs> select substr(contributions, 3, 5) from voter where voter_id=11;
> +------------+
> |   EXPR$0   |
> +------------+
> | 㠮〸         |
> +------------+
> In Oracle, the cast is implicit:
> SQL> select substr(contributions, 3, 5) from voter where voter_id=11;
> SUBST
> -----
> 8.08



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)