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

On Fri, May 12, 2017 at 7:55 AM, Sebastion Calbaza <
sebastian(dot)calbaza(at)hgdata(dot)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.