Re: select distinct in a subquery bug/problem

On 8/11/2012 2:21 PM, Raymond O'Donnell wrote:
> On 11/08/2012 04:32, Dan Halbert wrote:
>> 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]
> Does it make a difference if you include an ORDER BY in the subquery?
> AIUI, the particular result set from the subquery is indeterminate (in
> theory anyway) without it.
>
> Ray.
>
Yes, it's artificial. The real problem, as Tom Lane hinted, is that NOT
IN (..., NULL, ...) returns NULL and messes up the result, so if the
SELECT produces any NULL's, the query doesn't do what I want. So I
needed to not use NOT IN or make sure there are no NULL's

Dan