[teiid-issues] [JBoss JIRA] Resolved: (TEIID-1669) using aliases of table column in group by statements with Teiid

using aliases of table column in group by statements with Teiid --------------------------------------------------------------- Key: TEIID-1669 URL: https://issues.jboss.org/browse/TEIID-1669 Project: Teiid Issue Type: Bug Components: JDBC Connector, JDBC Driver Affects Versions: 7.4 Environment: Using Fedora15, DBvisualizer Reporter: Silvi Agarwal Assignee: Steven Hawkins Query: select table1.name as queues, count(table1.id) as new_total from ( select t.id as id, t.queue as name from RT3S.tickets as t, RT3S.transactions as trn where trn.Objectid = t.id and trn.Created >= {ts '2011-06-23 00:00:00'} and trn.Created < {ts '2011-06-25 00:00:00'} and trn.oldvalue = 'new' and t.queue in (42, 53, 62) ) as table1 Left Outer Join ( select ocfv.objectid as id, ocfv.customfield as cfv, ocfv.content as content from RT3S.ObjectCustomFieldValues as ocfv where ocfv.customfield = 27 ) as table2 on table1.id = table2.id where table2.cfv is null group by table1.name; Query is working fine without using "group by" and "count". But, if I am using 'Group by' I am getting an error: "Message:Error Code:1054 Message:'Unknown column 't.queue' in 'group statement'' error executing statement(s): > > [Prepared Values: [27, 42, 53, 62, {ts '2011-06-23 00:00:00.0'}, {ts '2011-06-25 00:00:00.0'}, 'new', 'new'] SQL: SELECT g_0.Queue AS c_0, COUNT(g_0.id) AS c_1 FROM (Tickets AS g_0 INNER JOIN Transactions AS g_1 ON g_1.ObjectId = g_0.id) LEFT OUTER JOIN ObjectCustomFieldValues AS g_2 ON g_0.id = g_2.ObjectId AND g_2.CustomField = ? WHERE g_0.Queue IN (?, ?, ?) AND g_1.Created >= ? AND g_1.Created < ? AND (g_1.OldValue = ? OR g_1.NewValue = ?) AND g_2.CustomField IS NULL GROUP BY t.queue LIMIT 10000] >" If we look at the sql it returns that the GROUP BY is saying "t.queue" but it aliased Tickets to g_0. I think Teiid is doing something wrong with aliases but not able to figure it out. Thanks for your help