Re: Query gives incorrect results

andrewst wrote:

> 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

It chews up CPU andlooks really really cool?

Well that and, I suspect, no one ever asked the question that jumped out at all of us.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)