[JBoss JIRA] (TEIID-3474) Inconsistent results of RIGHT function for different datasources

Wednesday, 13 May 2015

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

Steven Hawkins commented on TEIID-3474:
---------------------------------------

Postgres - this is a bug in our compensation.  We assume that the same logic that
basically works for oracle, will work for postgresql.  This is not true as pg doesn't
like negative indexes in the substr function.  We'll also update the logic so that
when the source is version 9.1 and above, we'll use the right function directly.

Oracle - the negative indexing is apparently allowed to go beyond the first character, so
we get null/empty results for strings that as smaller than the count.  This will be
addressed as well.

DB2 - this is a bug in DB2.  Left with a char cast works just fine, as does right without
a cast, but using right(char(val), count) just produces the wrong result. So we'll
have to workaround that on our side.

...
 Inconsistent results of RIGHT function for different datasources
 ----------------------------------------------------------------

                 Key: TEIID-3474
                 URL: https://issues.jboss.org/browse/TEIID-3474
             Project: Teiid
          Issue Type: Bug
    Affects Versions: 8.7.1.6_2
         Environment: OS: Fedora 20
 arch: x86_64
 java: oracle 1.8
            Reporter: Juraj Duráni
            Assignee: Steven Hawkins

 The RIGHT function returns different results for different datasources. Some of those
results are inconsistent with definition of the RIGHT(x,y) function.
 =======
 +*Query: SELECT intkey, RIGHT(intkey, 1) FROM table ORDER BY intkey*+
 *Teradata, SQLServer, Sybase, Ingres, MySQL, Oracle (this result is OK):*
 0	0
 1	1
 2	2
 ...
 10	0
 11	1
 ...
 100	0
 101	1
 *Postgres:*
 0	0
 1	1
 2	2
 ...
 10	10
 11	11
 ...
 100	100
 101	101
 *DB2:*
 0	" "
 1	" "
 2	" "
 ...
 10	" "
 11	" "
 ...
 100	" "
 101	" "
 =====
 +*Query:SELECT intkey, RIGHT(intkey, 2) FROM table ORDER BY intkey*+ 
 *SQLServer, Sybase, Ingress, MySQL, Teradata (this result is OK):*
 0	0
 1	1
 ...
 10	10
 11	11
 ...
 100	00
 101	01
 *Oracle:*
 0	<null>
 1	<null>
 2	<null>
 ...
 10	10
 11	11
 ...
 100	00
 101	01
 *Postgres:*
 0	0
 1	1
 2	2
 ...
 10	10
 11	11
 ...
 100	100
 101	101
 *DB2:*
 0	"  "
 1	"  "
 2	"  "
 ...
 10	"  "
 11	"  "
 ...
 100	"  "
 101	"  " 

--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

2026

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009