[teiid-issues] [JBoss JIRA] (TEIID-4631) IN clause causes an error when used within a function in SELECT clause
Steven Hawkins (JIRA)
issues at jboss.org
Thu Dec 8 18:00:00 EST 2016
[ https://issues.jboss.org/browse/TEIID-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins updated TEIID-4631:
----------------------------------
Component/s: JDBC Connector
Query Engine
The pushdown sql is generally correct, but the target databases aren't correctly handling a nested in predicate as a boolean expression. This may be a hard limitation of their parsers. I'll see if there is a workaround.
> IN clause causes an error when used within a function in SELECT clause
> ----------------------------------------------------------------------
>
> Key: TEIID-4631
> URL: https://issues.jboss.org/browse/TEIID-4631
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector, Query Engine
> Reporter: Salvatore R
> Assignee: Steven Hawkins
>
> The following query is wrongly rewritten when it is pushed down to MS SQL Server and Oracle but it works correctly in other databases like MySQL or PostgreSQL:
> {code:sql}
> select COALESCE(t.a in (8,9), FALSE)
> from ms.test_a t;
> {code}
> This is the exception thrown in MS SQL Server:
> {code}
> 19:05:41,253 WARN [org.teiid.CONNECTOR] (Worker8_QueryProcessorQueue52) amQzqFjFfV09 Connector worker process failed for atomic-request=amQzqFjFfV09.15.0.11: org.teiid.translator.jdbc.JDBCExecutionException: 102 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT isnull(g_0."a" IN (8, 9), 0) AS c_0 FROM "test"."dbo"."test_a" g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy29.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)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> 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)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
> and this is the exception thrown by Oracle:
> {code}
> 19:09:15,307 WARN [org.teiid.CONNECTOR] (Worker10_QueryProcessorQueue58) amQzqFjFfV09 Connector worker process failed for atomic-request=amQzqFjFfV09.17.0.13: org.teiid.translator.jdbc.JDBCExecutionException: 909 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT nvl(g_0."A" IN (8, 9), 0) FROM "TEST"."TEST_A" g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy29.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)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> 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)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
More information about the teiid-issues
mailing list