Re: BUG #8629: Strange resultset when using CTE or a subselect

On Tue, Nov 26, 2013 at 1:50 AM, jonathan.camile
<jonathan.camile@gmail.com>wrote:

>
> Here a query to create table with which I can reproduce this behavior :
>
> CREATE TABLE public.testme AS SELECT generate_series(134800, 348008) as id,
> trunc(random() * 9 + 1) as status;
>
> Then if you play with the following query, you will reproduce it.
>
> WITH filtred_test AS (
>     SELECT c.id
>     FROM public.testme c
>     WHERE c.status = ANY ('{5}')
> )
> SELECT mytest.id, mytest.status
> FROM  public.testme mytest
> WHERE mytest.id IN (SELECT filtred_test.id FROM filtred_test)
> ORDER BY mytest.status DESC
> OFFSET 35
> LIMIT 10
>
> I always have the same last result regardless of the offset or the limit I
> use.
>


I cannot reproduce your problem in 9.2.5 nor 9.3.1. Rerunning the query
with different OFFSET values gives me different results each time.