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

Window functions: wrong sort criteria is used

    XMLWordPrintable

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

              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.