Re: rownum, aggregates and (inline) views...

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Thu, 22 Aug 2002 23:45:49 -0400
Message-ID: <umbbvn51i6qva1@corp.supernews.com>

Quite true. Saw this strange behavior in 8.1.6.3

More tests:
  1 select r, id from (
  2 select rownum r, id from (

  3     select id from (select distinct t.id from t)
  4     where id=4

  5 ) where rownum < 4
  6* )
SQL> /          R ID
---------- ----------

         1 4

  1 select r, id from (
  2 select rownum r, id from (
  3 select id from (select distinct t.id from t)   4 -- where id=4
  5 ) where rownum < 4
  6* )
SQL> /          R ID
---------- ----------

         1          1
         2          2
         3          3

If its merging these two queries then the above results are just contradictory. I just cannot explain how oracle is processing them?

Anurag

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d650f41$0$237$ed9e5944_at_reading.news.pipex.net...
> It does seem a bit odd. FWIW your test does NOT reproduce on 8.1.7 or 9.2 on
> Win2000. Perhaps an upgrade would resolve the issue.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Gui" <koen.janssens_at_alcatel.be> wrote in message
> news:11ecf2b.0208220805.29a6e51b_at_posting.google.com...
> > Hello all.
> >
> > I am experiencing some weird query behavior trying to get the to N
> > rows of a view that contains a 'distinct' select. Although the real
> > life query is a bit more complex, i found a simple sample query that
> > has the same problem. I know this query is not very usefull an not
> > optimal, but its just to make a point:
> >
> > I have one table containing one integer field. The table contains 4
> > rows:
> >
> > create table t (id numeric);
> > insert into t values (1);
> > insert into t values (2);
> > insert into t values (3);
> > insert into t values (4);
> >
> > Then i do a simple select:
> >
> > select * from (select distinct id from t) where id=3
> >
> > This returns '3' as expected. Note that the 'select distinct id from
> > t' is a nested query.
> >
> > Now i only want to retrieve the first row of the result set of the
> > previous query:
> >
> > select * from (
> > select * from (select distinct id from t) where id=3
> > ) where rownum < 2
> >
> > This still returns '3', again as expected.
> >
> > Now i wrap another select * around this query:
> >
> > select * from (
> > select * from (
> > select * from (select distinct id from t) where id=3
> > ) where rownum < 2
> > )
> >
> > For some mysterious reason, this query does not return any rows
> > anymore.
> >
> > The only reason i can think of it that oracle merges the two where
> > clauses into one (id = 3 and rownum < 2). This would explain why i
> > don't see any results. But i have no idea why oracle does this in the
> > last query but not in the second one.
> >
> > Any ideas ? Am i missing something here ?
> >
> > We are using oracle 8.1.6 solaris edition.
> >
> > PS: If i add a rownum to the select part of the query, it works again:
> > select * from (
> > select * from (
> > select rownum r, q.* from (select distinct id from t) q where id=3
> > )q where rownum < 2
> > )
> > returns '3'. Don't ask me why....
>
>
Received on Thu Aug 22 2002 - 22:45:49 CDT