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

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

Krystal created DRILL-573:
-----------------------------

             Summary: 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
    Affects Versions: 1.0.0
            Reporter: Krystal


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.2#6252)