5 Replies Latest reply on Dec 30, 2013 10:19 PM by shawkins

Teiid exception in SubQuery

jkhoury Dec 26, 2013 11:50 AM

Hi there,

I am getting an exception with and Sql query that contains a sub-query.

SELECT Bank.Code AS Bank_Code,

(SELECT avg(Bank_sub.n)

FROM Bank Bank_sub

WHERE (Bank_sub.Code = Bank.Code))

AS Field4

FROM Bank Bank

GROUP BY Bank.Code

org.teiid.jdbc.TeiidSQLException: TEIID30504data: 1054 TEIID11008:TEIID11004 Error executing statement(s):

[Prepared Values: [] SQL: SELECT g_0.`Code`, (SELECT AVG(g_1.`n`) FROM `data`.`Bank` AS g_1 WHERE g_1.`Code` = anon_grp0.gcol0) FROM `data`.`Bank` AS g_0 GROUP BY g_0.`Code`].

Regards

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
Actions
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..
Actions
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
Actions
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
Actions
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
Actions

Go to original post