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