Re: Issue with NULLS LAST, with postgres_fdw sort pushdown

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