[MCOL-3304] Window functions in queries with embedded selects produce bad numbers Created: 2019-05-10  Updated: 2019-10-28  Resolved: 2019-06-20

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.2.3
Fix Version/s: 1.2.5

Type: Bug Priority: Major
Reporter: Emmanuel Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Sprint: 2019-05, 2019-06

 Description   

See the following example below:
The first query produces correct numbers.
In the second query, all the sums over windows are 100 times too small!!
If we create an InnoDB table, there is no issue

CREATE TABLE `bug_cs` (
`c` decimal(5,2) DEFAULT NULL
) ENGINE=columnstore;

INSERT INTO `bug_cs`
(`c`)
VALUES (2.05), (5.44),(3.04);

select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from bug_cs
WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);

select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from
(select
sum(c) as c
from
bug_cs
) t WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);

 Comments   
Comment by David Hall (Inactive) [ 2019-05-24 ]

This is a regression in 1.2

Comment by David Hall (Inactive) [ 2019-05-29 ]

PR #117 for regression test

Comment by David Hall (Inactive) [ 2019-05-29 ]

There is a regression test added

Comment by Daniel Lee (Inactive) [ 2019-06-20 ]

Build verified: 1.2.5-1 nighty

[root@localhost ~]# cat gitversionInfo.txt
server commit:
374ddee
engine commit:
4675816

Reproduced the issue in 1.2.3-1 and verified fix in 1.2.5-1

MariaDB [mytest]> select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from bug_cs
-> WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);
-----------------------------------------------+

c sum(c) over() sum(c) over w1 sum(c) over w2

-----------------------------------------------+

2.05 10.53 10.53 2.05
5.44 10.53 10.53 7.49
3.04 10.53 10.53 10.53

-----------------------------------------------+
3 rows in set (0.080 sec)

MariaDB [mytest]> select c,sum(c) over(), sum(c) over w1, sum(c) over w2 from
-> (select
-> sum(c) as c
-> from
-> bug_cs
-> ) t WINDOW `w1` as (),`w2` as (rows between unbounded preceding and current row);
------------------------------------------------+

c sum(c) over() sum(c) over w1 sum(c) over w2

------------------------------------------------+

10.53 10.53 10.53 10.53

------------------------------------------------+
1 row in set (0.015 sec)

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