mdayakar commented on code in PR #6196: URL: https://github.com/apache/hive/pull/6196#discussion_r2544799072
########## 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: yes only grouping() UDF is enough, actually the reproduction queries I took from JIRA issue. Anyway I have a simple repro queries at the end so I will keep only that query and remove this query. -- 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]
