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

[Juraj Duráni (JIRA)]

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

Juraj Duráni commented on TEIID-3600:
-------------------------------------

There is also some inconsistency in result of substring(x,y,z) function of different translators:
*1. second argument out of range* 
Teiid returns NULL by default, but almost every jdbc translator (except oracle and sybase - NULL) returns an empty string (""). I expect that this depends on driver. Excel, solr and salesforce return NULL.

*2. third argument is zero* 
In case of teiid, excel, solr and salesforce the result depends on second argument. If it is out of range, then the result is NULL, otherwise an empty string is returned. JDBC translators return an empty string, except of oracle and sybase - NULL.

Don't you want to consider to unify it?

> 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: Bug
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> 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)