[teiid-issues] [JBoss JIRA] (TEIID-4651) SybaseIQ translator: different order of arguments in LOCATE function

[Van Halbert (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4651?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Van Halbert updated TEIID-4651:
-------------------------------
    Git Pull Request: https://github.com/teiid/teiid/pull/937


PR for backport to 8.12.x-6.4.

> SybaseIQ translator: different order of arguments in LOCATE function
> --------------------------------------------------------------------
>
>                 Key: TEIID-4651
>                 URL: https://issues.jboss.org/browse/TEIID-4651
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.12.8.6_3
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 9.1.2, 9.2, 8.12.x-6.4
>
>
> Teiid's and SAP IQ's LOCATE function differs in order of arguments. Translator is pushing the LOCATE function as is into the source, which results in undesirable results. This is critical issue, as the queries seemingly work, but are not producing correct results.
> See [SAP IQ LOCATE function docs|http://help.sap.com/saphelp_iq1611_iqrefbb/helpdata/en/a5/5fae8484f2101591b6b7d46dca7cc4/content.htm]
> Query:
> {code:sql}
> SELECT INTKEY, STRINGNUM, LOCATE(1,STRINGNUM) FROM BQT1.SmallA ORDER BY intkey
> {code}
> being pushed as:
> {code:sql}
> [SELECT g_0."intkey" AS c_0, g_0."stringnum" AS c_1, locate('1', g_0."stringnum") AS c_2 FROM "bqt-server"."dvqe"."SmallA" AS g_0 ORDER BY c_0]
> {code}
> Results in:
> || intkey ||	stringnum	|| expr3 ||
> |0 | -24 | 0| 
> |1 | <null> | <null>| 
> |2 | -22 | 0| 
> |3 | -21 | 0| 
> |4 | -20 | 0| 
> |5 | -19 | 0| 
> |6 | -18 | 0| 
> |7 | -17 | 0| 
> |8 | -16 | 0| 
> |9 | -15 | 0| 
> |10 | -14 | 0| 
> |11 | -13 | 0| 
> |12 | -12 | 0| 
> |13 | -11 | 0| 
> |14 | -10 | 0| 
> |15 | -9 | 0| 
> |16 | -8 | 0| 
> |17 | <null> | <null>| 
> |18 | -6 | 0| 
> |19 | -5 | 0| 
> |20 | -4 | 0| 
> |21 | -3 | 0| 
> |22 | -2 | 0| 
> |23 | -1 | 0| 
> |24 | 0 | 0| 
> |25 | 1 | 1| 
> |26 | 2 | 0| 
> |27 | 3 | 0| 
> |28 | 4 | 0| 
> |29 | 5 | 0| 
> |30 | 6 | 0| 
> |31 | 7 | 0| 
> |32 | 8 | 0| 
> |33 | <null> | <null>| 
> |34 | 10 | 0| 
> |35 | 11 | 0| 
> |36 | 12 | 0| 
> |37 | 13 | 0| 
> |38 | 14 | 0| 
> |39 | 15 | 0| 
> |40 | 16 | 0| 
> |41 | 17 | 0| 
> |42 | 18 | 0| 
> |43 | 19 | 0| 
> |44 | 20 | 0| 
> |45 | 21 | 0| 
> |46 | 22 | 0| 
> |47 | 23 | 0| 
> |48 | 24 | 0| 
> |49 | <null> | <null> |



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)