From 9e9c38e15947f4f3ed478f8b70e74b55e31fe950 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
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

