[teiid-issues] [JBoss JIRA] (TEIID-5398) Sybase translator error caused by change in ASCII function pushdown

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-5398:
---------------------------------------

Based upon testing against the sybase 16 docker image and seeing similar questions online - it does not appear that uscalar behaves as expected.  The change here will be to make the unicode function alias specific to sql server and remove the alias from sybase.

That leaves things with the ascii function in a fairly inconsistent state.  Teiid/JDV has always returned the USC2/UTF16 value for the first char from the ASCII function (similar to H2).  The previous change was attempting to match the Teiid behavior by making the source functions respect unicode strings.  However for sources like mysql and sybase that functionality seems unavailable.  The most consistency would be to change the Teiid behavior of ascii and char functions to ascii characters only and corresponding pushdown changes - but that could break existing usage.  So for now we should probably just make things as consistent as possible and document that not all sources support unicode with the ascii and char functions.

> Sybase translator error caused by change in ASCII function pushdown
> -------------------------------------------------------------------
>
>                 Key: TEIID-5398
>                 URL: https://issues.jboss.org/browse/TEIID-5398
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.12.14.6_4
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 11.0
>
>
> There's a regression caused by TEIID-5313 change:
> {code:java}
> registerFunctionModifier(SourceSystemFunctions.ASCII, new AliasModifier("unicode")); //$NON-NLS-1$
> {code}
> Following query:
> {code:sql}
> SELECT INTKEY, ASCII(INTKEY) FROM BQT1.SmallA
> {code}
> being resolved to following src command:
> {code:sql}
> SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> results in following error:
> {code:title=server.log}
> 12:42:04,102 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue18) Connector worker process failed for atomic-request=reZs6o4Ybhhc.8.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> 	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> 	at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> 	at com.sun.proxy.$Proxy79.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:266) [rt.jar:1.8.0_151]
> 	at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 	at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> 	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:1149) [rt.jar:1.8.0_151]
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> 	at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: com.sybase.jdbc4.jdbc.SybSQLException: Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> 	at com.sybase.jdbc4.tds.Tds.processEed(Tds.java:4003)
> 	at com.sybase.jdbc4.tds.Tds.nextResult(Tds.java:3093)
> 	at com.sybase.jdbc4.jdbc.ResultGetter.nextResult(ResultGetter.java:78)
> 	at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:289)
> 	at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:271)
> 	at com.sybase.jdbc4.jdbc.SybStatement.queryLoop(SybStatement.java:2408)
> 	at com.sybase.jdbc4.jdbc.SybStatement.executeQuery(SybStatement.java:2394)
> 	at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:257)
> 	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.14.6_4-redhat-64-2.jar:8.12.14.6_4-redhat-64-2]
> 	... 18 more
> 12:42:04,118 WARN  [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue19) TEIID30020 Processing exception for request reZs6o4Ybhhc.8 'TEIID30504 Source: 14216 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey, unicode(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0]'. Originally TeiidProcessingException 'Function 'unicode' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).
> ' Tds.java:4003. Enable more detailed logging to see the entire stacktrace.
> {code}
> SRC Command in previous version:
> {code:sql}
> SELECT g_0.intkey, ascii(cast(g_0.intkey AS varchar(4000))) FROM smalla g_0
> {code}
> NOTE: the issue is not caused by casting int to varchar, happens also when calling ASCII function on string directly.



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