Sorting with ROW_NUMBER Bug

Hello all,

I see a strange behavior in my
Oracle 10.2.0.1 Database. When I create
a table like this:

CREATE TABLE BUG_TEST
(
  USER_NAME VARCHAR2(200 BYTE)
);

then create a view on the table:

CREATE OR REPLACE VIEW VI_BUG_TEST
(USER_NAME)
AS
SELECT
usr.USER_NAME USER_NAME
FROM
BUG_TEST usr
ORDER BY USER_NAME Insert test data:

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'a');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'b');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'c');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'd');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'e');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'f');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'g');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'h');

INSERT INTO BUG_TEST ( USER_NAME ) VALUES (
'i');

COMMIT; and then execute this statement:

SELECT
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY vi.USER_NAME) my_row_num, MY_MAX_ROW,
vi.USER_NAME
FROM (
SELECT MAX(ROWNUM) OVER (PARTITION BY 1) MY_MAX_ROW, my_vi.* FROM VI_BUG_TEST my_vi
) vi
ORDER BY my_row_num

The result is:

MY_ROW_NUM MY_MAX_ROW USER_NAME
---------- ---------- ---------

         1          9 a
         2          9 b
         3          9 c
         4          9 d
         5          9 i
         6          9 f
         7          9 g
         8          9 h
         9          9 e

9 rows selected.

I think the result should be sorted by
USER_NAME but it is not.

When changing
MAX(ROWNUM) OVER (PARTITION BY 1)
to
MAX(ROWNUM) OVER ()
resulting in:

SELECT
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY vi.USER_NAME) my_row_num, MY_MAX_ROW,
vi.USER_NAME
FROM (
SELECT MAX(ROWNUM) OVER () MY_MAX_ROW, my_vi.* FROM VI_BUG_TEST my_vi ) vi
ORDER BY my_row_num

the result is correct:

MY_ROW_NUM MY_MAX_ROW USER_NAME
---------- ---------- ---------

         1          9 a
         2          9 b
         3          9 c
         4          9 d
         5          9 e
         6          9 f
         7          9 g
         8          9 h
         9          9 i

9 rows selected.

Am I wrong with the result I expect or is it a Bug? Does someone know if this bug can be fixed? And if it really is a bug how can I report it oracle in the best way?

Björn Received on Thu Jul 06 2006 - 09:01:12 CDT