drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yash Sharma (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-573) SUBSTR/SUBSTRING functions of a number requires explicit cast
Date Sat, 03 May 2014 18:24:14 GMT

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

Yash Sharma commented on DRILL-573:
-----------------------------------

Another error for similar scenario:

0: jdbc:drill:zk=local> SELECT employee_id, first_name, substring(111111, 1, 3) FROM cp.`employee.json`
where employee_id> 5 limit 10; 
May 03, 2014 11:46:22 PM org.eigenbase.sql.validate.SqlValidatorException <init>
SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Cannot apply 'SUBSTRING' to arguments
of type 'SUBSTRING(<INTEGER> FROM <INTEGER> FOR <INTEGER>)'. Supported form(s):
'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
May 03, 2014 11:46:22 PM org.eigenbase.util.EigenbaseException <init>
SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column 33 to line 1, column
55
Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id:
"9462b7c9-629a-4731-9e7f-a5d4184c9c04"
endpoint {
  address: "L-yash-tech"
  user_port: 31010
  control_port: 31011
  data_port: 31012
}
error_type: 0
message: "Failure while parsing sql. < ValidationException:[ org.eigenbase.util.EigenbaseContextException:
From line 1, column 33 to line 1, column 55 ] < EigenbaseContextException:[ From line 1,
column 33 to line 1, column 55 ] < SqlValidatorException:[ Cannot apply \'SUBSTRING\' to
arguments of type \'SUBSTRING(<INTEGER> FROM <INTEGER> FOR <INTEGER>)\'.
Supported form(s): \'SUBSTRING(<CHAR> FROM <INTEGER>)\'\n\'SUBSTRING(<CHAR>
FROM <INTEGER> FOR <INTEGER>)\'\n\'SUBSTRING(<VARCHAR> FROM <INTEGER>)\'\n\'SUBSTRING(<VARCHAR>
FROM <INTEGER> FOR <INTEGER>)\'\n\'SUBSTRING(<BINARY> FROM <INTEGER>)\'\n\'SUBSTRING(<BINARY>
FROM <INTEGER> FOR <INTEGER>)\'\n\'SUBSTRING(<VARBINARY> FROM <INTEGER>)\'\n\'SUBSTRING(<VARBINARY>
FROM <INTEGER> FOR <INTEGER>)\' ]"
]
Error: exception while executing query (state=,code=0)


> 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)

Mime
View raw message