BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
От | PG Bug reporting form |
---|---|
Тема | BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY |
Дата | |
Msg-id | 151939899974.1461.9411971793110285476@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15082: PostgreSQL 11 dev bug with window range partition by QUERY
|
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления:
Следующее
От: PG Bug reporting formДата:
Сообщение: BUG #15083: [54000] ERROR: total size of jsonb array elements exceedsthe maximum of 268435455 bytes