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

You are correct, this is how it behaves.
For my dev mind (probably for others ) this was pretty non intuitive. In
the end I used array_length and sum to compute the count.

Still, is this the intended behaviour (I guess so from a mathematical
point of view ?? )? If it is I can argue that is not too intuitive and
adding a doc note near the unnest function description would be good.

Thanks a lot,
Sebi C.

On Fri, May 12, 2017 at 6:46 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>