Hive
  1. Hive
  2. HIVE-1449

Table aliases in order by clause lead to semantic analysis failure

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Query Processor
    • Labels:
      None

      Description

      A simple statement of the form

      select a.account_id, count(1) from tmp_ash_test2 a group by a.account_id order by a.account_id;

      throws a sematic analysis exception

      where as

      select a.account_id, count(1) from tmp_ash_test2 a group by a.account_id order by account_id;

      works fine (the second query does not have the table alias a in the order by clause.

        Activity

        Hide
        sukhendu chakraborty added a comment -

        I have slightly different symptoms but they seem to be related:

        select dist Group1 , count x from cars_tab order by dist;
        FAILED: Error in semantic analysis: Line 1:43 Invalid table alias or column reference dist

        However, if I use the column alias (Group1), it passes:
        select dist Group1 , count x from cars_tab order by Group1;

        Show
        sukhendu chakraborty added a comment - I have slightly different symptoms but they seem to be related: select dist Group1 , count x from cars_tab order by dist; FAILED: Error in semantic analysis: Line 1:43 Invalid table alias or column reference dist However, if I use the column alias (Group1), it passes: select dist Group1 , count x from cars_tab order by Group1;
        Carl Steinbach made changes -
        Field Original Value New Value
        Fix Version/s 0.7.0 [ 12315150 ]
        Hide
        Vick Fisher added a comment -

        This is more serious than it first appears. If you join two tables, and they both have a column with the same name, then there is no way to specify the order by clause in a query that also has group by. Even with no alias of the table name, just preceding the column name with the table name causes an error.

        For example:

        SELECT threaded_flight.flight_id, min(threaded_track.altitude)
        FROM threaded_flight JOIN threaded_track ON (threaded_flight.flight_id = threaded_track.flight_id)
        GROUP BY threaded_flight.flight_id
        ORDER BY threaded_flight.flight_id

        gives the error:

        "Error in semantic analysis: line 1:219 Invalid Table Alias or Column Reference threaded_flight"

        The query works without the ORDER BY.

        Show
        Vick Fisher added a comment - This is more serious than it first appears. If you join two tables, and they both have a column with the same name, then there is no way to specify the order by clause in a query that also has group by. Even with no alias of the table name, just preceding the column name with the table name causes an error. For example: SELECT threaded_flight.flight_id, min(threaded_track.altitude) FROM threaded_flight JOIN threaded_track ON (threaded_flight.flight_id = threaded_track.flight_id) GROUP BY threaded_flight.flight_id ORDER BY threaded_flight.flight_id gives the error: "Error in semantic analysis: line 1:219 Invalid Table Alias or Column Reference threaded_flight" The query works without the ORDER BY.
        Ashish Thusoo created issue -

          People

          • Assignee:
            Ashish Thusoo
            Reporter:
            Ashish Thusoo
          • Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:

              Development