Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.0.0
-
None
Description
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