You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Renukaprasad C (Jira)" <ji...@apache.org> on 2020/05/24 11:30:00 UTC

[jira] [Commented] (HIVE-23457) Hive Incorrect result with subquery while optimizer misses the aggregation stage

    [ https://issues.apache.org/jira/browse/HIVE-23457?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17115149#comment-17115149 ] 

Renukaprasad C commented on HIVE-23457:
---------------------------------------

[~Rajkumar Singh]
Limit is mandatory in Subqueries (HIVE-6348). So the ORDERY_BY is ommitted and continue to execute the query.
In the console WARN log been dispalayed - WARNING: Order/Sort by without limit in sub query or view [tmp] is removed, as it's pointless and bad for performance. 

> Hive Incorrect result with subquery while optimizer misses the aggregation stage 
> ---------------------------------------------------------------------------------
>
>                 Key: HIVE-23457
>                 URL: https://issues.apache.org/jira/browse/HIVE-23457
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 3.2.0
>            Reporter: Rajkumar Singh
>            Priority: Critical
>
> Steps to Repro:
> {code:java}
> create table abc (id int);
> insert into table abc values (1),(2),(3),(4),(5),(6);
> select * from abc order by id desc
> 6
> 5
> 4
> 3
> 2
> 1
> select `id` from (select * from abc order by id desc ) as tmp;
> 1
> 2
> 3
> 4
> 5
> 6
>  
> {code}
> looking at the query plan it seems while using the subquery optimizer missed the aggregation stage, I cant see any reduce stage.
> {code:java}
> set hive.query.results.cache.enabled=false;
> explain select * from abc order by id desc;
> +----------------------------------------------------+
> |                      Explain                       |
> +----------------------------------------------------+
> | Plan optimized by CBO.                             |
> |                                                    |
> | Vertex dependency in root stage                    |
> | Reducer 2 <- Map 1 (SIMPLE_EDGE)                   |
> |                                                    |
> | Stage-0                                            |
> |   Fetch Operator                                   |
> |     limit:-1                                       |
> |     Stage-1                                        |
> |       Reducer 2 vectorized                         |
> |       File Output Operator [FS_8]                  |
> |         Select Operator [SEL_7] (rows=6 width=4)   |
> |           Output:["_col0"]                         |
> |         <-Map 1 [SIMPLE_EDGE] vectorized           |
> |           SHUFFLE [RS_6]                           |
> |             Select Operator [SEL_5] (rows=6 width=4) |
> |               Output:["_col0"]                     |
> |               TableScan [TS_0] (rows=6 width=4)    |
> |                 default@abc,abc, ACID table,Tbl:COMPLETE,Col:COMPLETE,Output:["id"] |
> |                                                    |
> +----------------------------------------------------+
> explain select `id` from (select * from abc order by id desc ) as tmp;
> +------------------------------+
> |           Explain            |
> +------------------------------+
> | Plan optimized by CBO.       |
> |                              |
> | Stage-0                      |
> |   Fetch Operator             |
> |     limit:-1                 |
> |     Select Operator [SEL_1]  |
> |       Output:["_col0"]       |
> |       TableScan [TS_0]       |
> |         Output:["id"]        |
> |                              |
> +------------------------------+
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)