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)
>
>
Thanks for that case.

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?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company