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

That's the patch I came up with initially but it seemed to me to produce
the wrong result.  Correct me if that is not so:

CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public;

CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'test');

CREATE USER MAPPING FOR CURRENT_USER SERVER myserver;

CREATE TABLE base1 (a integer);
CREATE TABLE base2 (a integer);

CREATE FOREIGN TABLE fbase1 (a integer) SERVER myserver OPTIONS
(table_name 'base1');

INSERT INTO fbase1 VALUES (1);

CREATE FOREIGN TABLE fbase2 (a integer) SERVER myserver OPTIONS
(table_name 'base2');

INSERT INTO fbase2 VALUES (2);


explain verbose select subq.a, b2.a from (select 1 as a from fbase1 as b1)
as subq right join fbase2 as b2 on (subq.a = b2.a);
                                          QUERY PLAN

----------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..22423.12 rows=42778 width=8)
   Output: 1, b2.a
   Relations: (public.fbase2 b2) LEFT JOIN (public.fbase1 b1)
   Remote SQL: SELECT r2.a FROM (public.base2 r2 LEFT JOIN public.base1 r4
ON (((1 = r2.a))))
(4 rows)

select subq.a, b2.a from (select 1 as a from fbase1 as b1) as subq right
join fbase2 as b2 on (subq.a = b2.a);
 a | a
---+---
 1 | 2
(1 row)


---- to crosscheck - just using the local tables

explain verbose select subq.a, b2.a from (select 1 as a from base1 as b1)
as subq right join base2 as b2 on (subq.a = b2.a);
                                  QUERY PLAN

-------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..97614.88 rows=32512 width=8)
   Output: (1), b2.a
   Join Filter: (1 = b2.a)
   ->  Seq Scan on public.base2 b2  (cost=0.00..35.50 rows=2550 width=4)
         Output: b2.a
   ->  Materialize  (cost=0.00..48.25 rows=2550 width=4)
         Output: (1)
         ->  Seq Scan on public.base1 b1  (cost=0.00..35.50 rows=2550 width=4)
               Output: 1
(9 rows)

select subq.a, b2.a from (select 1 as a from base1 as b1) as subq right
join base2 as b2 on (subq.a = b2.a);
 a | a
---+---
   | 2
(1 row)

I thought both queries should produce the same result (the latter).

Which the non-push-down version does:

explain verbose select subq.a, b2.a from (select 1 as a from fbase1 as b1)
as subq right join fbase2 as b2 on (subq.a = b2.a);
                                      QUERY PLAN

---------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=200.00..128737.19 rows=42778 width=8)
   Output: (1), b2.a
   Join Filter: (1 = b2.a)
   ->  Foreign Scan on public.fbase2 b2  (cost=100.00..197.75 rows=2925
width=4)
         Output: b2.a
         Remote SQL: SELECT a FROM public.base2
   ->  Materialize  (cost=100.00..212.38 rows=2925 width=4)
         Output: (1)
         ->  Foreign Scan on public.fbase1 b1  (cost=100.00..197.75
rows=2925 width=4)
               Output: 1
               Remote SQL: SELECT NULL FROM public.base1
(11 rows)

select subq.a, b2.a from (select 1 as a from fbase1 as b1) as subq right
join fbase2 as b2 on (subq.a = b2.a);
 a | a
---+---
   | 2
(1 row)

I thought, columns of inner relation will be set to null during projection from ForeignScan for joins. But I was wrong. If we want to push-down joins in this case, we have two solutions

1. Build queries with subqueries at the time of deparsing. Thus a base relation or join has to include placeholders while being deparsed as a subquery. This means that the deparser should deparse expression represented by the placeholder. This may not be possible always.

2. At the time of projection from ForeignScan recognize the nullable placeholders and nullify them if the corresponding relation is nullified. That looks like a major surgery.

So, your patch looks to be the correct approach (even after we support deparsing subqueries). Can you please include a test in regression?