Teiid SQL Exception - Field not Present in Groupby Clause

Question

I have a query that uses a date_time column to produce an hour aggregation. The date_time column in aliased and present in the group by but I receive the following error:

': org.teiid.jdbc.TeiidSQLException: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 [DB.foo.date_time] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause

I can't seem to figure out why this is happening, since I don't try to pull the date_time column itself, just the Hour(date_time) I have aliased as "hr"

here is the query:

SELECT DISTINCT COUNT(*) as visits, hr
FROM
(SELECT DISTINCT 
        HOUR(date_time) AS hr, COUNT(DISTINCT(CONCAT(post_visid_high,CONCAT(post_visid_low,CONCAT(visit_num, visit_start_time_gmt))))) AS visits,  
        CASE 
            WHEN date_time BETWEEN '2017-12-07 00:00:00' AND '2017-12-07 23:59:59.999999' THEN 'yesterday' 
            WHEN date_time BETWEEN '2017-12-06 00:00:00' AND '2017-12-07 23:59:59.999999'  THEN 'day_before' 
        END AS date_range 
  FROM 
    DB.foo
  WHERE 
    date_time BETWEEN '2017-12-07 00:00:00' AND '2017-12-07 23:59:59.999999' 
    OR date_time BETWEEN '2017-12-06 00:00:00' AND '2017-12-07 23:59:59.999999' 
    AND report_suite IN ('X', 'Y', 'Z')
    AND exclude_hit = 0
    AND hit_source NOT IN (5,7,8,9)
      AND post_pagename IN 
      (SELECT DISTINCT post_pagename 
      FROM DB.foo 
      WHERE post_pagename IS NOT NULL 
        AND post_pagename != (' '))) b
GROUP BY hr

Any ideas why it thinks date_time is present instead of "hr"

Answer 1

This ended up being due to the way Teiid evaluates queries. The aliases are not recognized in GROUP BY clauses:

From Teiid docs: Column references in the group by cannot be made to alias names in the SELECT clause.

https://docs.jboss.org/author/display/teiid813final/GROUP+BY+Clause