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.ba...@enterprisedb.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.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 >
