Re: BUG #8629: Strange resultset when using CTE or a subselect - Mailing list pgsql-bugs

Hi David,

Thanks for your reply.

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.
If you have any clue about what's wrong I'll be very glad.

By advance, thanks.


David Johnston wrote
>
> 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...


David Johnston wrote
>
> 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...


David Johnston wrote
>
> 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-tp5780187p5780294.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.