[teiid-issues] [JBoss JIRA] (TEIID-4077) Mysql5 translator - unknow column in having clause

[Steven Hawkins (JIRA)]

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

Steven Hawkins updated TEIID-4077:
----------------------------------
    Component/s: JDBC Connector
       Priority: Major  (was: Critical)


This is a fairly specific mysql bug as the generated sql is valid.  They generally allow expressions as group by columns - except when there is also a having clause that also uses the grouping value, in which case they seem to want to use a select alias (that is not generally valid sql).  So it looks like we can just turn off support for expressions in group by - which we'll compensate for by introducing an inline view.

> Mysql5 translator - unknow column in having clause
> --------------------------------------------------
>
>                 Key: TEIID-4077
>                 URL: https://issues.jboss.org/browse/TEIID-4077
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.12.5
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> Query:
> {code:sql}
> SELECT A.INTKEY, A.BYTENUM FROM BQT1.SMALLA AS A GROUP BY A.INTKEY, A.BYTENUM HAVING A.BYTENUM = (SELECT B.bytenum FROM BQT1.SMALLA AS B WHERE (B.INTNUM = 10))
> {code}
> Source specific query:
> {code:sql}
> SELECT g_0.INTKEY, cast(g_0.BYTENUM AS signed) FROM smalla AS g_0 GROUP BY g_0.INTKEY, cast(g_0.BYTENUM AS signed) HAVING cast(g_0.BYTENUM AS signed) = (SELECT cast(g_1.BYTENUM AS signed) FROM smalla AS g_1 WHERE g_1.INTNUM = 10)
> {code}
> Exception:
> {code:plain}
> 07:33:07,377 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue36) Connector worker process failed for atomic-request=Z7YmVoh18LjC.13.2.6: org.teiid.translator.jdbc.JDBCExecutionException: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.INTKEY, cast(g_0.BYTENUM AS signed) FROM smalla AS g_0 GROUP BY g_0.INTKEY, cast(g_0.BYTENUM AS signed) HAVING cast(g_0.BYTENUM AS signed) = (SELECT cast(g_1.BYTENUM AS signed) FROM smalla AS g_1 WHERE g_1.INTNUM = 10)]
> 	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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'g_0.BYTENUM' in 'having clause'
> 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0-internal]
> 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0-internal]
> 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0-internal]
> 	at java.lang.reflect.Constructor.newInstance(Constructor.java:408) [rt.jar:1.8.0-internal]
> 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
> 	at com.mysql.jdbc.Util.getInstance(Util.java:386)
> 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
> 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
> 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
> 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
> 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
> 	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
> 	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
> 	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
> 	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)