[TEIID-4649] SybaseIQ translator: different order of arguments in CONVERT function Created: 14/Dec/16  Updated: 24/Apr/18  Resolved: 03/Jan/17

Status: Resolved
Project: Teiid
Component/s: JDBC Connector
Affects Version/s: 8.12.8.6_3
Fix Version/s: 9.1.2, 9.2, 8.12.13.6_4

Type: Bug Priority: Critical
Reporter: Jan Stastny Assignee: Steven Hawkins
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Dependency
Related

 Description   

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

SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.CharValue >= '0' AND BQT1.SmallA.CharValue < '1' ORDER BY IntKey

this gets pushed:

[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]

Error in log:

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



 Comments   
Comment by Steven Hawkins [ 03/Jan/17 ]

There were two issues here. The first is that the resolver was inserting unnecessary conversions for the char/string comparison. The other was that the sybase iq execution factory did not register the convert modifier. That was corrected and the conversion logic was expanded based upon the sybase translator and the sybase iq docs.

Generated at Fri Dec 14 08:51:46 EST 2018 using Jira 7.12.1#712002-sha1:609a50578ba6bc73dbf8b05dddd7c04a04b6807c.