All geek questions in one place

Row_number () gets the wrong result under certain conditions

I have a SQL query that works fine in SQL Server, but it does not work on Oracle and, in my opinion, it should not.

This is an 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 correct calculations.

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

Explain the 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 to ROW_NUMBER(changing the value from the default ASC to DESC), it will 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 returns only one group.

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

Explain the 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

Note that reproducing the problem requires that the most internal query has an expression GROUP BY. If not, the result will be 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 use Oracle Database 12c Release 12.1.0.1.0 - 64bit

, ORDER BY GROUP BY, Oracle, SQLServer. :

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

+4
1

, 18353141. 11.2.0.4, 12.1.0.1, NLS_SORT, NLS_COMP - :

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

:

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

, - ; , ( 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

, , SQL Server, Oracle, , .

12.1.0.2, 12.1.0.1, .

+3

Source: https://habr.com/ru/post/1692619/

More articles:


All Articles