BUG: Row_Number() - Help with a test case

From: <amwilson_at_iinet.net.au>
Date: Tue, 08 May 2007 11:32:48 +0800
Message-Id: <29322.1178595168@iinet.net.au>

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

...a number of times produces non-deterministic results (the result seems to toggle from one value to another). In my test case I have created a UNION ALL query, containing the above query repeated 5 times.

I've noticed that:

- The number of distinct ID's seems to affect whether or not this happens.
- There needs to be at least ~1% of values in the CLOSED column which are null.
- The number of rows in the table seems to affect this (you may need to increase

the number to see this happen).

Here is my test case:

REM Start of test case
prompt Enter number of rows:
accept numrows

drop table t;

create table t (

id number not null
, closed varchar2(1)
, some_date date not null

)
nologging;

insert /*+ append */
into t (

id
, closed
, some_date

)
-- ~10,000 distinct ID's
select trunc(dbms_random.value * 10000)
-- ~1% of records have CLOSED not null
, case sign(trunc(dbms_random.value * 100) - 1)

when -1 then null
else 'Y'
end
, sysdate
from dual
-- Number of rows
connect by level <= &numrows;

Some versions seem to require the commit before analysing commit;

exec dbms_stats.gather_table_stats(user, 'T');

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 union all
select count(*) from v where rn = 1 and closed is null union all
select count(*) from v where rn = 1 and closed is null union all
select count(*) from v where rn = 1 and closed is null union all
select count(*) from v where rn = 1 and closed is null;

REM End of test case

And here are the results I see under 10.2.0.2.0: SQL> select * from v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

SQL> ed
Wrote file afiedt.buf

1 explain plan for
2 with v as (

  3     select closed
  4     , row_number() over (
  5        partition by id
  6        order by some_date desc
  7     ) rn
  8     from t

9 )

 10  select count(*) from v where rn = 1 and closed is null union all
 11  select count(*) from v where rn = 1 and closed is null union all
 12  select count(*) from v where rn = 1 and closed is null union all
 13  select count(*) from v where rn = 1 and closed is null union all
 14* select count(*) from v where rn = 1 and closed is null

SQL> / Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 490403055

| Id  | Operation                  | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     5 |    75 |       |  8250  (81)|
00:01:40 |
|   1 |  UNION-ALL                 |      |       |       |       |            |
         |
|   2 |   SORT AGGREGATE           |      |     1 |    15 |       |            |
         |
|*  3 |    VIEW                    |      |   305K|  4477K|       |  1650   (2)|
00:00:20 |
|*  4 |     WINDOW SORT PUSHED RANK|      |   305K|  3880K|    14M|  1650   (2)|
00:00:20 |
|   5 |      TABLE ACCESS FULL     | T    |   305K|  3880K|       |   180   (4)|
00:00:03 |
|   6 |   SORT AGGREGATE           |      |     1 |    15 |       |            |
         |
|*  7 |    VIEW                    |      |   305K|  4477K|       |  1650   (2)|
00:00:20 |
|*  8 |     WINDOW SORT PUSHED RANK|      |   305K|  3880K|    14M|  1650   (2)|
00:00:20 |
|   9 |      TABLE ACCESS FULL     | T    |   305K|  3880K|       |   180   (4)|
00:00:03 |
|  10 |   SORT AGGREGATE           |      |     1 |    15 |       |            |
         |
|* 11 |    VIEW                    |      |   305K|  4477K|       |  1650   (2)|
00:00:20 |
|* 12 |     WINDOW SORT PUSHED RANK|      |   305K|  3880K|    14M|  1650   (2)|
00:00:20 |
|  13 |      TABLE ACCESS FULL     | T    |   305K|  3880K|       |   180   (4)|
00:00:03 |
|  14 |   SORT AGGREGATE           |      |     1 |    15 |       |            |
         |
|* 15 |    VIEW                    |      |   305K|  4477K|       |  1650   (2)|
00:00:20 |
|* 16 |     WINDOW SORT PUSHED RANK|      |   305K|  3880K|    14M|  1650   (2)|
00:00:20 |
|  17 |      TABLE ACCESS FULL     | T    |   305K|  3880K|       |   180   (4)|
00:00:03 |
|  18 |   SORT AGGREGATE           |      |     1 |    15 |       |            |
         |
|* 19 |    VIEW                    |      |   305K|  4477K|       |  1650   (2)|
00:00:20 |
|* 20 |     WINDOW SORT PUSHED RANK|      |   305K|  3880K|    14M|  1650   (2)|
00:00:20 |
|  21 |      TABLE ACCESS FULL     | T    |   305K|  3880K|       |   180   (4)|

00:00:03 |

Predicate Information (identified by operation id):

3 - filter("RN"=1 AND "CLOSED" IS NULL) 4 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY

INTERNAL_FUNCTION("SOME_DATE") DESC )<=1) 7 - filter("RN"=1 AND "CLOSED" IS NULL) 8 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY

INTERNAL_FUNCTION("SOME_DATE") DESC )<=1) 11 - filter("RN"=1 AND "CLOSED" IS NULL) 12 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY

INTERNAL_FUNCTION("SOME_DATE") DESC )<=1) 15 - filter("RN"=1 AND "CLOSED" IS NULL) 16 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY

INTERNAL_FUNCTION("SOME_DATE") DESC )<=1) 19 - filter("RN"=1 AND "CLOSED" IS NULL) 20 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY

INTERNAL_FUNCTION("SOME_DATE") DESC )<=1)

Note

'PLAN_TABLE' is old version

SQL> ed

1 with v as (

  2     select closed
  3     , row_number() over (
  4        partition by id
  5        order by some_date desc
  6     ) rn
  7     from t

8 )

  9  select count(*) from v where rn = 1 and closed is null union all
 10  select count(*) from v where rn = 1 and closed is null union all
 11  select count(*) from v where rn = 1 and closed is null union all
 12  select count(*) from v where rn = 1 and closed is null union all
 13* select count(*) from v where rn = 1 and closed is null

SQL> / COUNT(*)

I've reproduced this at work under the following versions, with the given number of rows in the test table:
8i - 300,000 rows
9i - 600,000 rows

10.1.0.3.0 - 300,000 rows
10.1.0.5.0 - 300,000 rows
10.2.0.2.0 - 300,000 rows

The behaviour seems erratic and sometimes does not manifest, but I'm unable to pin down the exact reason why. This test case is the best I can come up with, and seems to display the bug fairly consistently.

Any help would be greatly appreciated.

Thanks,
Anthony

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

Received on Mon May 07 2007 - 22:32:48 CDT