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

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: