Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!m73g2000cwd.googlegroups.com!not-for-mail
From: Audison.Athena@gmail.com
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc
Subject: an odd phenomenum for pseudocolumn
Date: 10 Oct 2006 09:10:35 -0700
Organization: http://groups.google.com
Lines: 35
Message-ID: <1160496635.538400.146330@m73g2000cwd.googlegroups.com>
NNTP-Posting-Host: 222.71.120.220
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1160496641 25669 127.0.0.1 (10 Oct 2006 16:10:41 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 10 Oct 2006 16:10:41 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.7) Gecko/20060909 Firefox/1.5.0.7,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: m73g2000cwd.googlegroups.com; posting-host=222.71.120.220;
   posting-account=dPPzuQ0AAAAyqY6RWLzBvHkjz9fskQOm
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:276944 comp.databases.oracle.misc:130776

I heard this odd problem from my colleage, and from his oral
description of the problem I just outline the structure of this SQL
clause omitting any other trivial things as folllows:

select b.*
from
(
select rownum row_id, a.*
from
(
select *
from table_ref1, table_ref2, table_ref3...
where complex condition
order by clause consist of several column
) a
where rownum <= a specified_num1
) b
where b.row_id > a specified_num2

in one environment, it seems the result of this sql statement is
random, with some probability it gives the expected answer, however
sometimes it return empty set.
however in another environment, and the same program was executed with
no problem, each run gives the right answer.

of course I know the same semantics of this clause can be implemented
by rank function, and it seems more fit to dispose this kind of
problem, here I want to know if this clause has any problem?if it has a
problem, it may be due to the pseudocolumn rownum, it appears in a
subquery select list and is aliased and refered at the containing query
where clause. If this pseudocolumn was substituted by a normal column,
then I promise no problem.
in the top-level query, the pseudocolumn in the subquery was referenced
as it's alias name, I don't know if oracle allow this kind of usage?