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
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.raghuwanshi@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
Attachments:
pg_nulls_last.patchapplication/x-download; name=pg_nulls_last.patchDownload
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index ef8eab6..021b764 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -2301,15 +2301,17 @@ appendOrderByClause(List *pathkeys, deparse_expr_cxt *context)
appendStringInfoString(buf, delim);
deparseExpr(em_expr, context);
if (pathkey->pk_strategy == BTLessStrategyNumber)
appendStringInfoString(buf, " ASC");
else
appendStringInfoString(buf, " DESC");
if (pathkey->pk_nulls_first)
appendStringInfoString(buf, " NULLS FIRST");
+ else
+ appendStringInfoString(buf, " NULLS LAST");
delim = ", ";
}
reset_transmission_modes(nestlevel);
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 280c377..6479640 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -238,27 +238,27 @@ SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
105 | 5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
106 | 6 | 00106 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo
107 | 7 | 00107 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
108 | 8 | 00108 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo
109 | 9 | 00109 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo
110 | 0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
(10 rows)
-- whole-row reference
EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.*, c3, c1
-> Foreign Scan on public.ft1 t1
Output: t1.*, c3, c1
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c3 ASC, "C 1" ASC
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c3 ASC NULLS LAST, "C 1" ASC NULLS LAST
(5 rows)
SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
t1
--------------------------------------------------------------------------------------------
(101,1,00101,"Fri Jan 02 00:00:00 1970 PST","Fri Jan 02 00:00:00 1970",1,"1 ",foo)
(102,2,00102,"Sat Jan 03 00:00:00 1970 PST","Sat Jan 03 00:00:00 1970",2,"2 ",foo)
(103,3,00103,"Sun Jan 04 00:00:00 1970 PST","Sun Jan 04 00:00:00 1970",3,"3 ",foo)
(104,4,00104,"Mon Jan 05 00:00:00 1970 PST","Mon Jan 05 00:00:00 1970",4,"4 ",foo)
(105,5,00105,"Tue Jan 06 00:00:00 1970 PST","Tue Jan 06 00:00:00 1970",5,"5 ",foo)
@@ -375,30 +375,30 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
----------+----------
fixed |
(1 row)
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
-- inner join; expressions in the clauses appear in the equivalence class list
EXPLAIN (VERBOSE, COSTS false)
SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2."C 1"
-> Merge Join
Output: t1.c1, t2."C 1"
Merge Cond: (t1.c1 = t2."C 1")
-> Foreign Scan on public.ft2 t1
Output: t1.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
-> Index Only Scan using t1_pkey on "S 1"."T 1" t2
Output: t2."C 1"
(10 rows)
SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
c1 | C 1
-----+-----
101 | 101
102 | 102
103 | 103
@@ -408,30 +408,30 @@ SELECT t1.c1, t2."C 1" FROM ft2 t1 JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFF
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- outer join; expressions in the clauses do not appear in equivalence class
-- list but no output change as compared to the previous query
EXPLAIN (VERBOSE, COSTS false)
SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2."C 1"
-> Merge Left Join
Output: t1.c1, t2."C 1"
Merge Cond: (t1.c1 = t2."C 1")
-> Foreign Scan on public.ft2 t1
Output: t1.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
-> Index Only Scan using t1_pkey on "S 1"."T 1" t2
Output: t2."C 1"
(10 rows)
SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1") OFFSET 100 LIMIT 10;
c1 | C 1
-----+-----
101 | 101
102 | 102
103 | 103
@@ -1334,70 +1334,70 @@ SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER B
Sort Key: t1.c3, t1.c1
-> Foreign Scan
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
Remote SQL: SELECT r1.ctid, ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8), r1."C 1", r1.c3, ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
(9 rows)
-- SEMI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
Limit
Output: t1.c1
-> Merge Semi Join
Output: t1.c1
Merge Cond: (t1.c1 = t2.c1)
-> Foreign Scan on public.ft1 t1
Output: t1.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
-> Materialize
Output: t2.c1
-> Foreign Scan on public.ft2 t2
Output: t2.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
(13 rows)
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
c1
-----
101
102
103
104
105
106
107
108
109
110
(10 rows)
-- ANTI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Limit
Output: t1.c1
-> Merge Anti Join
Output: t1.c1
Merge Cond: (t1.c1 = t2.c2)
-> Foreign Scan on public.ft1 t1
Output: t1.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
-> Materialize
Output: t2.c2
-> Foreign Scan on public.ft2 t2
Output: t2.c2
- Remote SQL: SELECT c2 FROM "S 1"."T 1" ORDER BY c2 ASC
+ Remote SQL: SELECT c2 FROM "S 1"."T 1" ORDER BY c2 ASC NULLS LAST
(13 rows)
SELECT t1.c1 FROM ft1 t1 WHERE NOT EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c2) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
c1
-----
110
111
112
113
114
@@ -1441,35 +1441,35 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 CROSS JOIN ft2 t2 ORDER BY t1.c1, t2.c1 OFFSET 1
1 | 106
1 | 107
1 | 108
1 | 109
1 | 110
(10 rows)
-- different server, not pushed down. No result expected.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1
-> Merge Join
Output: t1.c1, t2.c1
Merge Cond: (t2.c1 = t1.c1)
-> Foreign Scan on public.ft6 t2
Output: t2.c1, t2.c2, t2.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC NULLS LAST
-> Materialize
Output: t1.c1, t1.c2, t1.c3
-> Foreign Scan on public.ft5 t1
Output: t1.c1, t1.c2, t1.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC NULLS LAST
(13 rows)
SELECT t1.c1, t2.c1 FROM ft5 t1 JOIN ft6 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 100 LIMIT 10;
c1 | c1
----+----
(0 rows)
-- unsafe join conditions (c8 has a UDT), not pushed down. Practically a CROSS
-- JOIN since c8 in both tables has same value.
EXPLAIN (COSTS false, VERBOSE)
@@ -1739,35 +1739,35 @@ EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
EXECUTE join_stmt;
c1 | c1
----+----
(0 rows)
-- create user mapping for view_owner and execute the prepared statement
-- the join should not be pushed down since joining relations now use two
-- different user mappings
CREATE USER MAPPING FOR view_owner SERVER loopback;
EXPLAIN (COSTS false, VERBOSE) EXECUTE join_stmt;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------
Limit
Output: t1.c1, ft5.c1
-> Merge Join
Output: t1.c1, ft5.c1
Merge Cond: (t1.c1 = ft5.c1)
-> Foreign Scan on public.ft5 t1
Output: t1.c1, t1.c2, t1.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC NULLS LAST
-> Materialize
Output: ft5.c1, ft5.c2, ft5.c3
-> Foreign Scan on public.ft5
Output: ft5.c1, ft5.c2, ft5.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC
+ Remote SQL: SELECT c1 FROM "S 1"."T 4" ORDER BY c1 ASC NULLS LAST
(13 rows)
EXECUTE join_stmt;
c1 | c1
----+----
(0 rows)
-- recreate the dropped user mapping for further tests
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
DROP USER MAPPING FOR PUBLIC SERVER loopback;
@@ -3806,20 +3806,100 @@ select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
101 | 2
104 | 2
106 | 2
201 | 1
204 | 1
303 | 100
403 | 2
407 | 100
(13 rows)
+-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
+-- FIRST behavior here.
+-- ORDER BY DESC NULLS LAST options
+EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c6 DESC NULLS LAST, "C 1" ASC NULLS LAST
+(5 rows)
+
+SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+------+-----+--------------------+------------------------------+--------------------------+------+------------+-----
+ 960 | 42 | 00960_trig_update | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 970 | 42 | 00970_trig_update | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 980 | 42 | 00980_trig_update | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 990 | 42 | 00990_trig_update | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 1000 | 42 | 01000_trig_update | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 1218 | 818 | ggg_trig_update | | | (--; | ft2 |
+ 1001 | 101 | 0000100001 | | | | ft2 |
+ 1003 | 403 | 0000300003_update3 | | | | ft2 |
+ 1004 | 104 | 0000400004 | | | | ft2 |
+ 1006 | 106 | 0000600006 | | | | ft2 |
+(10 rows)
+
+-- ORDER BY DESC NULLS FIRST options
+EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c6 DESC NULLS FIRST, "C 1" ASC NULLS LAST
+(5 rows)
+
+SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+------+-----+-----------------+------------------------------+--------------------------+----+------------+-----
+ 1020 | 100 | 0002000020 | | | | ft2 |
+ 1101 | 201 | aaa | | | | ft2 |
+ 1103 | 503 | ccc_update3 | | | | ft2 |
+ 1104 | 204 | ddd | | | | ft2 |
+ 1208 | 818 | fff_trig_update | | | | ft2 |
+ 9 | 509 | 00009_update9 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | ft2 | foo
+ 19 | 509 | 00019_update9 | Tue Jan 20 00:00:00 1970 PST | Tue Jan 20 00:00:00 1970 | 9 | ft2 | foo
+ 29 | 509 | 00029_update9 | Fri Jan 30 00:00:00 1970 PST | Fri Jan 30 00:00:00 1970 | 9 | ft2 | foo
+ 39 | 509 | 00039_update9 | Mon Feb 09 00:00:00 1970 PST | Mon Feb 09 00:00:00 1970 | 9 | ft2 | foo
+ 49 | 509 | 00049_update9 | Thu Feb 19 00:00:00 1970 PST | Thu Feb 19 00:00:00 1970 | 9 | ft2 | foo
+(10 rows)
+
+-- ORDER BY ASC NULLS FIRST options
+EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Scan on public.ft1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c6 ASC NULLS FIRST, "C 1" ASC NULLS LAST
+(5 rows)
+
+SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+------+-----+-------------------+------------------------------+--------------------------+------+------------+-----
+ 1020 | 100 | 0002000020 | | | | ft2 |
+ 1101 | 201 | aaa | | | | ft2 |
+ 1103 | 503 | ccc_update3 | | | | ft2 |
+ 1104 | 204 | ddd | | | | ft2 |
+ 1208 | 818 | fff_trig_update | | | | ft2 |
+ 1218 | 818 | ggg_trig_update | | | (--; | ft2 |
+ 10 | 42 | 00010_trig_update | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 20 | 42 | 00020_trig_update | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 30 | 42 | 00030_trig_update | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 40 | 42 | 00040_trig_update | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+(10 rows)
+
-- ===================================================================
-- test check constraints
-- ===================================================================
-- Consistent check constraints provide consistent results
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
QUERY PLAN
-------------------------------------------------------------------
Aggregate
Output: count(*)
@@ -4623,37 +4703,37 @@ insert into foo select generate_series(1, :num_rows_foo, 2), generate_series(1,
SET enable_hashjoin to false;
SET enable_nestloop to false;
alter foreign table foo2 options (use_remote_estimate 'true');
create index i_loct1_f1 on loct1(f1);
create index i_foo_f1 on foo(f1);
analyze foo;
analyze loct1;
-- inner join; expressions in the clauses appear in the equivalence class list
explain (verbose, costs off)
select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Limit
Output: foo.f1, loct1.f1, foo.f2
-> Sort
Output: foo.f1, loct1.f1, foo.f2
Sort Key: foo.f2
-> Merge Join
Output: foo.f1, loct1.f1, foo.f2
Merge Cond: (foo.f1 = loct1.f1)
-> Merge Append
Sort Key: foo.f1
-> Index Scan using i_foo_f1 on public.foo
Output: foo.f1, foo.f2
-> Foreign Scan on public.foo2
Output: foo2.f1, foo2.f2
- Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC
+ Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC NULLS LAST
-> Index Only Scan using i_loct1_f1 on public.loct1
Output: loct1.f1
(17 rows)
select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
f1 | f1
----+----
20 | 20
22 | 22
24 | 24
@@ -4663,37 +4743,37 @@ select foo.f1, loct1.f1 from foo join loct1 on (foo.f1 = loct1.f1) order by foo.
32 | 32
34 | 34
36 | 36
38 | 38
(10 rows)
-- outer join; expressions in the clauses do not appear in equivalence class
-- list but no output change as compared to the previous query
explain (verbose, costs off)
select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Limit
Output: foo.f1, loct1.f1, foo.f2
-> Sort
Output: foo.f1, loct1.f1, foo.f2
Sort Key: foo.f2
-> Merge Left Join
Output: foo.f1, loct1.f1, foo.f2
Merge Cond: (foo.f1 = loct1.f1)
-> Merge Append
Sort Key: foo.f1
-> Index Scan using i_foo_f1 on public.foo
Output: foo.f1, foo.f2
-> Foreign Scan on public.foo2
Output: foo2.f1, foo2.f2
- Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC
+ Remote SQL: SELECT f1, f2 FROM public.loct1 ORDER BY f1 ASC NULLS LAST
-> Index Only Scan using i_loct1_f1 on public.loct1
Output: loct1.f1
(17 rows)
select foo.f1, loct1.f1 from foo left join loct1 on (foo.f1 = loct1.f1) order by foo.f2 offset 10 limit 10;
f1 | f1
----+----
10 | 10
11 |
12 | 12
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 885a5fb..95e00eb 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -670,20 +670,32 @@ update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
rollback to savepoint s3;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
release savepoint s3;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
-- none of the above is committed yet remotely
select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
commit;
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
+-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
+-- FIRST behavior here.
+-- ORDER BY DESC NULLS LAST options
+EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10;
+SELECT * FROM ft1 ORDER BY c6 DESC NULLS LAST, c1 OFFSET 795 LIMIT 10;
+-- ORDER BY DESC NULLS FIRST options
+EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+-- ORDER BY ASC NULLS FIRST options
+EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
+
-- ===================================================================
-- test check constraints
-- ===================================================================
-- Consistent check constraints provide consistent results
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
SELECT count(*) FROM ft1 WHERE c2 < 0;
SET constraint_exclusion = 'on';
EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
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.bapat@enterprisedb.com> wrote:
Show quoted text
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.raghuwanshi@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
On Wed, Mar 2, 2016 at 7:04 PM, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.com> wrote:
On Wed, Mar 2, 2016 at 2:35 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:Thanks Rajkumar for your report. Let me know if the attached patch fixes
the issue.
if (pathkey->pk_nulls_first)
appendStringInfoString(buf, " NULLS FIRST");
+ else
+ appendStringInfoString(buf, " NULLS LAST");
Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be
applied only if DESC is used in this ORDER BY clause?
--
Michael--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 3, 2016 at 7:27 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Wed, Mar 2, 2016 at 7:04 PM, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.com> wrote:On Wed, Mar 2, 2016 at 2:35 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:Thanks Rajkumar for your report. Let me know if the attached patch fixes
the issue.if (pathkey->pk_nulls_first) appendStringInfoString(buf, " NULLS FIRST"); + else + appendStringInfoString(buf, " NULLS LAST"); Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be applied only if DESC is used in this ORDER BY clause?
I assume that you are referring to show_sortorder_options().
As per PG documentation
http://www.postgresql.org/docs/9.4/static/queries-order.html, "By default,
null values sort as if larger than any non-null value; that is, NULLS FIRST
is the default for DESC order, and NULLS LAST otherwise." What
show_sortorder_options() is doing is just trying to avoid printing the
defaults, which is arguably fine for an explain output; it leaves defaults
to be interpreted by user. In this case we are constructing a query to be
sent to the foreign server and it's better not to leave the defaults to be
interpreted by the foreign server; in case it interprets them in different
fashion. get_rule_orderby() also explicitly adds these options.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
On Thu, Mar 3, 2016 at 7:27 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be
applied only if DESC is used in this ORDER BY clause?
... In this case we are constructing a query to be
sent to the foreign server and it's better not to leave the defaults to be
interpreted by the foreign server; in case it interprets them in different
fashion. get_rule_orderby() also explicitly adds these options.
Yeah, I agree that we don't need to go out of our way to make the query
succinct here. Explicitness is easier and safer too, so why not?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Mar 3, 2016 at 12:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
On Thu, Mar 3, 2016 at 7:27 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be
applied only if DESC is used in this ORDER BY clause?... In this case we are constructing a query to be
sent to the foreign server and it's better not to leave the defaults to be
interpreted by the foreign server; in case it interprets them in different
fashion. get_rule_orderby() also explicitly adds these options.Yeah, I agree that we don't need to go out of our way to make the query
succinct here. Explicitness is easier and safer too, so why not?
+1. So, committed Ashutosh's version.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers