Query does cartesian join unless

Question

I've got a query that's supposed to return 2 rows. However, it returns 48 rows. It's acting like one of the tables that's being joined isn't there. But if I add a column from that table to the select clause, with no changes to the from or where parts of the query, it returns 2 rows.

Here's what "Explain plan" says without the "m.*" in the select:

Here it is again after adding m.* in the select:

Can anybody explain why it should behave this way?

Update: We only had this problem on one system and not another. The DBA verified that the one with the problem is running optimizer_features_enable set to 10.2.0.5, and the one where it doesn't happen is running optimizer_features_enable set to 10.2.0.4. Unfortunately the customer site is running 10.2.0.5.

Answer 1

It's about a join elimination that was introduced in 10gR2:

Table elimination (alternately called "join elimination") removes redundant tables from a query. A table is redundant if its columns are only referenced to in join predicates, and it is guaranteed that those joins neither filter nor expand the resulting rows. There are several cases where Oracle will eliminate a redundant table.

Maybe that's kind of related bug or so. Have a look at this article.