Re: Query gives incorrect results

Originally posted by Edzard
> 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)
>

Irrelevant but facinating: however did the query come to contain this gem? :

TO_DATE('01-01-'||SUBSTR('2001',3,2),'DD-MM-YY') Apart from the 2-digit year bug as pointed out by Jim Kennedy, what is the point of doing this SUBSTR rather than just:

TO_DATE('01-01-2001','DD-MM-YYYY') ?

--
Posted via http://dbforums.com