Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8091 Simple window functions
  3. MDEV-9695

Wrong window frame when using RANGE BETWEEN N FOLLOWING AND PRECEDING

    Details

    • Type: Technical task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      10.2.0-5, 10.2.0-6, 10.2.0-7, 10.2.0-8, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-4

      Description

      The sliding window defined by RANGE BETWEEN N FOLLOWING and N PRECEDING does not add/remove rows in the correct order.

      Example:

      insert into t1 values
      ( 1 , 0, 1),
      ( 2 , 0, 2),
      ( 3 , 1, 4),
      ( 4 , 1, 8),
      ( 5 , 2, 32),
      ( 6 , 2, 64),
      ( 7 , 2, 128),
      ( 8 , 2, 16);
       
      select pk, a, b,
      bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as bit_or
      from t1;
       
      pk	a	b	bit_or
      1	0	1	3
      2	0	2	3
      3	1	4	12
      4	1	8	12
      5	2	32	96
      6	2	64	224
      7	2	128	176
      8	2	16	48
      

      The issue lies within the last partition. The removal of elements from the window is wrong. It removes an element 1 row after the one that should be removed.

      .....
      5	2	32	96     // OK (32 | 64) = 96
      6	2	64	224   // OK (32 | 64 | 128) = 224
      7	2	128	176   // NOT OK. Should be (64 | 128 | 16) = 208. Instead it is: (32 | 128 | 16).
      8	2	16	48     // Again NOT OK. Should be (128 | 16) = 144. Instead it is: (32 | 16)
      .....
      

      The problem lies in the fact that the removal of elements from the sliding window is done incorectly.
      from [32, 64, 128], we should add the row containing 16 and remove the row containing 32. Instead, when calling the remove() function for the window function, the current item points to row with the value of 64. Thus we remove 64 and get [32, 128, 16].

      Again the same problem continues for the next row. Instead of removing the value 64, we are removing the value 128 (the row after the correct value).

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              cvicentiu Vicențiu Ciorbaru
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: