You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Yash Sharma (JIRA)" <ji...@apache.org> on 2014/05/03 20:24:14 UTC
[jira] [Commented] (DRILL-573) SUBSTR/SUBSTRING functions of a
number requires explicit cast
[ 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)