[MDEV-9941] Window functions: two functions, one (wrong) sorting is used - 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

Consider an example:

create table t1 (
a int,
b int,
c int
);

insert into t1 values
(10, 1, 1),
(10, 3, 10),
(10, 1, 10),
(10, 3, 100),
(10, 5, 1000),
(10, 1, 100);

explain format=json

select

  a,b,c,

  row_number() over (partition by a),

  row_number() over (partition by a, b)

from t1;

The query plan is incorrect. We can't just sort by "a":

  "query_block": {

    "select_id": 1,

    "window_functions_computation": {

      "sorts": {

        "filesort": {

          "sort_key": "t1.a"

},

      "temporary_table": {

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 6,

          "filtered": 100

Attachments

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia added a comment - 2016-04-19 03:56

And running the SELECT produces a wrong result (added "ORDER BY b" to the query so it's easier to understand):

MariaDB [j81]> select    a,b,c,   row_number() over (partition by a),   row_number() over (partition by a, b) from t1 order by b;

+------+------+------+------------------------------------+---------------------------------------+

| a    | b    | c    | row_number() over (partition by a) | row_number() over (partition by a, b) |

+------+------+------+------------------------------------+---------------------------------------+

|   10 |    1 |   10 |                                  1 |                                     1 |

|   10 |    1 |    1 |                                  2 |                                     2 |

|   10 |    1 |   10 |                                  6 |                                     1 |

|   10 |    1 |  100 |                                  7 |                                     2 |

|   10 |    1 |    1 |                                 10 |                                     1 |

|   10 |    3 |  100 |                                  3 |                                     1 |

|   10 |    3 |   10 |                                  4 |                                     2 |

|   10 |    3 |  100 |                                  8 |                                     1 |

|   10 |    3 |   10 |                                 11 |                                     1 |

|   10 |    5 | 1000 |                                  5 |                                     1 |

|   10 |    5 | 1000 |                                  9 |                                     1 |

+------+------+------+------------------------------------+---------------------------------------+

Sergei Petrunia added a comment - 2016-04-19 03:56 And running the SELECT produces a wrong result (added "ORDER BY b" to the query so it's easier to understand): MariaDB [j81]> select a,b,c, row_number() over (partition by a), row_number() over (partition by a, b) from t1 order by b; +------+------+------+------------------------------------+---------------------------------------+ | a | b | c | row_number() over (partition by a) | row_number() over (partition by a, b) | +------+------+------+------------------------------------+---------------------------------------+ | 10 | 1 | 10 | 1 | 1 | | 10 | 1 | 1 | 2 | 2 | | 10 | 1 | 10 | 6 | 1 | | 10 | 1 | 100 | 7 | 2 | | 10 | 1 | 1 | 10 | 1 | | 10 | 3 | 100 | 3 | 1 | | 10 | 3 | 10 | 4 | 2 | | 10 | 3 | 100 | 8 | 1 | | 10 | 3 | 10 | 11 | 1 | | 10 | 5 | 1000 | 5 | 1 | | 10 | 5 | 1000 | 9 | 1 | +------+------+------+------------------------------------+---------------------------------------+

Igor Babaev (Inactive) added a comment - 2016-04-19 06:42

Sergey,

It means only that you have to sort by (a,b). Choose the correct order list.

Igor Babaev (Inactive) added a comment - 2016-04-19 06:42 Sergey, It means only that you have to sort by (a,b). Choose the correct order list.

Igor Babaev (Inactive) added a comment - 2016-09-23 21:47

This bug apparently was fixed some time ago in 10.2
I added the test case for it into win.test.

Igor Babaev (Inactive) added a comment - 2016-09-23 21:47 This bug apparently was fixed some time ago in 10.2 I added the test case for it into win.test.

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2016-04-19 03:55

Updated:: 2016-09-23 21:47

Resolved:: 2016-09-23 21:47

Git Integration

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

MariaDB Server