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

[Silvi Agarwal (JIRA)]

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