[jira] [Commented] (DRILL-4897) NumberFormatException in Drill SQL while casting to BIGINT when its actually a number

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

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

Karthikeyan Manivannan commented on DRILL-4897:
-----------------------------------------------

This works as expected. 

When Codegen is being done for the IfExpression corresponding to the Case, then the Codegen realizes that the type of IfExpression (Int) and ElseExpression(Varchar) are different. ResolverTypePrecedence.precedenceMap specifies the precedence order for type conversions and it clearly indicates that Varchar -> Int is a valid conversion but not the other way round. So as part of the If-Else type resolution, Codegen consults the precedenceMap and casts the Else-type(Varchar) to the If-type (Int).

The user can avoid this error by explicitly casting the ElseExpression to be BigInt.

_SELECT CAST(case isnumeric(columns[0]) WHEN 0 THEN 0 ELSE CAST(columns[0] as BigInt) END AS BIGINT) from ..._


> 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
>             Fix For: 1.15.0
>
>
> 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)