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

Window function produces incorrect value

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None

      Description

      create table t0 (a int);
      insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t2 (part_id int, pk int, a int);
      insert into t2 select if(a<5, 0, 1), a, if(a<5, NULL, 1) from t0;

      select * from t2;
      +---------+------+------+
      | part_id | pk   | a    |
      +---------+------+------+
      |       0 |    0 | NULL |
      |       0 |    1 | NULL |
      |       0 |    2 | NULL |
      |       0 |    3 | NULL |
      |       0 |    4 | NULL |
      |       1 |    5 |    1 |
      |       1 |    6 |    1 |
      |       1 |    7 |    1 |
      |       1 |    8 |    1 |
      |       1 |    9 |    1 |
      +---------+------+------+

      select 
        part_id, pk, a,
        count(a) over (partition by part_id order by pk
                       rows between 1 preceding and 1 following) as CNT
      from t2;
      +---------+------+------+-----+
      | part_id | pk   | a    | CNT |
      +---------+------+------+-----+
      |       0 |    0 | NULL |   0 |
      |       0 |    1 | NULL |   0 |
      |       0 |    2 | NULL |   0 |
      |       0 |    3 | NULL |   0 |
      |       0 |    4 | NULL |   0 |
      |       1 |    5 |    1 |   0 |
      |       1 |    6 |    1 |   0 |
      |       1 |    7 |    1 |   0 |
      |       1 |    8 |    1 |   0 |
      |       1 |    9 |    1 |   0 |
      +---------+------+------+-----+

      In other settings the value of window can be different.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: