BUG #14020: row_number() over(partition by order by) - weird behavior
От | b.yordanov2@gmail.com |
---|---|
Тема | BUG #14020: row_number() over(partition by order by) - weird behavior |
Дата | |
Msg-id | 20160314204348.8902.26168@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #14020: row_number() over(partition by order by) -
weird behavior
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 14020 Logged by: Boyko Email address: b.yordanov2@gmail.com PostgreSQL version: 9.5.0 Operating system: CentOS 6.4 Description: Hi, db=# \d+ offers_past_data; Table "public.offers_past_data" Column | Type | Modifiers | Storage | Stats target | Description ----------+---------+-----------+---------+--------------+------------- id | integer | not null | plain | | feed | integer | not null | plain | | position | integer | not null | plain | | db=# \d+ offers_testing; Table "public.offers_testing" Column | Type | Modifiers | Storage | Stats target | Description --------------------+-----------------------------+-----------------------------------------------------+----------+--------------+------------- id | bigint | not null default nextval('offers_id_seq'::regclass) | plain | | grossprice | numeric(11,2) | | main | | feed | integer | not null | plain | | product | integer | | plain | | db=# update offers_past_data a set position = b.position from (select id, feed, row_number() over(partition by product order by grossprice asc) as position from offers_testing) b where a.id = b.id and a.feed = b.feed and a.position <> b.position; UPDATE 0 This should update every row in offers_past_data when its âpositionâ changes. In the example above no changes were introduced since the last run so nothing is updated (expected). db=# select count(*) from offers_testing where product = 2; count ------- 99 (1 row) So there are 99 offers for product 2. Getting a single offer: db=# select id,grossprice from offers_testing where product = 2 limit 1; id | grossprice ---------+------------ 4127918 | 5000.00 (1 row) Updating its grossprice: db=# update offers_testing set grossprice = 20 where id = 4127918; UPDATE 1 Now when executing the first query again I expect that no more than 99 rows get updated in offers_past_data since this is the maximum amount of positions that would be affected by offer 4127918 grossprice change. db=# update offers_past_data a set position = b.position from (select id, feed, row_number() over(partition by product order by grossprice asc) as position from offers_testing) b where a.id = b.id and a.feed = b.feed and a.position <> b.position; UPDATE 104 104 rows get updated. Executing the same query again a few minutes later (no changes meanwhile in either table): db=# update offers_past_data a set position = b.position from (select id, feed, row_number() over(partition by product order by grossprice asc) as position from offers_testing) b where a.id = b.id and a.feed = b.feed and a.position <> b.position; UPDATE 28058 This time it updates 28058 rows. This is a test environment and nothing reads or writes to these tables. Is this a bug or am I missing something obvious? Regards, Boyko
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: BUG #14020: row_number() over(partition by order by) - weird behavior