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 :-)