[BUGS] BUG #14648: counts for queries using array unnesting is incorrect
От | sebastian.calbaza@hgdata.com |
---|---|
Тема | [BUGS] BUG #14648: counts for queries using array unnesting is incorrect |
Дата | |
Msg-id | 20170512130033.1796.93117@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: [BUGS] BUG #14648: counts for queries using array unnesting is incorrect
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 14648 Logged by: Sebastian Calbaza Email address: sebastian.calbaza@hgdata.com PostgreSQL version: 9.6.2 Operating system: Ubuntu 14.04 AWS Description: Below is a query that we are using to calculate some counts: * first version of the query lacks ```unnest(ids) as id,```,but second one has it * ```companies``` count value is incorrect for the second one, first query has the correct value ``` mydb=# select count(company) as available,count(distinct matchedCompany) as matchedCompanies,count(distinct company) as companies from ( SELECT F.urlx as company, CB_RDPPKLCPQDVACOMSTEFANHGDATACOM_agg.url as matchedCompany from (select unnest(urls) as urlxfrom hg_data_discovery_2017_04_10.GroupedFirmographics where ( (TRUE AND TRUE AND revenueRangeMin >= 1 AND employeesRangeMin >= 1 AND revenueRangeMax <= 1783792664 AND employeesRangeMax <= 4999000 ) OR FALSE ) ) as I inner join ( select unnest(urls) as urlx from hg_data_discovery_2017_04_10.GroupedInstallsWithoutDateSignalScore where productId IN (562) and signalScoreId IN (1,2,3) ) as F using(urlx) left outer join CB_RDPPKLCPQDVACOMSTEFANHGDATACOM_aggon CB_RDPPKLCPQDVACOMSTEFANHGDATACOM_agg.url=F.urlx where TRUE and TRUE ) as P ;available | matchedcompanies| companies -----------+------------------+----------- 496493 | 28503 | 495799 (1 row) Time: 7974.053 ms mydb=# select count(id) as people, count(company) as available,count(distinct matchedCompany) as matchedCompanies,count(distinct company) as companies from ( SELECT unnest(ids) as id, F.urlx as company, CB_RDPPKLCPQDVACOMSTEFANHGDATACOM_agg.url as matchedCompany from (select unnest(urls) as urlxfrom hg_data_discovery_2017_04_10.GroupedFirmographics where ( (TRUE AND TRUE AND revenueRangeMin >= 1 AND employeesRangeMin >= 1 AND revenueRangeMax <= 1783792664 AND employeesRangeMax <= 4999000 ) OR FALSE ) ) as I inner join ( select unnest(urls) as urlx from hg_data_discovery_2017_04_10.GroupedInstallsWithoutDateSignalScore where productId IN (562) and signalScoreId IN (1,2,3) ) as F using(urlx) left outer join CB_RDPPKLCPQDVACOMSTEFANHGDATACOM_aggon CB_RDPPKLCPQDVACOMSTEFANHGDATACOM_agg.url=F.urlx where TRUE and TRUE ) as P ;people | available| matchedcompanies | companies --------+-----------+------------------+-----------689905 | 689905 | 28503 | 28503 ``` -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: [HACKERS] [BUGS] Concurrent ALTER SEQUENCE RESTART Regression
Следующее
От: "K S, Sandhya (Nokia - IN/Bangalore)"Дата:
Сообщение: Re: [BUGS] Crash observed during the start of the Postgres process