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

On Fri, Jun 10, 2016 at 4:14 PM, Robert Haas <robertmhaas@gmail.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.77rows=41100 width=19)  Output: ft4.c1, ft4.c2, ft4.c3, (13)  ->  Foreign Scan on public.ft4
(cost=100.00..102.50rows=50 width=15)        Output: ft4.c1, ft4.c2, ft4.c3        Remote SQL: SELECT c1, c2, c3 FROM
"S1"."T 3"  ->  Materialize  (cost=253.50..306.57 rows=822 width=4)        Output: (13)        ->  Hash Left Join
(cost=253.50..302.46rows=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
Scanon 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