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

[Luis E Martinez-Poblete (Jira)]

Luis E Martinez-Poblete created HIVE-23060:
----------------------------------------------

             Summary: Query failing with error "Grouping sets expression is not 
in GROUP BY key. Error encountered near token"
                 Key: HIVE-23060
                 URL: https://issues.apache.org/jira/browse/HIVE-23060
             Project: Hive
          Issue Type: Bug
          Components: HiveServer2
            Reporter: Luis E Martinez-Poblete
            Assignee: mahesh kumar behera


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:

{noformat}
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)
{noformat}

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:

{noformat}
+-------------------------------------------------+--+
|                 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)                                        |
| )                                               |
+-------------------------------------------------+--+
{noformat}




--
This message was sent by Atlassian Jira
(v8.3.4#803005)