Re: [BUGS] BUG #14648: counts for queries using array unnesting is incorrect

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [BUGS] BUG #14648: counts for queries using array unnesting is incorrect
Дата
Msg-id CAKFQuwbOPohNb=Ya_VsHGmL2-4t2-7F2MCMQGWQD+11O2JrgwA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14648: counts for queries using array unnesting is incorrect  (Sebastion Calbaza <sebastian.calbaza@hgdata.com>)
Ответы Re: [BUGS] BUG #14648: counts for queries using array unnesting is incorrect  (Sebastion Calbaza <sebastian.calbaza@hgdata.com>)
Список pgsql-bugs
Дерево обсуждения
On Fri, May 12, 2017 at 7:55 AM, Sebastion Calbaza <sebastian.calbaza@hgdata.com> wrote:
Thanks for replying.... I know it's complicated to follow it (even if formatted ) , but probably I did not emphasize enough that by using unnest(ids) in the second query, the distinct count for company is not computed correctly anymore.
Focusing on the used select clauses  is important, they are just distinct counts, it would have been logical to stay the same for both queries, ignoring the unnest(ids)(the data set is in the milions of rows so you need to take my word that the first query return the correct values :) ).

​In the first query the number of distinct companies ​is greater than the number of matched companies.  In the second the numbers are equal.  If the only difference between the two is the "unnest(ids)" then its presence is causing every unmatched company to be discarded from the result.  Since "SELECT unnest(null::text[])" is the empty set it would remove the corresponding row from your subquery output.  You might try writing the following which will convert the empty set to a NULL and thus not discard records.

(SELECT unnest(ids)) AS id, F.urlx as company ...

David J.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Sebastion Calbaza
Дата:
Сообщение: Re: [BUGS] BUG #14648: counts for queries using array unnesting is incorrect
Следующее
От: Vitaliy Gomenyuk
Дата:
Сообщение: Re: [BUGS] BUG #14635: Query is executed slower on hot standby slavedatabase then on master database