today I was checking that certain operations are correct in our application and thus opened the differences between two workspaces.
While doing some cross-checking with results from the SQL Developer I noticed some really strange behaviour when selecting from the _DIFF view with the SQL Developer.
We have a normal difference for an object: it is modified in the parent and unchanged in the child workspace.
If I simple call setDiffversions(parent, child) and then do a +select *+ on the respective _DIFF view of the table I only get 2 rows per object!
both of these rows have NC as changecode.
I then took the primary key (let's assume it's 100) of one of the objects and changed my query simply to select * from XYZ_DIFF where primary_key=100 and voila, now I get 3 rows for the object, this time with the correct result that is also displayed when I do everything via our application.
the same happens when I use the query select * from XYZ_DIFF where primary_key in (100, 101, ....)
only when I don't have a where clause I get too few rows.
while there seems to be no direct impact on our application I still wonder whether this effect can cause serious bugs in certain situations.
has anyone ever experienced something like that before?
also, I used two different versions of the SQL developer and had the same result both times. and the effect only appeared when choosing "meaningful" where clauses, e.g. checking against the primary key or wm_diffversion.
I can't imagine this is expected behaviour.
I extracted the data from the DIFF view and LT table for one object where that happens, so I could supply that. no idea how the database got driven into a state where something like that can happen.
This is obviously not expected. I have seen cases where the _DIFF view has returned 2 rows before, although never in production. It was the case that there was an issue with the optimizer. My guess is that a different SQL plan is being used within your application vs SQL Developer. The plan used for SQL developer is not selecting the thrid row for whatever reason. File a SR (most likely against the optimizer) if you need hits looked into. Are you able to reproduce this at all in SQL*Plus? The addition of the predicates will change the plan, and in this case, allowed the correct results to be returned.