Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2802

HIVE2: got Invalid table alias or column reference when running UNION ALL query on HIVE2

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 8.7
    • 8.6
    • Query Engine
    • Hide

      Run UNION ALL query to different tables within same HIVE data source.

      Submit similar query like the following one to teiid:

      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)

      Show
      Run UNION ALL query to different tables within same HIVE data source. Submit similar query like the following one to teiid: 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)

    Description

      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)

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            mchantibco Ivan Chan (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: