Query gives incorrect results

From: Edzard <edzard_at_volcanomail.com>
Date: 3 Jun 2003 07:23:27 -0700
Message-ID: <5d75e934.0306030623.61e1feab@posting.google.com>

After migration of a Oracle7 database to 9iDB version 9.2.0.3, some queries appear to "no longer work".

SELECT o.object_name
, o.created
, pk.name

FROM   dba_dependencies         ap 


,      dba_dependencies         pk 

,      dba_objects              o 

WHERE  o.owner = 'SYS' 
AND    o.created BETWEEN
TO_DATE('01-01-'||SUBSTR('2001',3,2),'DD-MM-YY')
                 AND    

TO_DATE('31-12-'||SUBSTR('2003',3,2),'DD-MM-YY')

AND    ap.referenced_type  = o.object_type
AND    ap.referenced_name    = o.object_name
AND    ap.owner    = 'SYS' 
AND    pk.referenced_type  = ap.type 
AND    pk.referenced_name    = ap.name 
AND    pk.owner    = 'SYS' 

AND rownum < 10
/

In my database this query returns no rows.

Now add just seven spaces after the word WHERE:

SELECT o.object_name
, o.created
, pk.name

FROM   dba_dependencies         ap 


,      dba_dependencies         pk 

,      dba_objects              o 

WHERE         o.owner = 'SYS' 
AND    o.created BETWEEN
TO_DATE('01-01-'||SUBSTR('2001',3,2),'DD-MM-YY')
                 AND    

TO_DATE('31-12-'||SUBSTR('2003',3,2),'DD-MM-YY')

AND    ap.referenced_type  = o.object_type
AND    ap.referenced_name    = o.object_name
AND    ap.owner    = 'SYS' 
AND    pk.referenced_type  = ap.type 
AND    pk.referenced_name    = ap.name 
AND    pk.owner    = 'SYS' 

AND rownum < 10
/

Now the query returns rows.

This problem is reported to Oracle and considered a bug. I mention it here as the Oracle bugnr is not publicly visible (2977019).

Generally the migration was succesful and I ran in only three queries that gave wrong results (in a large set of buggy reports) Received on Tue Jun 03 2003 - 09:23:27 CDT