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

[Steven Hawkins (JIRA)]

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

Steven Hawkins resolved TEIID-1669.
-----------------------------------

    Resolution: Duplicate Issue


dup of TEIID-1571

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

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira