distinct + analytic function + exists - no_unnest + "where in" = nonsense

The example below is a simplified emp/dept version of a real query I ran into today, which gave way more results than expected.
A combination of distinct + analytic function + exists + "where in" turned out to be the cause of problem. The query itself was correct, but the database returns an unexpected result.

The only source of information on this problem that I could find, was https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533831800346334231.
On this page it is suggested that the problem is fixed in 12.1, but on my 21c machine it is still there.
I will not repeat what is discussed there - please do read it, it is quite informative.

The UNNEST hint (which fixes the problem) "Instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.".
This sort of explains what goes wrong in the first place. The DEPT table contains 4 rows, and because the optimizer does not unnest (merge) the subquery, the sum is done 4 times instead of 1, in spite of the distinct. I think...
Getting that far took quite some effort, so I thought I'd share my analysis, and my simplified emp/dept version of the examples from the link above.
The emp/dept example queries make little sense of course - you'd never need queries like these - but they are easiest to read so please ignore that detail :-)

-- The data source: select sum(e.sal) as sum_sal from emp e where e.deptno = 10 -- Result = 8750 (OK) - the sum of 3 rows -- The problem itself: select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where e.deptno in (select d2.deptno from dept d2) ) -- Result = 35000 (NOK) - the correct (sum) value of 8750 is multiplied by the 4 departments from the exists subquery, even though "exists" only returns true of false -- Analysis 1 (without distinct): select e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where e.deptno in (select d2.deptno from dept d2) ) -- Result 8750 (OK) - but of course 3 rows instead of 1, as expected -- Analysis 2 (without analytic function): select distinct e.deptno , sum(e.sal) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where e.deptno in (select d2.deptno from dept d2) ) group by e.deptno -- Result = 8750 (OK) - note that the distinct is of course not needed actually -- Analysis 3 (without exists) select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 -- Result = 8750 (OK) -- Analysis 4 (without "where in"): select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d ) -- Result = 8750 (OK) -- Analysis 5 (with a "where in" and rownum = 1): select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where e.deptno in (select d2.deptno from dept d2) and rownum = 1) -- Result = 8750 (OK) -- Analysis 6 (with a "where in" and rownum > 1): select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where e.deptno in (select d2.deptno from dept d2) and rownum > 1) -- Result = null (NOK) - I expected 35000, or maybe 26250 (3*8750) -- Analysis 7 (with a "where in" and a union): select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where e.deptno in (select 10 from dual union select 10 from dual)) -- Result = 35000 (NOK) - I expected 8750, since the union only returns 1 row, like Analysis 5 -- Analysis 8 (use deptno from dept in the exists): select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select 1 from dept d where d.deptno in (select d2.deptno from dept d2) ) -- Result = 8750 (OK) - so as long as you do not use a table from "outside" the exists, you should be OK -- Analysis 9 (with no_unnest hint): select distinct e.deptno , sum(e.sal) over(partition by e.deptno) as sum_sal from emp e where e.deptno = 10 and exists ( select /*+ no_unnest */ 1 from dept d where e.deptno in (select d2.deptno from dept d2) ) -- Result = 8750 (OK)