[HIVE-23060] Query failing with error "Grouping sets expression is not in GROUP BY key. Error encountered near token"

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: HiveServer2
• Assignee: mahesh kumar behera
• Reporter: Luis E Martinez-Poblete
• Created: 20/Mar/20 16:57
• Updated: 27/Feb/24 22:23
• Resolved: 24/Mar/20 22:29

Description

Synopsis:
=========
Query failing with error "Grouping sets expression is not in GROUP BY key. Error encountered near token"

Problem:
========
A Hive query in a view which fails with the following error:

Error while compiling statement: FAILED: SemanticException 35:21 [Error 10213]: Grouping sets expression is not in GROUP BY key. Error encountered near token 'l0_equities_region_id'

Reproduction case:

create database test;				

create table test.case665558 (c1 string, c2 string);

-- Working query				
select
   case
      when GROUPING__ID = 255 then `c1`
   end as `col_1`,
   case
      when GROUPING__ID = 255 then 3
   end as `col_2`,
   `c1`,
   `c2`
from
   `test`.`case665558`
group by
   `c1`,
   `c2`
GROUPING SETS 
   (
      (`c1`),
      (`c1`, `c2`)
   );
   
create view   test.viewcase665558 
as
select
   case
      when GROUPING__ID = 255 then `c1`
   end as `col_1`,
   case
      when GROUPING__ID = 255 then 3
   end as `col_2`,
   `c1`,
   `c2`
from
   `test`.`case665558`
group by
   `c1`,
   `c2`
GROUPING SETS 
   (
      (`c1`),
      (`c1`, `c2`)
   );   
   
Select * from test.viewcase665558 ;

Error: Error while compiling statement: FAILED: SemanticException 17:1 [Error 10213]: Grouping sets expression is not in GROUP BY key. Error encountered near token 'c1' (state=42000,code=40000)

The issue is because when the view is created, it adds the name of the table to the columns. This seems to be confusing Hive:

+-------------------------------------------------+--+
|                 createtab_stmt                  |
+-------------------------------------------------+--+
| CREATE VIEW `test.viewcase665558` AS select     |
| case                                            |
| when GROUPING__ID = 255 then `case665558`.`c1`  |
| end as `col_1`,                                 |
| case                                            |
| when GROUPING__ID = 255 then 3                  |
| end as `col_2`,                                 |
| `case665558`.`c1`,                              |
| `case665558`.`c2`                               |
| from                                            |
| `test`.`case665558`                             |
| group by                                        |
| `case665558`.`c1`,                              |
| `case665558`.`c2`                               |
| GROUPING SETS                                   |
| (                                               |
| (c1),                                           |
| (c1, c2)                                        |
| )                                               |
+-------------------------------------------------+--+