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

value forced to null with multiplication of window function expression

    XMLWordPrintable

    Details

      Description

      Test table / data:

      create table win_test (name varchar(10), cnt int);
      insert into win_test values ('Fred', 23), ('Fred', 35), ('Joe', 10);
      

      Query that fails, note values for pct_of_total are null:

      select q.name, q.row_cnt, round(100 * (q.row_cnt / sum(q.row_cnt) over (order by q.name rows between unbounded preceding and unbounded following)),2) pct_of_total from (select name, count(*) row_cnt, sum(cnt) sum_cnt from win_test group by 1) q;
      +------+---------+--------------+
      | name | row_cnt | pct_of_total |
      +------+---------+--------------+
      | Fred |       2 | NULL         |
      | Joe  |       1 | NULL         |
      +------+---------+--------------+
      

      removing the 100 * from the pct_of_total expression results in correct values, however as a fraction rather than the desired percentage values:

      select q.name, q.row_cnt, round((q.row_cnt / sum(q.row_cnt) over (order by q.name rows between unbounded preceding and unbounded following)),2) pct_of_total from (select name, count(*) row_cnt, sum(cnt) sum_cnt from win_test group by 1) q;
      +------+---------+--------------+
      | name | row_cnt | pct_of_total |
      +------+---------+--------------+
      | Fred |       2 | 0.67         |
      | Joe  |       1 | 0.33         |
      +------+---------+--------------+
      

      The original query works on columnStore 1.0.7 (which has our own window function implementation on top of 10.1) correctly and is a pattern for calculating percentage of totals.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            dthompson David Thompson (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: