Re: select distinct in a subquery bug/problem

"Dan Halbert" <halbert(at)halwitz(dot)org> writes:
> 1. select count(t1_id) from t1 where t1_id not in (select distinct t1_id from t2 limit 1103) ==> 13357 [CORRECT result]
> 2. select count(t1_id) from t1 where t1_id not in (select distinct t1_id from t2 limit 1104) ==> 0 [WRONG result; should be close to 13357]

I'm betting the 1104'th row in t2 is a null. NOT IN generally doesn't
do what people expect when there are nulls in the sub-select ... but
it is acting per spec.

The apparent dependency on which plan is chosen is illusory and stems
from the fact that "select distinct ... limit" gives you an unspecified
set of rows. I think the indexscan/unique plan would produce nulls
last, so you'd not see them as long as the limit was less than the
number of distinct values ... but if the distinct is done via hash
aggregation, the null entry could come out much earlier.

regards, tom lane