Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3304

Window functions in queries with embedded selects produce bad numbers

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.2.3
    • 1.2.5
    • ExeMgr
    • None
    • 2019-05, 2019-06

    Description

      See the following example below:
      The first query produces correct numbers.
      In the second query, all the sums over windows are 100 times too small!!
      If we create an InnoDB table, there is no issue

      CREATE TABLE `bug_cs` (
      `c` decimal(5,2) DEFAULT NULL
      ) ENGINE=columnstore;

      INSERT INTO `bug_cs`
      (`c`)
      VALUES (2.05), (5.44),(3.04);

      select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from bug_cs
      WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);

      select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from
      (select
      sum(c) as c
      from
      bug_cs
      ) t WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);

      Attachments

        Activity

          This is a regression in 1.2

          David.Hall David Hall (Inactive) added a comment - This is a regression in 1.2

          PR #117 for regression test

          David.Hall David Hall (Inactive) added a comment - PR #117 for regression test

          There is a regression test added

          David.Hall David Hall (Inactive) added a comment - There is a regression test added

          Build verified: 1.2.5-1 nighty

          [root@localhost ~]# cat gitversionInfo.txt
          server commit:
          374ddee
          engine commit:
          4675816

          Reproduced the issue in 1.2.3-1 and verified fix in 1.2.5-1

          MariaDB [mytest]> select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from bug_cs
          -> WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);
          -----------------------------------------------+

          c sum(c) over() sum(c) over w1 sum(c) over w2

          -----------------------------------------------+

          2.05 10.53 10.53 2.05
          5.44 10.53 10.53 7.49
          3.04 10.53 10.53 10.53

          -----------------------------------------------+
          3 rows in set (0.080 sec)

          MariaDB [mytest]> select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from
          -> (select
          -> sum(c) as c
          -> from
          -> bug_cs
          -> ) t WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);
          ------------------------------------------------+

          c sum(c) over() sum(c) over w1 sum(c) over w2

          ------------------------------------------------+

          10.53 10.53 10.53 10.53

          ------------------------------------------------+
          1 row in set (0.015 sec)

          dleeyh Daniel Lee (Inactive) added a comment - Build verified: 1.2.5-1 nighty [root@localhost ~] # cat gitversionInfo.txt server commit: 374ddee engine commit: 4675816 Reproduced the issue in 1.2.3-1 and verified fix in 1.2.5-1 MariaDB [mytest] > select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from bug_cs -> WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row); ----- ------------- -------------- ---------------+ c sum(c) over() sum(c) over w1 sum(c) over w2 ----- ------------- -------------- ---------------+ 2.05 10.53 10.53 2.05 5.44 10.53 10.53 7.49 3.04 10.53 10.53 10.53 ----- ------------- -------------- ---------------+ 3 rows in set (0.080 sec) MariaDB [mytest] > select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from -> (select -> sum(c) as c -> from -> bug_cs -> ) t WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row); ------ ------------- -------------- ---------------+ c sum(c) over() sum(c) over w1 sum(c) over w2 ------ ------------- -------------- ---------------+ 10.53 10.53 10.53 10.53 ------ ------------- -------------- ---------------+ 1 row in set (0.015 sec)

          People

            dleeyh Daniel Lee (Inactive)
            Girod Emmanuel
            Votes:
            1 Vote for this issue
            Watchers:
            3 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.