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