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

jonathan.camile wrote
> 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

Strange but not a bug - though I haven't tested it myself to prove out
anything.

You are ordering by a single field that, by definition, has the same value
for every record that the LIMIT/OFFSET sees. The ORDER BY is effectively a
no-op in this situation and the order of your output is going to be random.
Why it just happens that the same record is always in your random output I
have no clue but it is your query that is lacking here - not PostgreSQL.
You need a secondary order by field, like ID, if you want to guarantee that
different ranges provide different rows.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8629-Strange-resultset-when-using-CTE-or-a-subselect-tp5780187p5780342.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.