"Dan Halbert" <halbert@xxxxxxxxxxx> 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 - Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
