Bug 1248489
| Summary: | DB2 - error getting substring if "from position" is out of range | ||
|---|---|---|---|
| Product: | [JBoss] JBoss Data Virtualization 6 | Reporter: | Juraj Duráni <jdurani> |
| Component: | Teiid | Assignee: | Van Halbert <vhalbert> |
| Status: | CLOSED UPSTREAM | QA Contact: | Juraj Duráni <jdurani> |
| Severity: | unspecified | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 6.2.0 | CC: | atangrin, dlesage, drieden, vhalbert |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| 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 17:55:15 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
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 Steven Hawkins <shawkins> updated the status of jira TEIID-3600 to Resolved 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. Steven Hawkins <shawkins> updated the status of jira TEIID-3600 to Resolved |
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: