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

[Steven Hawkins (JIRA)]

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

Steven Hawkins resolved TEIID-4651.
-----------------------------------
    Fix Version/s: 9.1.2
       Resolution: Done


Switched the order of the first two parameters.  There is also a limitation documented that their locate function only support a search string of under 256 characters, which may be a later issue.

> 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
>
>
> 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)