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

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3474?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13067650#comment-13067650 ] 

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)