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

[Johnathon Lee (JIRA)]

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

Johnathon Lee updated TEIID-4649:
---------------------------------
    Fix Version/s: 8.12.13.6_4


> SybaseIQ translator: different order of arguments in CONVERT function
> ---------------------------------------------------------------------
>
>                 Key: TEIID-4649
>                 URL: https://issues.jboss.org/browse/TEIID-4649
>             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.13.6_4
>
>
> Teiid can't push directly CONVERT function via SybaseIQ translator. Sybase/SAP IQ has different order of arguments than Teiid has. See [SAP IQ CONVERT docs|http://help.sap.com/saphelp_iq1611_iqrefbb/helpdata/en/a5/3f6efb84f21015af0e8594ce5cd68e/content.htm]
> Important to mention is the fact, that CONVERT function is apart from a direct call in user's query also incorporated into resulting source queries by the translator. That will cause issues in lots of queries that need for example implicit conversion to be done.
> For query:
> {code:sql}
> SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.CharValue >= '0' AND BQT1.SmallA.CharValue < '1' ORDER BY IntKey
> {code}
> this gets pushed:
> {code:sql}
> [SELECT g_0."intkey" AS c_0 FROM "bqt-server"."dvqe"."SmallA" AS g_0 WHERE convert(g_0."charvalue", string) >= '0' AND convert(g_0."charvalue", string) < '1' ORDER BY c_0]
> {code}
> Error in log:
> {code}
> 08:05:32,921 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue13) Connector worker process failed for atomic-request=PYp5BTTPF3pK.6.0.0: org.teiid.translator.jdbc.JDBCExecutionException: 102 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 100 g_0."intkey" AS c_0 FROM "bqt-server"."dvqe"."SmallA" AS g_0 WHERE convert(g_0."charvalue", string) >= '0' AND convert(g_0."charvalue", string) < '1' ORDER BY c_0]
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:366)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_71]
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_71]
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_71]
> 	at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_71]
> 	at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> 	at com.sun.proxy.$Proxy80.execute(Unknown Source)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> 	at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> 	at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_71]
> 	at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
> 	at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> 	at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_71]
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_71]
> 	at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_71]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: SQL Anywhere Error -131: Syntax error near 'g_0.charvalue' on line 1
> 	at com.sybase.jdbc4.tds.Tds.a(Unknown Source)
> 	at com.sybase.jdbc4.tds.Tds.nextResult(Unknown Source)
> 	at com.sybase.jdbc4.tds.Tds.getResultSetResult(Unknown Source)
> 	at com.sybase.jdbc4.tds.TdsCursor.open(Unknown Source)
> 	at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(Unknown Source)
> 	at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(Unknown Source)
> 	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.8.6_3-redhat-1.jar:8.12.8.6_3-redhat-1]
> 	... 18 more
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)