[MDEV-10842] Wrong results when computing window functions with same sort key but different direction - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.2
Component/s: Optimizer - Window functions
Labels:
None

Description

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.

Attachments

Activity

Ascending order - Click to sort in descending order

Vicențiu Ciorbaru created issue - 2016-09-20 15:55

Vicențiu Ciorbaru made changes - 2016-09-20 15:55

Field	Original Value	New Value
Description	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: {code:sql} 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 (partition by pk desc) as r_desc, row_number() over (partition 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 {code:sql} This should also be considered a potential problem for partition by.	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: {code:sql} 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 (partition by pk desc) as r_desc, row_number() over (partition 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 {code} This should also be considered a potential problem for partition by.

Vicențiu Ciorbaru made changes - 2016-09-20 15:56

Description

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:

{code:sql}
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 (partition by pk desc) as r_desc,
         row_number() over (partition 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
{code}

This should also be considered a potential problem for partition by.

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:

{code:sql}
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 (partition by pk desc) as r_desc,
         row_number() over (partition 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
{code}

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.

Vicențiu Ciorbaru made changes - 2016-09-20 16:16

Description

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:

{code:sql}
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 (partition by pk desc) as r_desc,
         row_number() over (partition 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
{code}

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.

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:

{code:sql}
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
{code}

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.

Vicențiu Ciorbaru made changes - 2016-09-21 09:29

Fix Version/s		10.2.2 [ 22013 ]
Assignee	Vicentiu Ciorbaru [ cvicentiu ]	Igor Babaev [ igor ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:43

Workflow

MariaDB v3 [ 77143 ]

MariaDB v4 [ 150936 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Vicențiu Ciorbaru

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2016-09-20 15:55

Updated:: 2016-09-21 09:29

Resolved:: 2016-09-21 09:29

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration