[teiid-issues] [JBoss JIRA] (TEIID-3600) DB2 - error getting substring if "from position" is out of range

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3600?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13250306#comment-13250306 ] 

Steven Hawkins commented on TEIID-3600:
---------------------------------------

> I believe that this is wrong as (length(varchar(g_0.TIMESTAMPVALUE)) + 1) is obviously greater than length(varchar(g_0.TIMESTAMPVALUE)) which actually causes the exception

With string values the above "worked" to set the from index beyond the end of the string - but I can see that the issue there was that the strings were smaller than the datatype length.  Adjusting that index will mean that the length calculation will need to change as it would also have to be 0 in that case otherwise we'll still get the last character in the result.

> DB2 - error getting substring if "from position" is out of range
> ----------------------------------------------------------------
>
>                 Key: TEIID-3600
>                 URL: https://issues.jboss.org/browse/TEIID-3600
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: Misc. Connectors
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>             Fix For: 9.1
>
>
> If second argument of a SUBSTRING(x,y,z) function is out of range of first argument an exception is thrown [1]. Source-specific command [2].
> [1]
> Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-138, SQLSTATE=22011, SQLERRMC=null, DRIVER=4.12.55
> 	at com.ibm.db2.jcc.am.hd.a(hd.java:668)
> 	at com.ibm.db2.jcc.am.hd.a(hd.java:60)
> ...
> [2]
> SELECT substr(varchar(g_0.TimestampValue), 40, CASE WHEN 0 > (length(varchar(g_0.TimestampValue)) - (40 - 1)) THEN (length(varchar(g_0.TimestampValue)) - (40 - 1)) ELSE 0 END) FROM SmallA AS g_0 WHERE g_0.IntKey = 1



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)