oracle query with inconsistent results

Question

Im having a very strange problem, i have a complicated view that returns incorrect data when i query on a particular column.
heres an example:

select empname
       , has_garnishment 
from timecard_v2 
where empname = 'Testerson, Testy';

this returns the single result 'Testerson, Testy', 'N'

however, if i use the query:

select empname
       , has_garnishment 
from timecard_v2 
where empname = 'Testerson, Testy'
and has_garnishment = 'Y';

this returns the single result 'Testerson, Testy', 'Y'

The second query should return a subset of the first query, but it returns a different answer.

When I use the query:

select empname
       , has_garnishment 
from timecard_v2 
where empname = 'Testerson, Testy'
and has_garnishment = 'N';

I get no results

I have dissected the view and determined that this section of the view definition is where the problem arises and the problem exists even if i take the sql definition and run it as a straight query(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required):

SELECT 
  e.fullname empname ,
  NVL2(ded.has_garn, 'Y', 'N') has_garnishment
FROM timecard tc ,
  orderdetail od ,
  orderassign oa ,
  employee e ,
  employee3 e3 ,
  customer10 c10 ,
  order_misc om,
  (SELECT COUNT(*) has_garn,
    v_ssn
  FROM deductions
  WHERE yymmdd_stop                             = 0
  OR (LENGTH(yymmdd_stop)                       = 7
  AND to_date(SUBSTR(yymmdd_stop, 2), 'YYMMDD') > sysdate)
  GROUP BY v_ssn
  ) ded
WHERE oa.lrn(+) = tc.lrn_order
AND om.lrn(+)   = od.lrn
AND od.orderno  = oa.orderno
AND e.ssn       = tc.ssn
AND c10.custno  = tc.custno
AND e.lrn       = e3.lrn
AND e.ssn       = ded.v_ssn(+)

One thing of note about the definition of the 'ded' subquery. The v_ssn field is a virtual field on the deductions table.

I am not a DBA im a software developer but we recently lost our DBA and the new one is still getting up to speed so im trying to debug this issue. That being said, please explain things a little more thoroughly then you would for a fellow oracle expert.

thanks

Answer 1

Turns out the problem was a conflicting index. There was an old index on the column that the v_ssn virtual column is built from. I dropped that index and the query began behaving as expected. Im still concerned about how that index was affecting the query but at least my immediate problem is solved.

Thanks for your help!

Answer 2

Firstly, you have a join "AND od.orderno = oa.orderno" which will negate the outer join on "oa.lrn(+) = tc.lrn_order". IE, if tc.lrn_order doesn't find a match on oa, the outer join says to still return a row but that would have a null orderno which would fail any match with od

Secondly, the definition of the virtual column may be relevant. If removing the index fixed the problem, then it suggests that the earlier plan used the index.

Thirdly, the ded subquery seems a bit clunky. On the face of it,

SELECT COUNT(*) FROM tab WHERE COL=:val

seems pretty similar to

SELECT cnt FROM (SELECT COL, COUNT(*) FROM tab GROUP BY COL) WHERE COL=:val

but where :val doesn't exist in "tab", the first will return a count of 0 and the second won't return any rows (and with an outer join, would return NULL).

If there is a usable index on COL, then option 1 might be attractive. I suspect somewhere Oracle was re-writing the latter into the former, and if, rather than the NVL2, you had simply shown the has_garn value, you would have seen a 0 rather than a null.

An alternative for the bug might be this:

If v_ssn is based on func(col), then Oracle may (incorrectly) assume that func(COL) must be null if COL is null. If a large proportion of COL were null, it might assume that using an index on COL to find the not-null rows would give it a smaller and more efficient bunch of rows to process.