RE: BUG: Row_Number() - Help with a test case

From: Anthony Wilson <amwilson_at_iinet.net.au>
Date: Tue, 08 May 2007 14:53:13 +0800
Message-Id: <32806.1178607193@iinet.net.au>

Hi all,

I am an idiot, please disregard this. Obviously the error occurs because of the indeterminacy of row_number() when more than one record are ranked the same with respect to the ordering of the partition.

Can't believe it took me so long to see that.

cheers,
Anthony

Original Message ----- From: 'amwilson_at_iinet.net.au' <amwilson_at_iinet.net.au> To: oracle-l_at_freelists.org Sent: Tue May 8 11:32 Subject: Fwd: BUG: Row_Number() - Help with a test case

Hi All,

I've posted this on the OTN forums but haven't had much help, so apologies to anyone who's already seen this.

We've come across a bug in our 10.1 instances when selecting from a view which uses the ROW_NUMBER() function. I'd like to produce a small test case to send to our DBA's. I've searched Metalink and have not found any bugs which relate to this behaviour.

I've distilled the situation into as small a test case as I can produce. I'd be grateful if some people here could cast their eye over it and tell me:

If you are able to reproduce this behaviour using my test script.
Any ideas on how to produce a smaller test case, or to clarify exactly what causes this.

The situation may be summarised as follows: a table T with an ID column (not nullable, non-unique), some kind of flag column (in this case called CLOSED), which is nullable, and a date field used for ordering the partitions.

After creating and analyzing the test table, running a query of the form:

with v as (

select closed
, row_number() over (

      partition by id
      order by some_date desc

) rn
from t
)
select count(*)
from v
where rn = 1
and closed is null

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 08 2007 - 01:53:13 CDT