The following bug has been logged on the website:
Bug reference: 14648
Logged by: Sebastian Calbaza
Email address: [email protected]
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
