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

jonathan.camile wrote
> The following bug has been logged on the website:
>
> Bug reference: 8629
> Logged by: Jonathan Camile
> Email address:

> jonathan.camile@

> 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.

When debugging ORDER BY/LIMIT it is good practice to output the columns
being ordered, and others if applicable, and omit the LIMIT (or makes it
considerably larger than needed) to see what raw table data the LIMIT clause
is seeing. At minimum you need; "SELECT my_table.id,
my_table.hdata->'field' FROM ..." for the output and either remove the limit
or show at least 30 records initially.

The small subset of the data you have provided is insufficient to determine
whether you are making bad assumptions about your data or whether there is
actually a problem. The fact that it is not self-contained makes debugging
difficult as well.

Note that LIMIT and OFFSET can appear in either order. While there are some
parts of a select statement for which the syntax dictates an order these do
not appear to be in the group. If they could not then LIMIT 5 OFFSET 100
would make no sense...

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