Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Functions in SQL - very odd problem.

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:

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')

--

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 <> '+'

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US