[MCOL-393] window function in aggregate expression has wrong results Created: 2016-11-07  Updated: 2019-07-11  Resolved: 2019-07-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 1.0.4
Fix Version/s: 1.2.3

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

Epic Link: ColumnStore Compatibility Improvements

 Description   

With the following table:

create table opportunities (id int, accountName varchar(20), name varchar(128), owner varchar(7), amount decimal(10,2), closeDate date, stageName varchar(11)) engine=columnstore;

the window query below which should in theory work gives wrong results for pct_won:

select owner, count(*) wins, count(*) /( sum(count(*)) over ()) pct_won from opportunities group by 1;

Rewriting the query to have the window function isolated in a subquery works around the issue:

 select t.owner, t.wins, t.wins / t.total pct_won from (select owner, count(*) wins, sum(count(*)) over () total from opportunities group by 1) t;



 Comments   
Comment by David Hall (Inactive) [ 2019-07-11 ]

This bug was fixed as part of the long double efforts of SUM() and AVG() MCOL-1822. This bug was specifically fixed as part of that effort. We just hadn't seen the connection to this MCOL.

Generated at Thu Feb 08 02:20:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.