Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9941

Window functions: two functions, one (wrong) sorting is used

    XMLWordPrintable

    Details

      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

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

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