|
When computing window functions we group them (if possible) by the sort key, so that we perform
the minimum number of sorts possible. However, the following order by clauses are incompatible, yet we group them together:
create table t1 (
|
pk int primary key,
|
a int,
|
b int,
|
c char(10)
|
);
|
|
insert into t1 values
|
( 1, 0, 1, 'one'),
|
( 2, 0, 2, 'two'),
|
( 3, 0, 3, 'three'),
|
( 4, 1, 1, 'one'),
|
( 5, 1, 1, 'two'),
|
( 6, 1, 2, 'three'),
|
( 7, 2, NULL, 'n_one'),
|
( 8, 2, 1, 'n_two'),
|
( 9, 2, 2, 'n_three'),
|
(10, 2, 0, 'n_four'),
|
(11, 2, 10, NULL);
|
|
select row_number() over (order by a), row_number() over (order by a desc) from t;
|
select pk,
|
row_number() over (order by pk desc) as r_desc,
|
row_number() over (order by pk asc) as r_asc
|
from t1;
|
|
pk r_desc r_asc
|
1 11 11
|
2 10 10
|
3 9 9
|
4 8 8
|
5 7 7
|
6 6 6
|
7 5 5
|
8 4 4
|
9 3 3
|
10 2 2
|
11 1 1
|
The row numbers should be 11, 10, .. for the first column, but 1, 2, 3, ... for the second column. Running the row_number() queries separately outputs the correct results.
This should also be considered a potential problem for partition by.
|