thomasrebele commented on code in PR #6601:
URL: https://github.com/apache/hive/pull/6601#discussion_r3578309543
##########
ql/src/test/queries/clientpositive/cbo_filter_pushdown_windowing_notnull.q:
##########
@@ -0,0 +1,88 @@
+-- HIVE-29729: an IS NOT NULL predicate above nested windowing (OVER) Projects
must
+-- not trip the RedundancyChecker in HiveFilterProjectTransposeRule.
+set hive.cbo.fallback.strategy=NEVER;
+
+create table cl_wl_daily_table (
+ user_id string,
+ lender string,
+ attributes string,
+ loan_type string,
+ whitelisting_status string
+) stored as orc;
+
+explain
+select
+ user_id,
+ cg_tg_cohort
+from (
+ select
+ user_id,
+ lender,
+ maximum_principal,
+ loan_type,
+ case
+ when loan_type = 'FRESH' and rnk <= (333333 / user_count) * 0.1 then 'CG'
+ when loan_type = 'FRESH' and rnk <= (333333 / user_count) then 'TG'
+ when loan_type = 'REPEAT' and rnk <= (500 / user_count)
+ and substring(user_id, -2) between '60' and '69' then 'CG'
+ when loan_type = 'REPEAT' and rnk <= (500 / user_count)
+ and substring(user_id, -2) between '00' and '45' then 'TG'
+ when loan_type = 'REPEAT' and rnk <= (500 / user_count) then 'TG-TELE'
+ end as cg_tg_cohort
+ from (
+ select
+ user_id,
+ lender,
+ maximum_principal,
+ loan_type,
+ percent_rank() over(partition by loan_type order by rnk) as rnk,
+ count(user_id) over(partition by loan_type) as user_count
+ from (
+ select
+ user_id,
+ lender,
+ maximum_principal,
+ loan_type,
+ row_number() over(partition by loan_type order by rand()) as rnk
+ from (
+ select
+ wl.user_id as user_id,
+ max(lender) as lender,
+ max(maximum_principal) as maximum_principal,
+ max(loan_type) as loan_type
+ from (
+ select
+ user_id,
+ lender,
+ case
+ when repeat_wl = 0 and platform_repeat_wl = 0 then 'FRESH'
+ else 'REPEAT'
+ end as loan_type,
+ cast(maximum_principal / 100 as int) as maximum_principal
+ from (
+ select
+ user_id,
+ concat_ws('#', sort_array(collect_set(lender))) as lender,
+ max(
+ case
+ when coalesce(get_json_object(attributes,
'$.maximum_principal'), '_') = '_' then 0
+ else get_json_object(attributes, '$.maximum_principal')
+ end
+ ) as maximum_principal,
+ max(case when loan_type = 'REPEAT' then 1 else 0 end) as
repeat_wl,
+ max(case when loan_type = 'PLATFORM_REPEAT' then 1 else 0 end)
as platform_repeat_wl
+ from cl_wl_daily_table
+ where whitelisting_status = 'Currently WL'
+ group by user_id
+ ) base
+ where maximum_principal > 0
+ and maximum_principal is not null
+ and maximum_principal <> '_'
+ ) wl
+ group by
+ wl.user_id
+ ) grouped_users
+ ) ranked_once
+ ) ranked_twice
+) cohorted
+where cg_tg_cohort is not null;
Review Comment:
Could you please replace the q file test with a minimal one that reproduces
the exception? How about the following q file test?
```
-- HIVE-29729: an IS NOT NULL predicate above nested windowing (OVER)
Projects must
-- not trip the RedundancyChecker in HiveFilterProjectTransposeRule.
set hive.cbo.fallback.strategy=NEVER;
create table tab1 (id string);
select rnk2
from (
select
row_number() over(order by rnk) as rnk2
from (
select count(*) over() as rnk
from (select count(*) from tab1) t
) t2
) t3
where rnk2 is not null;
```
--
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]
