when performing a semi-join against a query with a key preserved in the semi join criteria and a non-distinct subquery the planner will incorrectly created an unnested query that does not preserve the semi-join. This can happen with either the explicit mj hint or with the query unnesting property set.

e.g.

select col2 from tbl where keyCol IN (select dupCol from tbl2)

will become

select col2 from tbl, tbl2 where keyCol = tbl2

rather than

select col2 from tbl, (select distinct dupCol from tbl2) x where keyCol = tbl2