The following bug has been logged on the website:
Bug reference: 8629
Logged by: Jonathan Camile
Email address: jonathan.camile@gmail.com
PostgreSQL version: 9.2.4
Operating system: Ubuntu 10.04.4 LTS
Description:
Hey folks!
I have a bit of an issue with a query and I don't understand why.
It might be not very elegant but here it is, when I use the following query
the last result will always be the same whatsoever the values of LIMIT and
OFFSET.
```
WITH filtred_table AS (
SELECT c.id
FROM my_table t
WHERE t.enabled = true
AND (t.hdata->'field')::integer = ANY ('{16788}')
)
SELECT my_table.id
FROM my_table
WHERE mycontract.id IN (SELECT filtred_table.id FROM filtred_table)
ORDER BY my_table.hdata->'field' DESC
LIMIT 5
OFFSET 0
```
Returns
```
392200574
367046368
375126026
407430954
385242163
```
```
WITH filtred_table AS (
SELECT c.id
FROM my_table t
WHERE t.enabled = true
AND (t.hdata->'field')::integer = ANY ('{16788}')
)
SELECT my_table.id
FROM my_table
WHERE mycontract.id IN (SELECT filtred_table.id FROM filtred_table)
ORDER BY my_table.hdata->'field' DESC
LIMIT 5
OFFSET 5
```
Returns
```
186939712
350754246
408507328
381550486
385242163
```
If I don't `ORDER BY` the field used to filter the issue doesn't happen.
Thanks.