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

value forced to null with multiplication of window function expression

    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: