[teiid-issues] [JBoss JIRA] (TEIID-2802) HIVE2: got Invalid table alias or column reference when running UNION ALL query on HIVE2

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12936069#comment-12936069 ] 

Steven Hawkins commented on TEIID-2802:
---------------------------------------

So more than likely this would happen even without the union as it seems like the presence of the grouping requires the order by to use the output names from the select clause.  Can you confirm if the direct to Hive (not through Teiid) query:

SELECT v_0.c_0 FROM (SELECT c_0 FROM (SELECT g_1.name AS c_0 FROM
opportunities_small g_1 UNION ALL SELECT g_0.name AS c_0 FROM opportunities
g_0) X__) v_0 GROUP BY v_0.c_0 ORDER BY c_0

works as expected.
                
> HIVE2:  got Invalid table alias or column reference when running UNION ALL query on HIVE2
> -----------------------------------------------------------------------------------------
>
>                 Key: TEIID-2802
>                 URL: https://issues.jboss.org/browse/TEIID-2802
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.6
>         Environment: TEIID with HIVE2
>            Reporter: Ivan Chan
>            Assignee: Steven Hawkins
>              Labels: teiid
>
> If I tried to combine 2 tables from a same HIVE2 data source using UNION ALL, I got "Invalid table alias or column reference" error.
> Query that I submitted to Teiid originally:
> select "name"
> from (Select * from hive2.opportunities_small UNION ALL Select * from
> hive2.opportunities) "query"
> group by "name"
> order by "name"
> Query that Teiid generated:
> SELECT v_0.c_0 FROM (SELECT c_0 FROM (SELECT g_1.name AS c_0 FROM
> opportunities_small g_1 UNION ALL SELECT g_0.name AS c_0 FROM opportunities
> g_0) X__) v_0 GROUP BY v_0.c_0 ORDER BY v_0.c_0
> Error:
> Invalid table alias or column reference 'v_0': (possible column names are: c_0)

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira