An incoming link this morning took me to a thread on the Oracle forums. The problem related to the issue of a query returning the wrong results. The query in question was as follows:
SELECT ap_prod,ca_altcod,clm,pn_id FROM sa_codalt,sa_prodnat,sa_prod,usrtraco.traco_sa_prod,sa_natura WHERE ap_prod = pn_prod(+) AND pn_id IS NULL AND ca_prod = ap_prod AND ca_tipo = 90000000013 AND oldcod = ca_altcod AND nt_tiponat = 'CLM' AND nt_cod = clm ;
The problem is that this query seems to be working, returning 10 rows with a null pn_id as specified in the where clause. But when you add one more column to the select list, you get more rows back, including rows where pn_id is not null.
Now, the original poster does not make it easy for us to help him – he hasn’t used aliases on the tables and columns, hasn’t included execution plans, and (initially) didn’t point out that one of the tables is in the local database, two in one remote, and two in a second remote database.
This behaviour, however, is clearly a bug – the query is returning non-null results against a predicate that demands nulls. So let’s turn to metalink. How do you try to find the bug ?
First, identify the special features of this query. It’s got an outer join (they can be a source of great “entertainment”), it’s a distributed query (and it turned out that the OP couldn’t emulate the problem with a local-only version of the query) with a couple of database links, it’s got a null predicate (another cause of endless entertainment) which is being ignored. So let’s use that information to search.
First choose the advanced search, and tick just the option for the Bug database. Then type the phrase wrong results into the Exact Phrase text box.
After that I tried the following in the match all words text box: distributed outer join null. This returned just a few results (but if I had got more than 100 results, I would have used the product list to restrict the problem to the server). None of the results matched the symptoms, so I changed wrong results to wrong result (note the singular) just in case – again I got a few returns but no match.
So I changed the distributed to dblink. The problem looks like bug 4527815.
Total search time – 5 minutes (I’m not always that lucky – but a Metalink search is always worth a few minutes of anyone’s time).
Big Clue from the bug description – look at the execution plan for predicates disappearing when you do a remote join. In other words, you have to have at least two tables at the end of a database link before you could see this bug.
As I’ve said before – always do a proper investigation with explain plan using dbms_xplan properly to get the best possible output.