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

["Rajkumar Singh (Jira)" <ji...@apache.org>]

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)