[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=13095970#comment-13095970 ] 

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

I think the resolution here will be to account for the db2 exceptional case, but to otherwise document that there can be difference between empty and null results from the substring function.  As needed by customer issues we can address discrepancies.  The variety and complexity of the case expressions needed otherwise would be daunting:

Sybase - case when str is null || length is null then null when length = 0 then '' else substr(str, begin, length) end
SQL Server would be slightly different
etc.

> 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: 8.12
>
>
> 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.3.15#6346)