hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mihir Kulkarni (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-4429) Nested ORDER BY produces incorrect result
Date Sat, 27 Apr 2013 00:34:16 GMT

     [ https://issues.apache.org/jira/browse/HIVE-4429?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mihir Kulkarni updated HIVE-4429:
---------------------------------

    Description: 
Nested ORDER BY clause doesn't honor the outer one in specific case.

The below query produces result which honors only the inner ORDER BY clause. (it produces
only 1 MapRed job)

{code:title=Query.sql|borderStyle=solid}
SELECT alias.b0 as d0, alias.b1 as d1
FROM
    (SELECT test.a0 as b0, test.a1 as b1 
    FROM test
    ORDER BY b1 ASC, b0 DESC) alias    
ORDER BY d0 ASC, d1 DESC;

{code}

---------------------------------------------------------------------------

On the other hand the query below honors the outer ORDER BY clause which produces the correct
result. (it produces 2 MapRed jobs)

{code:borderStyle=solid}
SELECT alias.b0 as d0, alias.b1 as d1
FROM
    (SELECT test.a0 as b0, test.a1 as b1 
    FROM test
    ORDER BY b1 ASC, b0 DESC) alias    
ORDER BY d0 DESC, d1 DESC;

{code}

---------------------------------------------------------------------------
Any other combination of nested ORDER BY clauses does produce the correct result.
Please see attachments for query, schema and Hive Commands for reprocase.


  was:
Nested ORDER BY clause doesn't honor the outer one in specific case.

The below query produces result which honors only the inner ORDER BY clause. (it produces
only 1 MapRed job)

{code:title=query.sql|borderStyle=solid}
SELECT alias.b0 as d0, alias.b1 as d1
FROM
    (SELECT test.a0 as b0, test.a1 as b1 
    FROM test
    ORDER BY b1 ASC, b0 DESC) alias    
ORDER BY d0 ASC, d1 DESC;

{code}

---------------------------------------------------------------------------

On the other hand the query below honors the outer ORDER BY clause which produces the correct
result. (it produces 2 MapRed jobs)

SELECT alias.b0 as d0, alias.b1 as d1
FROM
    (SELECT test.a0 as b0, test.a1 as b1 
    FROM test
    ORDER BY b1 ASC, b0 DESC) alias    
ORDER BY d0 DESC, d1 DESC;

---------------------------------------------------------------------------
Any other combination of nested ORDER BY clauses does produce the correct result.
Please see attachments for query, schema and Hive Commands for reprocase.


    
> Nested ORDER BY produces incorrect result
> -----------------------------------------
>
>                 Key: HIVE-4429
>                 URL: https://issues.apache.org/jira/browse/HIVE-4429
>             Project: Hive
>          Issue Type: Bug
>          Components: Query Processor, SQL, UDF
>    Affects Versions: 0.9.0
>         Environment: Red Hat Linux VM with Hive 0.9 and Hadoop 2.0
>            Reporter: Mihir Kulkarni
>            Priority: Critical
>         Attachments: Hive_Command_Script.txt, HiveQuery.txt, Test_Data.txt
>
>
> Nested ORDER BY clause doesn't honor the outer one in specific case.
> The below query produces result which honors only the inner ORDER BY clause. (it produces
only 1 MapRed job)
> {code:title=Query.sql|borderStyle=solid}
> SELECT alias.b0 as d0, alias.b1 as d1
> FROM
>     (SELECT test.a0 as b0, test.a1 as b1 
>     FROM test
>     ORDER BY b1 ASC, b0 DESC) alias    
> ORDER BY d0 ASC, d1 DESC;
> {code}
> ---------------------------------------------------------------------------
> On the other hand the query below honors the outer ORDER BY clause which produces the
correct result. (it produces 2 MapRed jobs)
> {code:borderStyle=solid}
> SELECT alias.b0 as d0, alias.b1 as d1
> FROM
>     (SELECT test.a0 as b0, test.a1 as b1 
>     FROM test
>     ORDER BY b1 ASC, b0 DESC) alias    
> ORDER BY d0 DESC, d1 DESC;
> {code}
> ---------------------------------------------------------------------------
> Any other combination of nested ORDER BY clauses does produce the correct result.
> Please see attachments for query, schema and Hive Commands for reprocase.

--
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

Mime
View raw message