[MDEV-12336] value forced to null with multiplication of window function expression Created: 2017-03-22  Updated: 2017-06-28  Resolved: 2017-03-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2.4
Fix Version/s: 10.2.5

Type: Bug Priority: Major
Reporter: David Thompson (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 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.

 Comments   
Comment by David Thompson (Inactive) [ 2017-03-22 ]

Another workaround is to remove the brackets after the 100 *:

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 | 66.67        |
 
| Joe  |       1 | 33.33        |
 
+------+---------+--------------+

Comment by Vicențiu Ciorbaru [ 2017-03-29 ]

OK to push

Comment by Igor Babaev [ 2017-03-30 ]

The fix for this bug was pushed into the 10.2 tree.

Generated at Thu Feb 08 07:56:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.