Re: Issue with NULLS LAST, with postgres_fdw sort pushdown

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