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

Multiple window functions don't generate correct sorting criterias

Details

    Description

      create table t1 (
      pk int primary key,
      a int,
      b int,
      c char(10),
      d decimal(10, 3),
      e real
      );
      insert into t1 values
      ( 1, 0, 1,    'one',    0.1,  0.001),
      ( 2, 0, 2,    'two',    0.2,  0.002),
      ( 3, 0, 3,    'three',  0.3,  0.003),
      ( 4, 1, 2,    'three',  0.4,  0.004),
      ( 5, 1, 1,    'two',    0.5,  0.005),
      ( 6, 1, 1,    'one',    0.6,  0.006),
      ( 7, 2, NULL, 'n_one',  0.5,  0.007),
      ( 8, 2, 1,    'n_two',  NULL, 0.008),
      ( 9, 2, 2,    NULL,     0.7,  0.009),
      (10, 2, 0,    'n_four', 0.8,  0.010),
      (11, 2, 10,   NULL,     0.9,  NULL);
      

      The following query doesn't sort the rows of the table correctly:

      select pk, a,
      nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following),
      nth_value(pk, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following)
      from t1;
      

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

      Querying separately produces the correct sort key (a, pk)

      Attachments

        Activity

          igor Igor Babaev (Inactive) added a comment - - edited

          Vicentiu,

          I've found what caused this problem. This is a bug in the code of Window_funcs_sort::setup.
          Tomorrow I will fix it.

          Could you please provide me with a test case that can be executed on the current 10.2?

          igor Igor Babaev (Inactive) added a comment - - edited Vicentiu, I've found what caused this problem. This is a bug in the code of Window_funcs_sort::setup. Tomorrow I will fix it. Could you please provide me with a test case that can be executed on the current 10.2?

          Hi Igor,

          Just change nth_value(pk, 1) to sum(pk). That should work on the current 10.2.

          Vicentiu

          cvicentiu Vicențiu Ciorbaru added a comment - Hi Igor, Just change nth_value(pk, 1) to sum(pk). That should work on the current 10.2. Vicentiu

          The fix for this bug was pushed into the 10.2 tree.

          igor Igor Babaev (Inactive) added a comment - The fix for this bug was pushed into the 10.2 tree.

          People

            igor Igor Babaev (Inactive)
            cvicentiu Vicențiu Ciorbaru
            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.