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

On 2016/06/15 9:13, Amit Langote wrote:
> On 2016/06/15 0:50, Robert Haas wrote:
>> On Tue, Jun 14, 2016 at 4:06 AM, Amit Langote wrote:
>>> Attached new version of the patch with following changes:

>> OK, I committed this version with some cosmetic changes.

Thank you all for working on this!

While reviewing the patch, I noticed that the patch is still 
restrictive.  Consider:

postgres=# explain verbose select ft1.a, (ft3.a IS NOT NULL) from (ft1 
inner join ft2 on ft1.a = ft2.a) left join ft3 on ft1.a = ft3.a; QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan  (cost=100.00..103.10 rows=2 width=5)    Output: ft1.a, (ft3.a IS NOT NULL)    Relations: ((public.ft1)
INNERJOIN (public.ft2)) LEFT JOIN 
 
(public.ft3)    Remote SQL: SELECT r1.a, r4.a FROM ((public.t1 r1 INNER JOIN 
public.t2 r2 ON (((r1.a = r2.a)))) LEFT JOIN public.t3 r4 ON (((r1.a = 
r4.a))))
(4 rows)

That's great, but:

postgres=# explain verbose select * from t1 left join (select ft1.a, 
(ft3.a IS NOT NULL) from (ft1 inner join ft2 on ft1.a = ft2.a) left join 
ft3 on ft1.a = ft3.a) ss (a, b) on t1.a = ss.a;                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------  Hash Right
Join (cost=202.11..204.25 rows=3 width=13)    Output: t1.a, t1.b, ft1.a, ((ft3.a IS NOT NULL))    Hash Cond: (ft1.a =
t1.a)   ->  Hash Left Join  (cost=201.04..203.15 rows=2 width=5)          Output: ft1.a, (ft3.a IS NOT NULL)
HashCond: (ft1.a = ft3.a)          ->  Foreign Scan  (cost=100.00..102.09 rows=2 width=4)                Output: ft1.a
             Relations: (public.ft1) INNER JOIN (public.ft2)                Remote SQL: SELECT r4.a FROM (public.t1 r4
INNERJOIN 
 
public.t2 r5 ON (((r4.a = r5.a))))          ->  Hash  (cost=101.03..101.03 rows=1 width=4)                Output: ft3.a
              ->  Foreign Scan on public.ft3  (cost=100.00..101.03 
 
rows=1 width=4)                      Output: ft3.a                      Remote SQL: SELECT a FROM public.t3    ->  Hash
(cost=1.03..1.03 rows=3 width=8)          Output: t1.a, t1.b          ->  Seq Scan on public.t1  (cost=0.00..1.03
rows=3width=8)                Output: t1.a, t1.b
 
(19 rows)

As in the example shown upthread, we could still push down the 
ft1-ft2-ft3 join and then perform the join between the result and t1. 
However, the patch doesn't allow that, because ph_eval_at is (b 4 7) and 
relids for the ft1-ft2-ft3 join is (b 4 5 7), and so the 
bms_nonempty_difference(relids, phinfo->ph_eval_at) test returns true.

ISTM that a robuster solution to this is to push down the ft1-ft2-ft3 
join with the PHV by extending deparseExplicitTargetList() and/or 
something else so that we can send the remote query like:

select ft1.a, (ft3.a IS NOT NULL) from (ft1 inner join ft2 on ft1.a = 
ft2.a) left join ft3 on ft1.a = ft3.a

Right?

Best regards,
Etsuro Fujita