Functions in SQL - very odd problem.

From: SteveS <steve.cat.south_at_cat.blueyonder.co.uk>
Date: Mon, 18 Apr 2005 08:57:51 GMT
Message-ID: <jEK8e.14616$G8.6811@text.news.blueyonder.co.uk>

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, worklistcreatets Date_Created, u2.fullname Created_by,samplename, 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,'NULL') in ('NULL','*','d')

and nvl(u2.state,'NULL') in ('NULL','*','d') and u1.state <> '+' and u2.state <> '+' and assignedanalyst=u1.name and auditusername=u2.name

Same date range this query yielded 142 records

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,'NULL') in ('NULL','*','d')
and nvl(u2.state,'NULL') in ('NULL','*','d')

-- and u1.state <> '+'

and u2.state <> '+' and assignedanalyst=u1.name and auditusername=u2.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.

--
Received on Mon Apr 18 2005 - 03:57:51 CDT