[
https://issues.apache.org/jira/browse/DRILL-4897?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16402463#comment-16402463
]
Karthikeyan Manivannan commented on DRILL-4897:
-----------------------------------------------
This seems to be happening because the "WHEN 0 THEN 0" in the query. I think
the "THEN 0" causes PROJECT to assume that the result column is INT instead of
BIGINT and the query throws the exception when a number larger than what INT
can hold is processed. The query runs fine if it is changed to "...WHEN 0 THEN
2147483648..." but fails when it is changed to "...WHEN 0 THEN 2147483647..."
0: jdbc:drill:zk=local> select CAST(case isnumeric(columns[0]) WHEN 0 THEN
2147483647 ELSE columns[0] END AS BIGINT) from
dfs.`/Users/karthik/work/bugs/DRILL-4897/pw2.csv`;
Error: SYSTEM ERROR: NumberFormatException: 2147483648
Fragment 0:0
[Error Id: d29ec48e-e659-41b4-a722-9c546ef8c9c9 on 172.30.8.179:31010]
(state=,code=0)
0: jdbc:drill:zk=local> select CAST(case isnumeric(columns[0]) WHEN 0 THEN
2147483648 ELSE columns[0] END AS BIGINT) from
dfs.`/Users/karthik/work/bugs/DRILL-4897/pw2.csv`;
+-------------+
| EXPR$0 |
+-------------+
| 1 |
| 2 |
...
...
| 2147483648 |
| 4294967296 |
+-------------+
The planner seems to be doing the same thing in both cases:
Failed Case
< Project(EXPR$0=[CAST(CASE(=(ISNUMERIC(ITEM($0, 0)), 0), 2147483647, ITEM($0,
0))):BIGINT]) : rowType = RecordType(BIGINT EXPR$0): rowcount = 2.0, cumulative
cost = \{4.0 rows, 10.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
---
Succesfull
> Project(EXPR$0=[CAST(CASE(=(ISNUMERIC(ITEM($0, 0)), 0), 2147483648, ITEM($0,
> 0))):BIGINT]) : rowType = RecordType(BIGINT EXPR$0): rowcount = 2.0,
> cumulative cost = \{4.0 rows, 10.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
So, I guess the problem is in the way the expression is handled in PROJECT. I
will investigate this further.
> NumberFormatException in Drill SQL while casting to BIGINT when its actually
> a number
> -------------------------------------------------------------------------------------
>
> Key: DRILL-4897
> URL: https://issues.apache.org/jira/browse/DRILL-4897
> Project: Apache Drill
> Issue Type: Bug
> Components: Functions - Drill
> Reporter: Srihari Karanth
> Assignee: Karthikeyan Manivannan
> Priority: Blocker
>
> In the following SQL, drill cribs when trying to convert a number which is in
> varchar
> select cast (case IsNumeric(Delta_Radio_Delay)
> when 0 then 0 else Delta_Radio_Delay end as BIGINT)
> from datasource.`./sometable`
> where Delta_Radio_Delay='4294967294';
> BIGINT should be able to take very large number. I dont understand how it
> throws the below error:
> 0: jdbc:drill:> select cast (case IsNumeric(Delta_Radio_Delay)
> when 0 then 0 else Delta_Radio_Delay end as BIGINT)
> from datasource.`./sometable`
> where Delta_Radio_Delay='4294967294';
> Error: SYSTEM ERROR: NumberFormatException: 4294967294
> Fragment 1:29
> [Error Id: a63bb113-271f-4d8b-8194-2c9728543200 on cluster-3:31010]
> (state=,code=0)
> How can i modify SQL to fix this?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
