diff options
| author | Robert Haas <rhaas@postgresql.org> | 2016-06-14 11:48:27 -0400 |
|---|---|---|
| committer | Robert Haas <rhaas@postgresql.org> | 2016-06-14 11:48:27 -0400 |
| commit | 131c7e70b4596027992a2f72bfd3765f0fff1b7c (patch) | |
| tree | 74c2b4462ecd6cc0b366ea7b879f9f80795ab8e2 /contrib/postgres_fdw/sql | |
| parent | 5484c0a9806b3e90b483128bc386054fc432cb65 (diff) | |
| download | postgresql-131c7e70b4596027992a2f72bfd3765f0fff1b7c.tar.gz postgresql-131c7e70b4596027992a2f72bfd3765f0fff1b7c.zip | |
postgres_fdw: Check PlaceHolderVars before pushing down a join.
As discovered by Andreas Seltenreich via sqlsmith, it's possible for a
remote join to need to generate a target list which contains a
PlaceHolderVar which would need to be evaluated on the remote server.
This happens when we try to push down a join tree which contains outer
joins and the nullable side of the join contains a subquery which
evauates some expression which can go to NULL above the level of the
join. Since the deparsing logic can't build a remote query that
involves subqueries, it fails while trying to produce an SQL query
that can be sent to the remote side. Detect such cases and don't try
to push down the join at all.
It's actually fine to push down the join if the PlaceHolderVar needs
to be evaluated at the current join level. This patch makes a small
change to build_tlist_to_deparse so that this case will work.
Amit Langote, Ashutosh Bapat, and me.
Diffstat (limited to 'contrib/postgres_fdw/sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 12 |
1 files changed, 12 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 6c2b08c37a4..a2b03a1a715 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -527,6 +527,18 @@ EXECUTE join_stmt; EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM (ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1)) left join (ft5 t3 JOIN v_ft5 t4 ON (t3.c1 = t4.c1)) ON (t1.c1 = t3.c1); +-- non-Var items in targelist of the nullable rel of a join preventing +-- push-down in some cases +-- unable to push {ft1, ft2} +EXPLAIN (COSTS false, VERBOSE) +SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15; +SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15; + +-- ok to push {ft1, ft2} but not {ft1, ft2, ft4} +EXPLAIN (COSTS false, VERBOSE) +SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15; +SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15; + -- recreate the dropped user mapping for further tests CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; DROP USER MAPPING FOR PUBLIC SERVER loopback; |