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

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) INNER JOIN (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)
Hash Cond: (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 INNER JOIN
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=3 width=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