Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.4
-
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Test table / data:
{code} create table win_test (name varchar(10), cnt int); insert into win_test values ('Fred', 23), ('Fred', 35), ('Joe', 10); {code} Query that fails, note values for pct_of_total are null: {code} 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 | +------+---------+--------------+ {code} removing the 100 * from the pct_of_total expression results in correct values, however as a fraction rather than the desired percentage values: {code} 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 | +------+---------+--------------+ {code} The original query works on columnStore correctly and is a pattern for calculating percentage of totals. |
Test table / data:
{code} create table win_test (name varchar(10), cnt int); insert into win_test values ('Fred', 23), ('Fred', 35), ('Joe', 10); {code} Query that fails, note values for pct_of_total are null: {code} 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 | +------+---------+--------------+ {code} removing the 100 * from the pct_of_total expression results in correct values, however as a fraction rather than the desired percentage values: {code} 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 | +------+---------+--------------+ {code} 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. |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Assignee | Igor Babaev [ igor ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Igor Babaev [ igor ] |
| Fix Version/s | 10.2.5 [ 22117 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 80084 ] | MariaDB v4 [ 151841 ] |
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 |+------+---------+--------------+