Hive
  1. Hive
  2. HIVE-2228

Can't use DB qualified column names in WHERE or GROUP BY clauses

    Details

    • Type: Bug Bug
    • Status: Patch Available
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.7.0
    • Fix Version/s: None
    • Labels:
      None

      Description

      Hive doesn't allow you to use DB qualified column names in the WHERE or GROUP BY clauses. The workaround is to define a table alias:

      hive> CREATE DATABASE db1;
      OK
      
      hive> CREATE TABLE db1.t(a INT, b INT);
      OK
      
      hive> SELECT * FROM db1.t WHERE db1.t.a > 100;
      FAILED: Error in semantic analysis: Line 1:26 Invalid table alias or column reference 'db1'
      
      hive> SELECT * FROM db1.t t WHERE t.a > 100;
      OK
      
      hive> SELECT * FROM db1.t GROUP BY db1.t.a;
      FAILED: Error in semantic analysis: Line 1:29 Invalid table alias or column reference 'db1'
      
      hive> SELECT * FROM db1.t t GROUP BY t.a;
      OK
      
      1. HIVE-2228.2.patch.txt
        539 kB
        Zhenxiao Luo
      2. HIVE-2228.1.patch.txt
        584 kB
        Zhenxiao Luo

        Issue Links

          Activity

          Hide
          Zhenxiao Luo added a comment -

          propose to add dbName into ColumnInfo. The logic is mainly in TypeCheckProcFactory. While, lots of updates in TestParse.

          Review Request submitted at:
          https://reviews.facebook.net/D5913

          Show
          Zhenxiao Luo added a comment - propose to add dbName into ColumnInfo. The logic is mainly in TypeCheckProcFactory. While, lots of updates in TestParse. Review Request submitted at: https://reviews.facebook.net/D5913
          Hide
          Namit Jain added a comment -

          Isn't it true for other clauses also ?
          I mean, order by, sort by, distribute by etc.
          It is OK if you want to do that in a follow-up, but wanted to confirm.

          Show
          Namit Jain added a comment - Isn't it true for other clauses also ? I mean, order by, sort by, distribute by etc. It is OK if you want to do that in a follow-up, but wanted to confirm.
          Hide
          Namit Jain added a comment -

          minor comments on phabricator

          Show
          Namit Jain added a comment - minor comments on phabricator
          Hide
          Zhenxiao Luo added a comment -

          @Namit: Thanks for the comment.
          You are correct, none of the other clauses working: SORT BY, ORDER BY, CLUSTER BY, and DISTRIBUTE BY.

          I filed HIVE-3542 and link with this ticket.

          Show
          Zhenxiao Luo added a comment - @Namit: Thanks for the comment. You are correct, none of the other clauses working: SORT BY, ORDER BY, CLUSTER BY, and DISTRIBUTE BY. I filed HIVE-3542 and link with this ticket.
          Hide
          Zhenxiao Luo added a comment -

          Comments addressed. Review Request submitted at:
          https://reviews.facebook.net/D5913

          Show
          Zhenxiao Luo added a comment - Comments addressed. Review Request submitted at: https://reviews.facebook.net/D5913

            People

            • Assignee:
              Zhenxiao Luo
              Reporter:
              Carl Steinbach
            • Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:

                Development