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

Window function produces incorrect value

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • Optimizer
    • 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

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.