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

          Transition Time In Source Status Execution Times
          Elena Stepanova made transition -
          Open Confirmed
          		1d 16h 36m 1
          Igor Babaev (Inactive) made transition -
          Confirmed In Progress
          		3d 12h 19m 1
          Igor Babaev (Inactive) made transition -
          In Progress In Review
          		19h 29m 1
          Vicențiu Ciorbaru made transition -
          In Review Stalled
          		16h 33m 1
          Igor Babaev (Inactive) made transition -
          Stalled Closed
          		12h 48m 1

          People

            igor Igor Babaev (Inactive)
            dthompson David Thompson (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.