kasakrisz commented on code in PR #6196: URL: https://github.com/apache/hive/pull/6196#discussion_r2541535772
########## ql/src/test/queries/clientpositive/groupingset_grouping_udf_view.q: ########## @@ -0,0 +1,129 @@ +CREATE DATABASE IF NOT EXISTS test_grouping_set; +CREATE TABLE test_grouping_set.test_grouping( +id string, +s_outlook string, +m_outlook string, +f_outlook string, +s_rating string, +m_rating string, +f_rating string) +CLUSTERED BY ( +id) +INTO 2 BUCKETS +; +-- Run query with grouping sets +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +; + +-- Create view with same query +CREATE OR REPLACE VIEW test_view AS +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) Review Comment: I think the bug can be reproduced with fewer columns. Could you please remove the unnecessary ones. ########## ql/src/test/queries/clientpositive/groupingset_grouping_udf_view.q: ########## @@ -0,0 +1,129 @@ +CREATE DATABASE IF NOT EXISTS test_grouping_set; +CREATE TABLE test_grouping_set.test_grouping( +id string, +s_outlook string, +m_outlook string, +f_outlook string, +s_rating string, +m_rating string, +f_rating string) +CLUSTERED BY ( +id) +INTO 2 BUCKETS Review Comment: Does cluster by contributes to the bug? If not could you please remove it. ########## ql/src/test/results/clientpositive/llap/groupingset_grouping_udf_view.q.out: ########## @@ -0,0 +1,367 @@ +PREHOOK: query: CREATE DATABASE IF NOT EXISTS test_grouping_set +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:test_grouping_set +POSTHOOK: query: CREATE DATABASE IF NOT EXISTS test_grouping_set +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:test_grouping_set +PREHOOK: query: CREATE TABLE test_grouping_set.test_grouping( +id string, +s_outlook string, +m_outlook string, +f_outlook string, +s_rating string, +m_rating string, +f_rating string) +CLUSTERED BY ( +id) +INTO 2 BUCKETS +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:test_grouping_set +PREHOOK: Output: test_grouping_set@test_grouping +POSTHOOK: query: CREATE TABLE test_grouping_set.test_grouping( +id string, +s_outlook string, +m_outlook string, +f_outlook string, +s_rating string, +m_rating string, +f_rating string) +CLUSTERED BY ( +id) +INTO 2 BUCKETS +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:test_grouping_set +POSTHOOK: Output: test_grouping_set@test_grouping +PREHOOK: query: SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +PREHOOK: type: QUERY +PREHOOK: Input: test_grouping_set@test_grouping +#### A masked pattern was here #### +POSTHOOK: query: SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +POSTHOOK: type: QUERY +POSTHOOK: Input: test_grouping_set@test_grouping +#### A masked pattern was here #### +PREHOOK: query: CREATE OR REPLACE VIEW test_view AS +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +PREHOOK: type: CREATEVIEW +PREHOOK: Input: test_grouping_set@test_grouping +PREHOOK: Output: database:default +PREHOOK: Output: default@test_view +POSTHOOK: query: CREATE OR REPLACE VIEW test_view AS +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +POSTHOOK: type: CREATEVIEW +POSTHOOK: Input: test_grouping_set@test_grouping +POSTHOOK: Output: database:default +POSTHOOK: Output: default@test_view +POSTHOOK: Lineage: test_view.agency EXPRESSION [] +POSTHOOK: Lineage: test_view.id SIMPLE [(test_grouping)test_grouping.FieldSchema(name:id, type:string, comment:null), ] +POSTHOOK: Lineage: test_view.type EXPRESSION [] +POSTHOOK: Lineage: test_view.value EXPRESSION [(test_grouping)test_grouping.FieldSchema(name:s_outlook, type:string, comment:null), (test_grouping)test_grouping.FieldSchema(name:m_outlook, type:string, comment:null), (test_grouping)test_grouping.FieldSchema(name:f_outlook, type:string, comment:null), (test_grouping)test_grouping.FieldSchema(name:s_rating, type:string, comment:null), (test_grouping)test_grouping.FieldSchema(name:m_rating, type:string, comment:null), (test_grouping)test_grouping.FieldSchema(name:f_rating, type:string, comment:null), ] +PREHOOK: query: show create table test_view +PREHOOK: type: SHOW_CREATETABLE +PREHOOK: Input: default@test_view +POSTHOOK: query: show create table test_view +POSTHOOK: type: SHOW_CREATETABLE +POSTHOOK: Input: default@test_view +CREATE VIEW `test_view` AS SELECT +`test_grouping`.`id`, +CASE +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`s_outlook`) = 0 THEN 'outlook' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`m_outlook`)= 0 THEN 'outlook' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`f_outlook`)= 0 THEN 'outlook' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`s_rating`)= 0 THEN 'rating' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`m_rating`)= 0 THEN 'rating' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`f_rating`)= 0 THEN 'rating' +ELSE NULL +END `type`, +CASE +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`s_outlook`) = 0 THEN 'S' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`m_outlook`)= 0 THEN 'M' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`f_outlook`)= 0 THEN 'F' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`s_rating`)= 0 THEN 'S' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`m_rating`)= 0 THEN 'M' +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`f_rating`)= 0 THEN 'F' +ELSE NULL +END `agency`, +CASE +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`s_outlook`) = 0 THEN `test_grouping`.`s_outlook` +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`m_outlook`)= 0 THEN `test_grouping`.`m_outlook` +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`f_outlook`)= 0 THEN `test_grouping`.`f_outlook` +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`s_rating`)= 0 THEN `test_grouping`.`s_rating` +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`m_rating`)= 0 THEN `test_grouping`.`m_rating` +WHEN grouping(`test_grouping`.`id`, `test_grouping`.`f_rating`)= 0 THEN `test_grouping`.`f_rating` +ELSE NULL +END `value` +FROM `test_grouping_set`.`test_grouping` +GROUP BY +`test_grouping`.`id`, +`test_grouping`.`s_outlook`, +`test_grouping`.`m_outlook`, +`test_grouping`.`f_outlook`, +`test_grouping`.`s_rating`, +`test_grouping`.`m_rating`, +`test_grouping`.`f_rating` +GROUPING SETS ( +(`test_grouping`.`id`, `test_grouping`.`s_outlook`), +(`test_grouping`.`id`, `test_grouping`.`m_outlook`), +(`test_grouping`.`id`, `test_grouping`.`f_outlook`), +(`test_grouping`.`id`, `test_grouping`.`s_rating`), +(`test_grouping`.`id`, `test_grouping`.`m_rating`), +(`test_grouping`.`id`, `test_grouping`.`f_rating`) +) +PREHOOK: query: select * from test_view +PREHOOK: type: QUERY +PREHOOK: Input: default@test_view +PREHOOK: Input: test_grouping_set@test_grouping +#### A masked pattern was here #### +POSTHOOK: query: select * from test_view +POSTHOOK: type: QUERY +POSTHOOK: Input: default@test_view +POSTHOOK: Input: test_grouping_set@test_grouping +#### A masked pattern was here #### +PREHOOK: query: CREATE TABLE your_table(col1 int, col2 int, col3 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@your_table +POSTHOOK: query: CREATE TABLE your_table(col1 int, col2 int, col3 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@your_table +PREHOOK: query: create view your_view as SELECT + col1, + col2, + SUM(col3), + GROUPING(col1) AS is_col1_grouped +FROM + your_table +GROUP BY + col1, col2 +WITH CUBE +PREHOOK: type: CREATEVIEW +PREHOOK: Input: default@your_table +PREHOOK: Output: database:default +PREHOOK: Output: default@your_view +POSTHOOK: query: create view your_view as SELECT + col1, + col2, + SUM(col3), + GROUPING(col1) AS is_col1_grouped +FROM + your_table +GROUP BY + col1, col2 +WITH CUBE +POSTHOOK: type: CREATEVIEW +POSTHOOK: Input: default@your_table +POSTHOOK: Output: database:default +POSTHOOK: Output: default@your_view +POSTHOOK: Lineage: your_view._c2 EXPRESSION [(your_table)your_table.FieldSchema(name:col3, type:int, comment:null), ] +POSTHOOK: Lineage: your_view.col1 SIMPLE [(your_table)your_table.FieldSchema(name:col1, type:int, comment:null), ] +POSTHOOK: Lineage: your_view.col2 SIMPLE [(your_table)your_table.FieldSchema(name:col2, type:int, comment:null), ] +POSTHOOK: Lineage: your_view.is_col1_grouped EXPRESSION [] +PREHOOK: query: show create table your_view +PREHOOK: type: SHOW_CREATETABLE +PREHOOK: Input: default@your_view +POSTHOOK: query: show create table your_view +POSTHOOK: type: SHOW_CREATETABLE +POSTHOOK: Input: default@your_view +CREATE VIEW `your_view` AS SELECT + `your_table`.`col1`, + `your_table`.`col2`, + SUM(`your_table`.`col3`), + GROUPING(`your_table`.`col1`) AS `is_col1_grouped` +FROM + `default`.`your_table` +GROUP BY + `your_table`.`col1`, `your_table`.`col2` +WITH CUBE Review Comment: Looks like this command prints some optimized query text. Could you please run ``` describe formatted test_view; ``` It should print the `Expanded Query:` which is the one which used when compiling queries with view references. ########## ql/src/test/queries/clientpositive/groupingset_grouping_udf_view.q: ########## @@ -0,0 +1,129 @@ +CREATE DATABASE IF NOT EXISTS test_grouping_set; +CREATE TABLE test_grouping_set.test_grouping( +id string, +s_outlook string, +m_outlook string, +f_outlook string, +s_rating string, +m_rating string, +f_rating string) +CLUSTERED BY ( +id) +INTO 2 BUCKETS +; +-- Run query with grouping sets +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +; Review Comment: Is this query necessary for testing? We have lots of grouping sets related test. Could you please check if there is any which covers this? If not could you please simplify this query. It should contain only the minimum parts required for repro the bug. ########## ql/src/test/queries/clientpositive/groupingset_grouping_udf_view.q: ########## @@ -0,0 +1,129 @@ +CREATE DATABASE IF NOT EXISTS test_grouping_set; +CREATE TABLE test_grouping_set.test_grouping( +id string, +s_outlook string, +m_outlook string, +f_outlook string, +s_rating string, +m_rating string, +f_rating string) +CLUSTERED BY ( +id) +INTO 2 BUCKETS +; +-- Run query with grouping sets +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value +FROM test_grouping_set.test_grouping +GROUP BY +id, +s_outlook, +m_outlook, +f_outlook, +s_rating, +m_rating, +f_rating +GROUPING SETS ( +(id, s_outlook), +(id, m_outlook), +(id, f_outlook), +(id, s_rating), +(id, m_rating), +(id, f_rating) +) +; + +-- Create view with same query +CREATE OR REPLACE VIEW test_view AS +SELECT +id, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'outlook' +WHEN grouping(id, m_outlook)= 0 THEN 'outlook' +WHEN grouping(id, f_outlook)= 0 THEN 'outlook' +WHEN grouping(id, s_rating)= 0 THEN 'rating' +WHEN grouping(id, m_rating)= 0 THEN 'rating' +WHEN grouping(id, f_rating)= 0 THEN 'rating' +ELSE NULL +END type, +CASE +WHEN grouping(id, s_outlook) = 0 THEN 'S' +WHEN grouping(id, m_outlook)= 0 THEN 'M' +WHEN grouping(id, f_outlook)= 0 THEN 'F' +WHEN grouping(id, s_rating)= 0 THEN 'S' +WHEN grouping(id, m_rating)= 0 THEN 'M' +WHEN grouping(id, f_rating)= 0 THEN 'F' +ELSE NULL +END agency, +CASE +WHEN grouping(id, s_outlook) = 0 THEN s_outlook +WHEN grouping(id, m_outlook)= 0 THEN m_outlook +WHEN grouping(id, f_outlook)= 0 THEN f_outlook +WHEN grouping(id, s_rating)= 0 THEN s_rating +WHEN grouping(id, m_rating)= 0 THEN m_rating +WHEN grouping(id, f_rating)= 0 THEN f_rating +ELSE NULL +END value Review Comment: Does all the selected expressions especially the case expression contributes to the bug? If not could you please remove it. I suspect one call of ``` grouping(id, s_outlook) ``` should be enough. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
