From 9e9c38e15947f4f3ed478f8b70e74b55e31fe950 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 24 Jun 2016 15:06:19 -0400 Subject: postgres_fdw: Fix incorrect NULL handling in join pushdown. something.* IS NOT NULL means that every attribute of the row is not NULL, not that the row itself is non-NULL (e.g. because it's coming from below an outer join. Use (somevar.*)::pg_catalog.text IS NOT NULL instead. Ashutosh Bapat, per a report by Rushabh Lathia. Reviewed by Amit Langote and Etsuro Fujita. Schema-qualification added by me. --- contrib/postgres_fdw/sql/postgres_fdw.sql | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index a2b03a1a715..df4060d57d1 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -543,6 +543,11 @@ SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; DROP USER MAPPING FOR PUBLIC SERVER loopback; +-- join with nullable side with some columns with null values +UPDATE ft5 SET c3 = null where c1 % 9 = 0; +EXPLAIN VERBOSE SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1; +SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1; + -- =================================================================== -- parameterized queries -- =================================================================== -- cgit v1.2.3