pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
2+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2018-02-23 9fe802c818 Fix brown-paper-bag bug in commit 0a459cec96d3856f476c2db298c6b52f592894e8.
* BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
@ 2018-02-23 15:16 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2018-02-23 15:16 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: digoal@126.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
@ 2018-02-23 20:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2018-02-23 20:12 UTC (permalink / raw)
To: digoal@126.com; +Cc: pgsql-bugs@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-02-23 20:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-23 15:16 BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY PG Bug reporting form <noreply@postgresql.org>
2018-02-23 20:12 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox