Re: [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116 - Mailing list pgsql-hackers

Hi Ashutosh,

On 2016/06/07 17:02, Ashutosh Bapat wrote:
> On Tue, Jun 7, 2016 at 11:36 AM, Amit Langote wrote:
>> On 2016/06/05 23:01, Andreas Seltenreich wrote:

...

>>> --8<---------------cut here---------------start------------->8---
>>> create extension postgres_fdw;
>>> create server myself foreign data wrapper postgres_fdw;
>>> create schema fdw_postgres;
>>> create user mapping for public server myself options (user :'USER');
>>> import foreign schema public from server myself into fdw_postgres;
>>> select subq_0.c0 as c0 from
>>>        (select 31 as c0 from fdw_postgres.a as ref_0
>>>             where 93 >= ref_0.aa) as subq_0
>>>        right join fdw_postgres.rtest_vview5 as ref_1
>>>        on (subq_0.c0 = ref_1.a )
>>>        where 92 = subq_0.c0;
>>> --8<---------------cut here---------------end--------------->8---
>>
> 
> The repro assumes existence of certain tables/views e.g. rtest_vview5, a in
> public schema. Their definition is not included here. Although I could
> reproduce the issue by adding a similar query in the postgres_fdw
> regression tests (see attached patch).

See below for the query I used (almost same as the regression test you added).

>> Thanks for the example.  It seems that postgres_fdw join-pushdown logic
>> (within foreign_join_ok()?) should reject a join if any PlaceHolderVars in
>> its targetlist are required above it.  Tried to do that with the attached
>> patch which trivially fixes the reported assertion failure.
>>
> 
> Although the patch fixes the issue, it's restrictive. The placeholder Vars
> can be evaluated locally after the required columns are fetched from the
> foreign server. The right fix, therefore, is to build targetlist containing
> only the Vars that belong to the foreign tables, which in this case would
> contain "nothing". Attached patch does this and fixes the issue, while
> pushing down the join. Although, I haven't tried the exact query given in
> the report. Please let me know if the patch fixes issue with that query as
> well.

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.12rows=42778 width=8)  Output: 1, b2.a  Relations: (public.fbase2 b2) LEFT JOIN (public.fbase1 b1)
RemoteSQL: 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.88rows=32512 width=8)  Output: (1), b2.a  Join Filter: (1 = b2.a)  ->  Seq Scan on public.base2 b2
(cost=0.00..35.50rows=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.19rows=42778 width=8)  Output: (1), b2.a  Join Filter: (1 = b2.a)  ->  Foreign Scan on
public.fbase2b2  (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=2925width=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)


Am I missing something?

Thanks,
Amit