Re: BUG #14020: row_number() over(partition by order by) - weird behavior
От | David G. Johnston |
---|---|
Тема | Re: BUG #14020: row_number() over(partition by order by) - weird behavior |
Дата | |
Msg-id | CAKFQuwbWiSwSf42hd-h9MgU9CSicnurxv7sY607Uw1D6dLMW9g@mail.gmail.com обсуждение исходный текст |
Ответ на | BUG #14020: row_number() over(partition by order by) - weird behavior (b.yordanov2@gmail.com) |
Ответы |
Re: BUG #14020: row_number() over(partition by order by) - weird behavior
(Boyko Yordanov <b.yordanov2@gmail.com>)
|
Список | pgsql-bugs |
On Mon, Mar 14, 2016 at 1:43 PM, <b.yordanov2@gmail.com> wrote: > > db=3D# update offers_past_data a set position =3D 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 =3D b.id and a.feed =3D b.feed= and > a.position <> b.position; > UPDATE 0 > =E2=80=8BUpdating offers_past_data =E2=80=8B > > This should update every row in offers_past_data when its =E2=80=9Cpositi= on=E2=80=9D > changes. In the example above no changes were introduced since the last r= un > so nothing is updated (expected). > > db=3D# select count(*) from offers_testing where product =3D 2; > count > ------- > 99 > (1 row) > So there are 99 offers for product 2. > =E2=80=8BCounting offers_testing=E2=80=8B > Getting a single offer: > > db=3D# select id,grossprice from offers_testing where product =3D 2 limit= 1; > id | grossprice > ---------+------------ > 4127918 | 5000.00 > (1 row) > > =E2=80=8BCounting offers_testing=E2=80=8B Updating its grossprice: > > db=3D# update offers_testing set grossprice =3D 20 where id =3D 4127918; > UPDATE 1 > > =E2=80=8BUpdating offers_testing=E2=80=8B 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. > You haven't proven to us that a single row in offers_testing cannot match more than one row in offers_past_data. Assuming a 1-to-many situation the update count for offers_past_data can definitely be more than the number of rows returned by the sub-query. =E2=80=8B=E2=80=8B > db=3D# update offers_past_data a set position =3D 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 =3D b.id and a.feed =3D 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=3D# update offers_past_data a set position =3D 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 =3D b.id and a.feed =3D 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? =E2=80=8BIts likely data related, not a bug. Using the "UPDATE ... RETURNING *" form should provide good insight. Specifically, look for all rows having the same (id, feed) pair. Also, "ORDER BY grossprice" seems inadequate. The potential for duplicates here - which would then make the assignment of row numbers within the product partition random - is non-zero and is a quite likely source of your problem - along with the probable one-to-many relationship between offers_testing and offers_past_data. David J. =E2=80=8B
В списке pgsql-bugs по дате отправления:
Предыдущее
От: b.yordanov2@gmail.comДата:
Сообщение: BUG #14020: row_number() over(partition by order by) - weird behavior
Следующее
От: Boyko YordanovДата:
Сообщение: Re: BUG #14020: row_number() over(partition by order by) - weird behavior