Mysterious 9.2.0.4 (on HP-UX) problem

[SteveS]

Can anyone help with a mysterious problem that has arisen since 'upgrading'
from 8 to 9.2.0.4?

The situation is this:

Queries that worked fine under 8 are now producing *really* strange results
under 9.2.0.4. The queries all involve functions, including built-in
functions
like NVL. The strangeness is that less than the expected number of rows is
returned *or* and ORA-01422 is generated. Here's an example:
-- This query yielded 54 records
select distinct worklistname Worklist_Name, worklistcreatet s Date_Created,
u2.fullname Created_by,samp lename, u2.fullname Closed_By, audittimestamp
Date_Closed
from wkls_histx, lmsuser u1, lmsuser u2
where audittimestamp between '&StartDate' and '&EndDate'
and samplename not like 'JAN-%'
and fullprocname not like '%OBMCLIBRARY%'
and fullprocname not like '%MICROLIBRARY% '
and fullprocname not like '%OBMMLIBRARY%'
and assignedanalyst not in ('None','Not Defined')
and u2.fullname not like 'ChemLMS%'
-- and nvl(u1.state,'N ULL') in ('NULL','*','d' )
-- and nvl(u2.state,'N ULL') in ('NULL','*','d' )
and u1.state <> '+'
and u2.state <> '+'
and assignedanalyst =u1.name
and auditusername=u 2.name

--
-- Same date range this query yielded 142 records
select distinct worklistname Worklist_Name, worklistcreatet s Date_Created,
u2.fullname Created_by,samp lename, u2.fullname Closed_By, audittimestamp
Date_Closed
from wkls_histx, lmsuser u1, lmsuser u2
where audittimestamp between '&StartDate' and '&EndDate'
and samplename not like 'JAN-%'
and fullprocname not like '%OBMCLIBRARY%'
and fullprocname not like '%MICROLIBRARY% '
and fullprocname not like '%OBMMLIBRARY%'
and assignedanalyst not in ('None','Not Defined')
and u2.fullname not like 'ChemLMS%'
and nvl(u1.state,'N ULL') in ('NULL','*','d' )
and nvl(u2.state,'N ULL') in ('NULL','*','d' )
-- and u1.state <> '+'
-- and u2.state <> '+'
and assignedanalyst =u1.name
and auditusername=u 2.name
The difference between the two is that in the second example the nvl
function has been replaced by something that is functionally equivalent. It
has been verified that under Oracle 8 the two queries return the same number
of rows.

In another example, using a user-defined function, the number of rows
returned depends on the breadth of the criteria (the query is too complex to
show here), with broader criteria returning *fewer* rows, until the point
when one of the criteria is LIKE '%' and an ORA-01422 is returned. This
query also worked fine under Oracle 8.

I suspect two things are happening here. One is we are getting hit by a bug
in 9i:

"Incorrect Results (instead of ORA-1422) Selecting Through A Function "

.... but the question is, why on earth should a function that can only
possibly return one row be trying to return more than one?

Has anyone seen anything like this before?

Apologies if any of this is unclear,

Steve S

--

Put the cats out before replying.