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

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.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)

Am I missing something?

Thanks,
Amit