This service will be undergoing maintenance at 00:00 UTC, 2017-10-23 It is expected to last about 30 minutes
Bug 1248489 - DB2 - error getting substring if "from position" is out of range
DB2 - error getting substring if "from position" is out of range
Status: CLOSED UPSTREAM
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Teiid (Show other bugs)
6.2.0
Unspecified Unspecified
unspecified Severity unspecified
: ---
: ---
Assigned To: Van Halbert
Juraj Duráni
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2015-07-30 08:00 EDT by Juraj Duráni
Modified: 2017-06-12 11:31 EDT (History)
4 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
When using DB2, if the second argument of a SUBSTRING(x,y,z) function is out of the range of the first argument, users will encounter an exception.
Story Points: ---
Clone Of:
Environment:
Last Closed: 2017-02-22 12:55:15 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)


External Trackers
Tracker ID Priority Status Summary Last Updated
JBoss Issue Tracker JDV7-63 Major New DB2 - error getting substring if "from position" is out of range 2017-06-12 11:29 EDT
JBoss Issue Tracker TEIID-3600 Major Resolved DB2 - error getting substring if "from position" is out of range 2017-06-12 11:29 EDT

  None (edit)
Description Juraj Duráni 2015-07-30 08:00:10 EDT
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:
Comment 1 JBoss JIRA Server 2015-08-06 09:45:08 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3600 to Resolved
Comment 2 Van Halbert 2015-09-03 14:16:37 EDT
This has bee resolved upstream on the 8.12 Teiid branch.
Comment 3 JBoss JIRA Server 2016-01-26 13:00:55 EST
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3600 to Closed
Comment 4 Juraj Duráni 2016-05-09 07:35:57 EDT
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
Comment 5 JBoss JIRA Server 2016-05-24 08:26:05 EDT
Van Halbert <vhalbert@jboss.org> updated the status of jira TEIID-3600 to Reopened
Comment 6 JBoss JIRA Server 2016-05-24 09:16:54 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3600 to Resolved
Comment 7 Juraj Duráni 2016-06-09 09:09:50 EDT
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
Comment 8 JBoss JIRA Server 2016-06-09 09:11:15 EDT
Juraj Duráni <jdurani@redhat.com> updated the status of jira TEIID-3600 to Reopened
Comment 9 Juraj Duráni 2016-06-09 09:30:41 EDT
I am sorry. Setting to from NEW to ASSIGNED.
Comment 10 JBoss JIRA Server 2016-09-19 14:35:40 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3600 to Resolved

Note You need to log in before you can comment on or make changes to this bug.