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.raghuwan...@enterprisedb.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
pg_nulls_last.patch
Description: application/download
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
