[teiid-issues] [JBoss JIRA] (TEIID-4078) Oracle translator - query fails - ORA-00979: not a GROUP BY expression

[Steven Hawkins (JIRA)]

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

Steven Hawkins resolved TEIID-4078.
-----------------------------------
    Resolution: Done


Removed the check for nested casts so that the expression is preserved.

> Oracle translator - query fails - ORA-00979: not a GROUP BY expression
> ----------------------------------------------------------------------
>
>                 Key: TEIID-4078
>                 URL: https://issues.jboss.org/browse/TEIID-4078
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>             Fix For: 9.0, 8.12.5
>
>
> Query:
> {code:sql}
> SELECT A.INTKEY, A.BOOLEANVALUE FROM BQT1.SMALLA AS A GROUP BY A.INTKEY, A.BOOLEANVALUE HAVING CONVERT(A.BOOLEANVALUE, STRING) >= (SELECT MIN(CONVERT(B.BOOLEANVALUE, STRING)) FROM BQT1.SMALLA AS B WHERE A.INTKEY = B.INTKEY)
> {code}
> Source specific command:
> {code:sql}
> SELECT trunc(g_0."INTKEY"), CASE WHEN g_0."BOOLEANVALUE" = 0 THEN 0 WHEN g_0."BOOLEANVALUE" IS NOT NULL THEN 1 END FROM "DV"."SMALLA" g_0 GROUP BY trunc(g_0."INTKEY"), CASE WHEN g_0."BOOLEANVALUE" = 0 THEN 0 WHEN g_0."BOOLEANVALUE" IS NOT NULL THEN 1 END HAVING CASE WHEN g_0."BOOLEANVALUE" = 0 THEN 'false' WHEN g_0."BOOLEANVALUE" IS NOT NULL THEN 'true' END >= (SELECT MIN(CASE WHEN g_1."BOOLEANVALUE" = 0 THEN 'false' WHEN g_1."BOOLEANVALUE" IS NOT NULL THEN 'true' END) FROM "DV"."SMALLA" g_1 WHERE trunc(g_1."INTKEY") = trunc(g_0."INTKEY"))
> {code}
> Exception:
> {code:plain}
> 07:59:09,071 WARN  [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue54) Connector worker process failed for atomic-request=gVALrvEkz/+o.7.3.8: org.teiid.translator.jdbc.JDBCExecutionException: 979 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT trunc(g_0."INTKEY"), CASE WHEN g_0."BOOLEANVALUE" = 0 THEN 0 WHEN g_0."BOOLEANVALUE" IS NOT NULL THEN 1 END FROM "DV"."SMALLA" g_0 GROUP BY trunc(g_0."INTKEY"), CASE WHEN g_0."BOOLEANVALUE" = 0 THEN 0 WHEN g_0."BOOLEANVALUE" IS NOT NULL THEN 1 END HAVING CASE WHEN g_0."BOOLEANVALUE" = 0 THEN 'false' WHEN g_0."BOOLEANVALUE" IS NOT NULL THEN 'true' END >= (SELECT MIN(CASE WHEN g_1."BOOLEANVALUE" = 0 THEN 'false' WHEN g_1."BOOLEANVALUE" IS NOT NULL THEN 'true' END) FROM "DV"."SMALLA" g_1 WHERE trunc(g_1."INTKEY") = trunc(g_0."INTKEY"))]
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-2.jar:8.12.5.redhat-2]
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0-internal]
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0-internal]
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0-internal]
> 	at java.lang.reflect.Method.invoke(Method.java:483) [rt.jar:1.8.0-internal]
> 	at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> 	at com.sun.proxy.$Proxy22.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-internal]
> 	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:1142) [rt.jar:1.8.0-internal]
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal]
> 	at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal]
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
> 	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
> 	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
> 	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
> 	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
> 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
> 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
> 	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
> 	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
> 	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
> 	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
> 	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.5.redhat-2.jar:8.12.5.redhat-2]
> 	... 18 more
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)