Rajkumar Singh created HIVE-23457: ------------------------------------- Summary: 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
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)