row_number() gets wrong result in certain conditions

Question

I have a SQL query that works perfectly in SQL Server, but it fails on Oracle and, in my opinion, it shouldn't.

This is the example to reproduce it:

CREATE TABLE TEST
   ( TEST_ID     NUMBER(37,0) NOT NULL,
     TEST_NAME   VARCHAR2(50 BYTE), 
     TEST_GROUP  VARCHAR2(20 BYTE), 
     CONSTRAINT TEST_PK PRIMARY KEY (TEST_ID) );

INSERT INTO TEST (TEST_ID, TEST_NAME) VALUES (1, 'TEST 1');
INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_GROUP) VALUES (2, 'TEST 2', 'A');
INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_GROUP) VALUES (3, 'TEST 3', 'B');
INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_GROUP) VALUES (4, 'TEST 4', 'A');

This query returns the expected information:

SELECT TEST_GROUP, COUNT(R$), MIN(R$) R$_A, MAX(R$) R$_Z 
  FROM (
          SELECT MAIN.*, ROW_NUMBER() OVER (ORDER BY TEST_GROUP, TEST_ID) R$
            FROM ( SELECT TEST_ID, TEST_NAME, TEST_GROUP 
                   FROM TEST 
                   GROUP BY TEST_ID, TEST_NAME, TEST_GROUP 
                 ) MAIN
       ) MAIN
GROUP BY TEST_GROUP

It returns three TEST_GROUPS with the right calculations.

TEST_GROUP     COUNT(R$)    R$_A    R$_Z
-------------- --------- ------- -------
A                      2       1       2
B                      1       3       3
(null)                 1       4       4

Explain Plan:

OPERATION                OBJECT_NAME     CARDINALITY     COST 

SELECT STATEMENT                                   4        3       
SORT (GROUP BY NOSORT)                             4        3               
VIEW                                               4        3                       
WINDOW (NOSORT)                                    4        3                               
SORT (GROUP BY)                                    4        3                                       
TABLE ACCESS (FULL)      TEST                      4        3 

Other XML 
{info} 

info type="db_version" 
12.1.0.1 

info type="parse_schema" 
"BABTEC" 

info type="dynamic_sampling" 
2 

info type="plan_hash" 
1486410247 

info type="plan_hash_2" 
1249517352 

{hint} 

FULL(@"SEL$335DD26A" "TEST"@"SEL$3") 
NO_ACCESS(@"SEL$1" "MAIN"@"SEL$1") 
OUTLINE(@"SEL$3") 
OUTLINE(@"SEL$2") 
OUTLINE_LEAF(@"SEL$1") 
MERGE(@"SEL$3") 
OUTLINE_LEAF(@"SEL$335DD26A") 
ALL_ROWS 
DB_VERSION('12.1.0.1') 
OPTIMIZER_FEATURES_ENABLE('12.1.0.1') 
IGNORE_OPTIM_EMBEDDED_HINTS

But if we change the sorting in the ROW_NUMBER (by changing from the default ASC to DESC) it does not:

SELECT TEST_GROUP, COUNT(R$), MIN(R$) R$_A, MAX(R$) R$_Z 
FROM (
        SELECT MAIN.*, ROW_NUMBER() OVER (ORDER BY TEST_GROUP **DESC**, TEST_ID) R$
        FROM ( SELECT TEST_ID, TEST_NAME, TEST_GROUP 
               FROM TEST 
               GROUP BY TEST_ID, TEST_NAME, TEST_GROUP 
             ) MAIN
     ) MAIN
GROUP BY TEST_GROUP;

It only returns a single group.

TEST_GROUP     COUNT(R$)    R$_A    R$_Z
-------------- --------- ------- -------
A                      4       1       4

Explain Plan:

OPERATION                OBJECT_NAME     CARDINALITY     COST 

SELECT STATEMENT                                   4        3       
HASH(GROUP BY)                                     4        3               
VIEW                                               4        3                       
WINDOW (NOSORT)                                    4        3                               
SORT (GROUP BY)                                    4        3                                       
TABLE ACCESS (FULL)      TEST                      4        3 

Other XML 
{info} 

info type="db_version" 
12.1.0.1 

info type="parse_schema" 
"BABTEC" 

info type="dynamic_sampling" 
2 

info type="plan_hash" 
1128091058 

info type="plan_hash_2" 
3776505473 

{hint} 

FULL(@"SEL$335DD26A" "TEST"@"SEL$3") 
NO_ACCESS(@"SEL$1" "MAIN"@"SEL$1") 
OUTLINE(@"SEL$3") 
OUTLINE(@"SEL$2") 
OUTLINE_LEAF(@"SEL$1") 
MERGE(@"SEL$3") 
OUTLINE_LEAF(@"SEL$335DD26A") 
ALL_ROWS 
DB_VERSION('12.1.0.1') 
OPTIMIZER_FEATURES_ENABLE('12.1.0.1') 
IGNORE_OPTIM_EMBEDDED_HINTS

Notice that to reproduce the problem, it is required that the most internal query has a GROUP BY expression. If not, the result is the one we expect:

SELECT TEST_GROUP, COUNT(R$), MIN(R$) R$_A, MAX(R$) R$_Z 
FROM (
          SELECT MAIN.*, ROW_NUMBER() OVER (ORDER BY TEST_GROUP DESC, TEST_ID) R$
          FROM ( SELECT TEST_ID, TEST_NAME, TEST_GROUP 
                 FROM TEST ) MAIN
     ) MAIN
GROUP BY TEST_GROUP;

TEST_GROUP     COUNT(R$)    R$_A    R$_Z
----------------------------------------
(null)                 1       1       1
B                      1       2       2
A                      2       3       4

We are using Oracle Database 12c Release 12.1.0.1.0 - 64bit

There is a workaround for this problem that is add an ORDER BY clause after the GROUP BY, but this is only valid in Oracle, it fails in SQLServer. The query will be:

SELECT TEST_GROUP, COUNT(R$), MIN(R$) R$_A, MAX(R$) R$_Z 
FROM (
         SELECT MAIN.*, ROW_NUMBER() OVER (ORDER BY TEST_GROUP DESC, TEST_ID) R$
         FROM ( SELECT TEST_ID, TEST_NAME, TEST_GROUP 
                FROM TEST 
                GROUP BY TEST_ID, TEST_NAME, TEST_GROUP 
                ORDER BY TEST_GROUP DESC ) MAIN
     ) MAIN
GROUP BY TEST_GROUP;

TEST_GROUP     COUNT(R$)    R$_A    R$_Z
-------------- --------- ------- -------
(null)                 1       1       1
B                      1       2       2
A                      2       3       4

Any help will be appreciated

I am not able to reproduce the problem. The second query returns three rows, not one. — Florin Ghita, Jan 24 '18 at 09:28
You should edit your question to add the output that you see from each query. If this is an Oracle bug in your version of the database, we may not see the same output as you. — Boneist, Jan 24 '18 at 09:29
Just to confirm Florins result. I see 3 groups for the query where you see 1. I'm using 11.2.0.4. — BriteSponge, Jan 24 '18 at 10:04
Posting te explain plans for the different queries could be useful — Aleksej, Jan 24 '18 at 10:04
I can reproduce in 11.2.0.4 but not 12.1.0.2 (don't have access to 12.1.0.1). So I suspect you're seeing bug 18353141. Can you add the output of `select * from nls_session_parameters where parameter in ('NLS_SORT','NLS_COMP');` to the question please? — Alex Poole, Jan 24 '18 at 10:47
@Matias - yes, that'll do it... try `alter session set NLS_COMP=linguistic` and re-run. I believe you'll get correct results. Is there a reason you haven't patched up to 12.1.0.2 at least? — Alex Poole, Jan 24 '18 at 11:28
@Alex - Thanks for the quick answer. This mostly solves the main problem we found. We do not patched because our Oracle database is not for production purpose but developping. Our customers will surely have patched their database, or at least will know that there is an Oracle bug with a patch ready to be applied. — Matias, Jan 25 '18 at 08:29

score 3 · Accepted Answer · answered Jan 24 '18 at 11:49

This seems to be bug 18353141. It's reproducible in 11.2.0.4 as well as 12.1.0.1 if NLS_SORT is set and NLS_COMP is set to binary:

alter session set NLS_SORT=spanish;
alter session set NLS_COMP=binary;

-- your second query

T  COUNT(R$)       R$_A       R$_Z
- ---------- ---------- ----------
A          4          1          4

Changing the sort to linguistic fixes it:

alter session set NLS_SORT=spanish;
alter session set NLS_COMP=linguistic;

-- your second query

T  COUNT(R$)       R$_A       R$_Z
- ---------- ---------- ----------
           1          1          1
B          1          2          2
A          2          3          4

You can also modify the query to make the analytic order-by and the group-by different; e.g. this works just concatenating null (before the DESC):

alter session set NLS_SORT=spanish;
alter session set NLS_COMP=binary;

SELECT TEST_GROUP, COUNT(R$), MIN(R$) R$_A, MAX(R$) R$_Z 
  FROM (
        SELECT MAIN.*, ROW_NUMBER() OVER (ORDER BY TEST_GROUP||null DESC, TEST_ID) R$
-------------------------------------------------------------^^^^^^
          FROM ( SELECT TEST_ID, TEST_NAME, TEST_GROUP 
                   FROM TEST 
               GROUP BY TEST_ID, TEST_NAME, TEST_GROUP 
               ) MAIN
       ) MAIN
GROUP BY TEST_GROUP;

T  COUNT(R$)       R$_A       R$_Z
- ---------- ---------- ----------
           1          1          1
B          1          2          2
A          2          3          4

but it sounds like you want the same query to work in both SQL Server and Oracle so you'll need to find a way of modifying it that is valid for both.

It's fixed in the 12.1.0.2 patch set, and an individual patch is available for 12.1.0.1 if you aren't able to apply the patch set.

Thank you very much for your help :-) – Matias Jan 24 '18 at 12:59 — Matias, Jan 24 '18 at 12:59

row_number() gets wrong result in certain conditions

1 Answers1