From: steve.cat.south@cat.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   
      
      
   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   
      
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)