We're updating the issue view to help you get more done. 

Window function produces incorrect value

Description

1 2 3 4 create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (part_id int, pk int, a int); insert into t2 select if(a<5, 0, 1), a, if(a<5, NULL, 1) from t0;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select * from t2; +---------+------+------+ | part_id | pk | a | +---------+------+------+ | 0 | 0 | NULL | | 0 | 1 | NULL | | 0 | 2 | NULL | | 0 | 3 | NULL | | 0 | 4 | NULL | | 1 | 5 | 1 | | 1 | 6 | 1 | | 1 | 7 | 1 | | 1 | 8 | 1 | | 1 | 9 | 1 | +---------+------+------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select part_id, pk, a, count(a) over (partition by part_id order by pk rows between 1 preceding and 1 following) as CNT from t2; +---------+------+------+-----+ | part_id | pk | a | CNT | +---------+------+------+-----+ | 0 | 0 | NULL | 0 | | 0 | 1 | NULL | 0 | | 0 | 2 | NULL | 0 | | 0 | 3 | NULL | 0 | | 0 | 4 | NULL | 0 | | 1 | 5 | 1 | 0 | | 1 | 6 | 1 | 0 | | 1 | 7 | 1 | 0 | | 1 | 8 | 1 | 0 | | 1 | 9 | 1 | 0 | +---------+------+------+-----+

In other settings the value of window can be different.

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Labels

None

External issue ID

None

External issue ID

None

Components

Fix versions

Priority

Major