Re: select distinct in a subquery bug/problem - Mailing list pgsql-general

On 11/08/2012 04:32, Dan Halbert wrote:
> In version 9.1.4-0ubuntu12.04:
>
>
>
> Hi - I am getting wrong answers from a certain kind of query, and have
> narrowed it down to a change in the query plan between two similar
> queries. The two queries below use different query plans, and generate
> different results, one of which is completely wrong.
>
>
>
> 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.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie