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

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 3.2.0
• Component/s: Hive
• Assignee: Unassigned
• Reporter: Rajkumar Singh
• Created: 13/May/20 05:00
• Updated: 25/May/20 03:36

Description

Steps to Repro:

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
 

looking at the query plan it seems while using the subquery optimizer missed the aggregation stage, I cant see any reduce stage.

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"]        |
|                              |
+------------------------------+