Re: [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116

On Fri, Jun 10, 2016 at 4:14 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Although I have done a bit of review of this patch, it needs more
> thought than I have so far had time to give it. I will update again
> by Tuesday.

I've reviewed this a bit further and have discovered an infelicity.
The following is all with the patch applied.

By itself, this join can be pushed down:

contrib_regression=# EXPLAIN SELECT 13 FROM ft1 RIGHT JOIN ft2 ON
ft1.c1 = ft2.c1;
QUERY PLAN
------------------------------------------------------
Foreign Scan (cost=100.00..137.66 rows=822 width=4)
Relations: (public.ft2) LEFT JOIN (public.ft1)
(2 rows)

That's great. However, when that query is used as the outer rel of a
left join, it can't:

contrib_regression=# explain verbose select * from ft4 LEFT JOIN
(SELECT 13 FROM ft1 RIGHT JOIN ft2 ON ft1.c1 = ft2.c1) q on true;
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=353.50..920.77 rows=41100 width=19)
Output: ft4.c1, ft4.c2, ft4.c3, (13)
-> Foreign Scan on public.ft4 (cost=100.00..102.50 rows=50 width=15)
Output: ft4.c1, ft4.c2, ft4.c3
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
-> Materialize (cost=253.50..306.57 rows=822 width=4)
Output: (13)
-> Hash Left Join (cost=253.50..302.46 rows=822 width=4)
Output: 13
Hash Cond: (ft2.c1 = ft1.c1)
-> Foreign Scan on public.ft2 (cost=100.00..137.66
rows=822 width=4)
Output: ft2.c1
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
-> Hash (cost=141.00..141.00 rows=1000 width=4)
Output: ft1.c1
-> Foreign Scan on public.ft1
(cost=100.00..141.00 rows=1000 width=4)
Output: ft1.c1
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
(18 rows)

Of course, because of the PlaceHolderVar, there's no way to push down
the ft1-ft2-ft4 join to the remote side. But we could still push down
the ft1-ft2 join and then locally perform the join between the result
and ft4. However, the proposed fix doesn't allow that, because
ph_eval_at is (b 4 5) and relids for the ft1-ft2 join is also (b 4 5),
and so the bms_is_subset(phinfo->ph_eval_at, relids) test returns
true.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company