Horton

Thread

Commits

  1. Fix brown-paper-bag bug in commit 0a459cec96d3856f476c2db298c6b52f592894e8.

  1. BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY

    The Post Office <noreply@postgresql.org> — 2018-02-23T15:16:39Z

    The following bug has been logged on the website:

Bug reference:      15082
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: Unsupported/Unknown
Operating system:   CentOS 7.4 x64
Description:        

```
create table t(
  sid int,     -- 学号
  sub int2,    -- 科目
  des text,    -- 科目描述
  score float4    -- 分数
);


insert into t values 
  (1, 1, '语文', 81),
  (2, 1, '语文', 71),
  (3, 1, '语文', 99),
  (4, 1, '语文', 100),
  (5, 1, '语文', 32),
  (6, 1, '语文', 89),
  (7, 1, '语文', 90.5),
  (1, 2, '数学', 81),
  (2, 2, '数学', 96),
  (3, 2, '数学', 78),
  (4, 2, '数学', 90),
  (5, 2, '数学', 12),
  (6, 2, '数学', 97),
  (7, 2, '数学', 99.5),
  (1, 3, '英语', 100),
  (2, 3, '英语', 95),
  (3, 3, '英语', 95),
  (4, 3, '英语', 95),
  (5, 3, '英语', 56),
  (6, 3, '英语', 87),
  (7, 3, '英语', 93),
  (1, 4, '物理', 60),
  (2, 4, '物理', 60),
  (3, 4, '物理', 65),
  (4, 4, '物理', 65),
  (5, 4, '物理', 65),
  (6, 4, '物理', 71),
  (7, 4, '物理', 71)
;


select 
  *, 
  sum(score) over w1, 
  avg(score) over w1, 
  first_value(score) over w1, 
  last_value(score) over w1 
from t 
  window w1 as (partition by sub order by score::int range between 1
preceding and 6 following) 
order by sub, score;


 sid | sub | des  | score | sum |         avg         | first_value |
last_value 
-----+-----+------+-------+-----+---------------------+-------------+------------
   5 |   1 | 语文 |    32 | 562 | 80.2857142857142857 |          32 |       
100
   2 |   1 | 语文 |    71 | 562 | 80.2857142857142857 |          32 |       
100
   1 |   1 | 语文 |    81 | 562 | 80.2857142857142857 |          32 |       
100
   6 |   1 | 语文 |    89 | 562 | 80.2857142857142857 |          32 |       
100
   7 |   1 | 语文 |    90 | 562 | 80.2857142857142857 |          32 |       
100
   3 |   1 | 语文 |    99 | 562 | 80.2857142857142857 |          32 |       
100
   4 |   1 | 语文 |   100 | 562 | 80.2857142857142857 |          32 |       
100
   5 |   2 | 数学 |    12 | 554 | 79.1428571428571429 |          12 |       
100
   3 |   2 | 数学 |    78 | 554 | 79.1428571428571429 |          12 |       
100
   1 |   2 | 数学 |    81 | 554 | 79.1428571428571429 |          12 |       
100
   4 |   2 | 数学 |    90 | 554 | 79.1428571428571429 |          12 |       
100
   2 |   2 | 数学 |    96 | 554 | 79.1428571428571429 |          12 |       
100
   6 |   2 | 数学 |    97 | 554 | 79.1428571428571429 |          12 |       
100
   7 |   2 | 数学 |   100 | 554 | 79.1428571428571429 |          12 |       
100
   5 |   3 | 英语 |    56 | 621 | 88.7142857142857143 |          56 |       
100
   6 |   3 | 英语 |    87 | 621 | 88.7142857142857143 |          56 |       
100
   7 |   3 | 英语 |    93 | 621 | 88.7142857142857143 |          56 |       
100
   3 |   3 | 英语 |    95 | 621 | 88.7142857142857143 |          56 |       
100
   4 |   3 | 英语 |    95 | 621 | 88.7142857142857143 |          56 |       
100
   2 |   3 | 英语 |    95 | 621 | 88.7142857142857143 |          56 |       
100
   1 |   3 | 英语 |   100 | 621 | 88.7142857142857143 |          56 |       
100
   1 |   4 | 物理 |    60 | 457 | 65.2857142857142857 |          60 |        
71
   2 |   4 | 物理 |    60 | 457 | 65.2857142857142857 |          60 |        
71
   3 |   4 | 物理 |    65 | 457 | 65.2857142857142857 |          60 |        
71
   4 |   4 | 物理 |    65 | 457 | 65.2857142857142857 |          60 |        
71
   5 |   4 | 物理 |    65 | 457 | 65.2857142857142857 |          60 |        
71
   6 |   4 | 物理 |    71 | 457 | 65.2857142857142857 |          60 |        
71
   7 |   4 | 物理 |    71 | 457 | 65.2857142857142857 |          60 |        
71
(28 rows)


select 
  *, 
  sum(score) over w1, 
  avg(score) over w1, 
  first_value(score) over w1, 
  last_value(score) over w1 
from t 
  window w1 as (order by score::int range between 1 preceding and 6
following) 
order by score;

 sid | sub | des  | score | sum |         avg         | first_value |
last_value 
-----+-----+------+-------+-----+---------------------+-------------+------------
   5 |   2 | 数学 |    12 |  12 | 12.0000000000000000 |          12 |        
12
   5 |   1 | 语文 |    32 |  32 | 32.0000000000000000 |          32 |        
32
   5 |   3 | 英语 |    56 | 176 | 58.6666666666666667 |          56 |        
60
   1 |   4 | 物理 |    60 | 315 | 63.0000000000000000 |          60 |        
65
   2 |   4 | 物理 |    60 | 315 | 63.0000000000000000 |          60 |        
65
   3 |   4 | 物理 |    65 | 408 | 68.0000000000000000 |          65 |        
71
   4 |   4 | 物理 |    65 | 408 | 68.0000000000000000 |          65 |        
71
   5 |   4 | 物理 |    65 | 408 | 68.0000000000000000 |          65 |        
71
   6 |   4 | 物理 |    71 | 213 | 71.0000000000000000 |          71 |        
71
   7 |   4 | 物理 |    71 | 213 | 71.0000000000000000 |          71 |        
71
   2 |   1 | 语文 |    71 | 213 | 71.0000000000000000 |          71 |        
71
   3 |   2 | 数学 |    78 | 240 | 80.0000000000000000 |          78 |        
81
   1 |   1 | 语文 |    81 | 249 | 83.0000000000000000 |          81 |        
87
   1 |   2 | 数学 |    81 | 249 | 83.0000000000000000 |          81 |        
87
   6 |   3 | 英语 |    87 | 449 | 89.8000000000000000 |          87 |        
93
   6 |   1 | 语文 |    89 | 647 | 92.4285714285714286 |          89 |        
95
   7 |   1 | 语文 |    90 | 743 | 92.8750000000000000 |          89 |        
96
   4 |   2 | 数学 |    90 | 743 | 92.8750000000000000 |          89 |        
96
   7 |   3 | 英语 |    93 | 670 | 95.7142857142857143 |          93 |        
99
   3 |   3 | 英语 |    95 | 877 | 97.4444444444444444 |          95 |       
100
   2 |   3 | 英语 |    95 | 877 | 97.4444444444444444 |          95 |       
100
   4 |   3 | 英语 |    95 | 877 | 97.4444444444444444 |          95 |       
100
   2 |   2 | 数学 |    96 | 877 | 97.4444444444444444 |          95 |       
100
   6 |   2 | 数学 |    97 | 592 | 98.6666666666666667 |          96 |       
100
   3 |   1 | 语文 |    99 | 399 | 99.7500000000000000 |          99 |       
100
   7 |   2 | 数学 |   100 | 399 | 99.7500000000000000 |          99 |       
100
   4 |   1 | 语文 |   100 | 399 | 99.7500000000000000 |          99 |       
100
   1 |   3 | 英语 |   100 | 399 | 99.7500000000000000 |          99 |       
100
(28 rows)
```

when i use frame clause(range), it's only right when don't use partition by,
and use the whole rows as one partition.

is it a bug?

digoal,
best regards.

  2. Re: BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-02-23T20:12:31Z

    =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> when i use frame clause(range), it's only right when don't use partition by,
> and use the whole rows as one partition.
> is it a bug?

Yup.  Fixed, thanks.

			regards, tom lane