hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "mahesh kumar behera (Jira)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-23060) Query failing with error "Grouping sets expression is not in GROUP BY key. Error encountered near token"
Date Tue, 24 Mar 2020 12:17:00 GMT

     [ https://issues.apache.org/jira/browse/HIVE-23060?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

mahesh kumar behera updated HIVE-23060:
---------------------------------------
    Status: Open  (was: Patch Available)

> 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
>            Priority: Major
>         Attachments: HIVE-23060.01.patch, HIVE-23060.02.patch, HIVE-23060.03.patch
>
>
> 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)

Mime
View raw message