Re: Issue with NULLS LAST, with postgres_fdw sort pushdown

Thanks Ashutosh. Retested the issue after applying given patch,It is fine
now.

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On Wed, Mar 2, 2016 at 2:35 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

> Thanks Rajkumar for your report. Let me know if the attached patch fixes
> the issue.
>
> The code did not add NULL LAST clause the case when pk_nulls_first is
> false in pathkey. PFA the fix for the same. I have also added few tests to
> postgres_fdw.sql for few combinations of asc/desc and nulls first/last.
>
> On Mon, Feb 29, 2016 at 3:49 PM, Rajkumar Raghuwanshi <
> rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
>
>> Hi,
>>
>> I am testing postgres_fdw sort pushdown feature for PostgreSQL 9.6 DB,
>> and I observed below issue.
>>
>> *Observation: *If giving nulls last option with the order by clause as
>> 'desc nulls last', remote query is not considering nulls last and giving
>> wrong result in 9.6 version. while in 9.5 it is giving proper result.
>>
>> for testing, I have a table "fdw_sort_test" in foreign server for which
>> postgres_fdw, foreign table created in local server.
>>
>> db2=# select * from fdw_sort_test ;
>> id | name
>> ----+------
>> 1 | xyz
>> 3 |
>> 2 | abc
>> 4 | pqr
>> (4 rows)
>>
>> on version 9.6 :
>>
>> db1=# select * from fdw_sort_test order by name
>> desc nulls last;
>> id | name
>> ----+------
>> 3 |
>> 1 | xyz
>> 4 | pqr
>> 2 | abc
>> (4 rows)
>>
>> db1=# explain verbose select * from fdw_sort_test
>> order by name desc nulls last;
>> QUERY
>> PLAN
>> ------------------------------
>> --------------------------------------------------
>> Foreign Scan on public.fdw_sort_test
>> (cost=100.00..129.95 rows=561 width=122)
>> Output: id, name
>> Remote SQL: SELECT id, name FROM
>> public.fdw_sort_test ORDER BY name DESC
>> (3 rows)
>>
>>
>> on version 9.5 :
>> db1=# select * from fdw_sort_test order by name
>> desc nulls last;
>> id | name
>> ----+------
>> 1 | xyz
>> 4 | pqr
>> 2 | abc
>> 3 |
>> (4 rows)
>>
>> db1=# explain verbose select * from fdw_sort_test
>> order by name desc nulls last;
>> QUERY
>> PLAN
>> ------------------------------
>> --------------------------------------------------------
>> Sort (cost=152.44..153.85 rows=561 width=122)
>> Output: id, name
>> Sort Key: fdw_sort_test.name DESC NULLS LAST
>> -> Foreign Scan on public.fdw_sort_test
>> (cost=100.00..126.83 rows=561 width=122)
>> Output: id, name
>> Remote SQL: SELECT id, name FROM
>> public.fdw_sort_test
>>
>> *steps to reproduce : *
>>
>> --connect to sql
>> \c postgres postgres
>> --create role and database db1, will act as local server
>> create role db1 password 'db1' superuser login;
>> create database db1 owner=db1;
>> grant all on database db1 to db1;
>>
>> --create role and database db2, will act as foreign server
>> create role db2 password 'db2' superuser login;
>> create database db2 owner=db2;
>> grant all on database db2 to db2;
>>
>> --connect to db2 and create a table
>> \c db2 db2
>> create table fdw_sort_test (id integer, name varchar(50));
>> insert into fdw_sort_test values (1,'xyz');
>> insert into fdw_sort_test values (3,null);
>> insert into fdw_sort_test values (2,'abc');
>> insert into fdw_sort_test values (4,'pqr');
>>
>> --connect to db1 and create postgres_fdw
>> \c db1 db1
>> create extension postgres_fdw;
>> create server db2_link_server foreign data wrapper postgres_fdw options
>> (host 'db2_machine_ip', dbname 'db2', port 'db_machine_port_no');
>> create user mapping for db1 server db2_link_server options (user 'db2',
>> password 'db2');
>>
>> --create a foreign table
>> create foreign table fdw_sort_test (id integer, name varchar(50)) server
>> db2_link_server;
>>
>> --run the below query and checkout the output
>> select * from fdw_sort_test order by name desc nulls last;
>>
>> --check the explain plan
>> explain plan select * from fdw_sort_test order by name desc nulls last;
>>
>> Thanks & Regards,
>> Rajkumar Raghuwanshi
>> QMG, EnterpriseDB Corporation
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>