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)