ClickHouse: wrong result in window function without ORDER BY section

SELECT
   gr,
   str,
   count(*) OVER (PARTITION BY gr) a,
   sum(1)   OVER (PARTITION BY gr) b,
   count(*) OVER (PARTITION BY gr ORDER BY str DESC) c,   
   sum(1)   OVER (PARTITION BY gr ORDER BY str DESC) d
from  (select number %3 gr,  'numb'||toString(number) str from numbers(9))
order by gr, str;

┌─gr─┬─str───┬─a─┬─b─┬─c─┬─d─┐
│  0 │ numb0 │ 3 │ 3 │ 3 │ 3 │
│  0 │ numb3 │ 2 │ 2 │ 2 │ 2 │
│  0 │ numb6 │ 1 │ 1 │ 1 │ 1 │
│  1 │ numb1 │ 3 │ 3 │ 3 │ 3 │
│  1 │ numb4 │ 2 │ 2 │ 2 │ 2 │
│  1 │ numb7 │ 1 │ 1 │ 1 │ 1 │
│  2 │ numb2 │ 3 │ 3 │ 3 │ 3 │
│  2 │ numb5 │ 2 │ 2 │ 2 │ 2 │
│  2 │ numb8 │ 1 │ 1 │ 1 │ 1 │
└────┴───────┴───┴───┴───┴───┘


expected (PG):

SELECT
   gr,
   str,
   count(*) OVER (PARTITION BY gr) a,
   sum(1)   OVER (PARTITION BY gr) b,
   count(*) OVER (PARTITION BY gr ORDER BY str DESC) c,   
   sum(1)   OVER (PARTITION BY gr ORDER BY str DESC) d
FROM
(SELECT generate_series%3 gr, 'numb'||generate_series str FROM generate_series(0,8)) t
order by gr, str

 gr |  str  | a | b | c | d
----+-------+---+---+---+---
  0 | numb0 | 3 | 3 | 3 | 3
  0 | numb3 | 3 | 3 | 2 | 2
  0 | numb6 | 3 | 3 | 1 | 1
  1 | numb1 | 3 | 3 | 3 | 3
  1 | numb4 | 3 | 3 | 2 | 2
  1 | numb7 | 3 | 3 | 1 | 1
  2 | numb2 | 3 | 3 | 3 | 3
  2 | numb5 | 3 | 3 | 2 | 2
  2 | numb8 | 3 | 3 | 1 | 1
(9 rows)

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 15 (5 by maintainers)

Most upvoted comments

will ch support ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWS etc clauses?

If it’s god’s will it will happen