Description of problem: 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 Steps to Reproduce: Query: e.g. SELECT substring(timestampvalue, 40, 0) FROM smalla WHERE intkey = 1; Additional information: 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? Document URL: Section Number and Name: Describe the issue: Suggestions for improvement: Additional information:
Steven Hawkins <shawkins> updated the status of jira TEIID-3600 to Resolved
This has bee resolved upstream on the 8.12 Teiid branch.
Steven Hawkins <shawkins> updated the status of jira TEIID-3600 to Closed
The issue is still present. Source-specific command has changed, but exception is thrown anyway. Source-specific command: SELECT substr(varchar(g_0.TIMESTAMPVALUE), CASE WHEN 40 > 0 THEN 0 ELSE 40 END, CASE WHEN 0 > (length(varchar(g_0.TIMESTAMPVALUE)) - (CASE WHEN 40 > 0 THEN 0 ELSE 40 END - 1)) THEN (length(varchar(g_0.TIMESTAMPVALUE)) - (CASE WHEN 40 > 0 THEN 0 ELSE 40 END - 1)) ELSE 0 END) FROM SMALLA AS g_0 WHERE g_0.INTKEY = 1 Result: Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-138, SQLSTATE=22011, SQLERRMC=null, DRIVER=4.12.55
Van Halbert <vhalbert> updated the status of jira TEIID-3600 to Reopened
The query is still failing. I can see condition in the source-specific command like follows: CASE WHEN 40 > length(varchar(g_0.TIMESTAMPVALUE)) THEN (length(varchar(g_0.TIMESTAMPVALUE)) + 1) 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. Source-specific command: SELECT substr(varchar(g_0.TIMESTAMPVALUE), CASE WHEN 40 > length(varchar(g_0.TIMESTAMPVALUE)) THEN (length(varchar(g_0.TIMESTAMPVALUE)) + 1) ELSE 40 END, CASE WHEN 0 > (length(varchar(g_0.TIMESTAMPVALUE)) - (CASE WHEN 40 > length(varchar(g_0.TIMESTAMPVALUE)) THEN (length(varchar(g_0.TIMESTAMPVALUE)) + 1) ELSE 40 END - 1)) THEN (length(varchar(g_0.TIMESTAMPVALUE)) - (CASE WHEN 40 > length(varchar(g_0.TIMESTAMPVALUE)) THEN (length(varchar(g_0.TIMESTAMPVALUE)) + 1) ELSE 40 END - 1)) ELSE 0 END) FROM SMALLA AS g_0 WHERE integer(g_0.INTKEY) = 1
Juraj Duráni <jdurani> updated the status of jira TEIID-3600 to Reopened
I am sorry. Setting to from NEW to ASSIGNED.