Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
1.1.7
-
None
Description
Filtering does not work properly in the columnstore when there is an embedded query using window functions.
See the example below: we are trying to compute a moving average and then filter out NULL values. The query works well in InnoDB or TokuDB. But not with the columnstore which keeps the NULL values. We have seen the same behaviour in version 1.2.3.
CREATE TABLE `bug_cs` (
`a` tinyint unsigned,
`b` decimal(5,2) DEFAULT NULL
) ENGINE=columnstore;
INSERT INTO `bug_cs`
(`a`,
`b`)
VALUES (1,2.05), (2,5.44),(3,3.04), (4,4.03),(5,2.45),(6,9.02),(7,4.23),(8,3.02),(9,2.84),(10,8.3);
select * from
(
SELECT
`a`,
case when ROW_NUMBER() OVER `w_1`>3 then AVG(`b`) OVER `w_0` else null end as `mavg`
FROM bug_cs
WINDOW
`w_1` as ( ORDER BY `a` ASC),
`w_0` as ( ORDER BY `a` ASC rows between 3 preceding and current row)
) t
where `mavg` is not null;