-
1. Re: Teiid exception in SubQuery
jchidiac Dec 27, 2013 2:42 AM (in response to jkhoury)I tried with the same query and i got the Same Error [i think this error is related to the Group BY ] :
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'anon_grp0.gcol0' in 'where clause'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:125)
... 13 more
-
2. Re: Teiid exception in SubQuery
rareddy Dec 27, 2013 1:06 PM (in response to jchidiac)John & Joseph,
Can you double check if the translator name on your model is set to "mysql" or "mysql5"?
If you attach your VDB (only if it can be shared openly), or if you can duplicate this with a sample Dynamic VDB, post that. We can check if this is bug vs usage error.
Thanks
Ramesh..
-
3. Re: Teiid exception in SubQuery
shawkins Dec 29, 2013 6:34 PM (in response to rareddy)This is a bug, but not one that I've seen before. [TEIID-1680] issues with correlation and aggregation - JBoss Issue Tracker covers a known planning issue that affects correlated aggregates - but here you are correlated on a grouping expression. It appears to still occur on latest. What version are you on? Can you log an issue?
It should be possible to use a workaround with an inline view such as:
SELECT Bank_Code,
(SELECT avg(Bank_sub.n)
FROM Bank Bank_sub
WHERE (Bank_sub.Code = Bank_Code))
AS Field4
FROM (SELECT Bank.Code AS Bank_Code FROM Bank GROUP BY Bank.Code) AS V
Steve
-
4. Re: Teiid exception in SubQuery
jchidiac Dec 30, 2013 2:22 AM (in response to rareddy)mysql5 as translator, and 8.6 as version
-
5. Re: Teiid exception in SubQuery
shawkins Dec 30, 2013 10:19 PM (in response to jchidiac)[TEIID-2787] Correlated grouping expression not properly replaced in pushdown subquery - JBoss Issue Tracker was logged and worked for 8.7.
Thanks,
Steve