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

Window functions: wrong sort criteria is used

Details

    Description

      create table t1 (a int, b int, c int);
      insert into t1 values (1,3,1),(2,2,1),(3,1,1);
      

      explain format=json
      select 
        rank() over (partition by c order by a),
        count(*) over (partition by c)
      from t1;
      

      EXPLAIN shows:

        "query_block": {
          "select_id": 1,
          "window_functions_computation": {
            "sorts": {
              "filesort": {
                "sort_key": "t1.c"
              }
            },
            "temporary_table": {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows": 3,
                "filtered": 100
              }
            }
          }
        }
      

      In debugger I put a breakpoint into the loop in order_window_funcs_by_window_specs and see:

      (gdb) p dbug_print_item(prev)
        $103 = 0x555556d7e280 "count(*) over (partition by c)"
      (gdb) p prev->marker
        $104 = 7
      (gdb) p dbug_print_item(curr)
        $105 = 0x555556d7e280 "rank() over (partition by c order by a)"
      (gdb) p curr->marker
        $106 = 0
      

      Sorting on just t1.c is wrong. RANK() function needs sorting by t1.c, t1.a.

      Attachments

        Issue Links

          Activity

            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            psergei Sergei Petrunia made changes -
            Assignee Igor Babaev [ igor ]

            Sergey,

            This is not a bug.
            The sort order for the sub-sequence of window functions starting from the element marked by SORTORDER_CHANGE_FLAG
            up to the next element marked by SORTORDER_CHANGE_FLAG must be taken from the last element of the
            sub-sequence (not from the first one).

            igor Igor Babaev (Inactive) added a comment - Sergey, This is not a bug. The sort order for the sub-sequence of window functions starting from the element marked by SORTORDER_CHANGE_FLAG up to the next element marked by SORTORDER_CHANGE_FLAG must be taken from the last element of the sub-sequence (not from the first one).

            Ok, I was using the list incorrectly, then. Will fix now.

            psergei Sergei Petrunia added a comment - Ok, I was using the list incorrectly, then. Will fix now.
            psergei Sergei Petrunia made changes -
            Assignee Igor Babaev [ igor ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Summary Window functions: bug in order_window_funcs_by_window_specs Window functions: wrong sort criteria is used
            psergei Sergei Petrunia made changes -
            Fix Version/s N/A [ 14700 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            Component/s Optimizer - Window functions [ 13502 ]
            Component/s Optimizer [ 10200 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 74769 ] MariaDB v4 [ 132835 ]

            People

              psergei Sergei Petrunia
              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.