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

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


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