[multistage][bug] multi semi-join hint not applied in weird conditions #12013
Description
#11937 introduces multi-join capability however there are several scenarios that this didn't apply
select /*+ joinOptions(join_strategy = 'dynamic_broadcast') */
--[work] key, COUNT(*)
--[work] *
--[work] sum(val)
--[work] sum(1)
--[not work] count(val)
--[not work] count(*)
from tbl
where col1 IN (select col from dim1 where ...)
and col2 IN (select col from dim2 where ...)
and col3 IN (select col from dim3 where ...)
group by key
specificallly when COUNT(*) is being used without agg-group, multi-semi join rules doesn't apply and 2 of the 3 IN clause are still modeled as shuffled inner-join