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

Thursday, 9 June 2016

     [
https://issues.jboss.org/browse/TEIID-3600?page=com.atlassian.jira.plugin...
]

Juraj Duráni reopened TEIID-3600:
---------------------------------

The query is still failing. I can see condition in the source-specific command like
follows:
{code:sql}
CASE WHEN 40 > length(varchar(g_0.TIMESTAMPVALUE)) THEN
(length(varchar(g_0.TIMESTAMPVALUE)) + 1)
{code}

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:
{code:sql}
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
{code}

...
 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: 9.0, 8.12.5, 8.13.5

 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.4.11#64026)

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

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