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

On 2016/06/08 14:13, Ashutosh Bapat wrote:
> On Tue, Jun 7, 2016 at 6:19 PM, Amit Langote wrote:
>> On Tue, Jun 7, 2016 at 7:47 PM, Ashutosh Bapat wrote:
>>> Looks good to me. If we add a column from the outer relation, the
>> "NULL"ness
>>> of inner column would be more clear. May be we should tweak the query to
>>> produce few more rows, some with non-NULL columns from both the
>> relations.
>>> Also add a note to the comment in the test mentioning that such a join
>> won't
>>> be pushed down for a reader to understand the EXPLAIN output. Also, you
>>> might want to move that test, closer to other un-pushability tests.
>>
>> Done in the attached.  Please check if my comment explains the reason
>> of push-down failure correctly.
>
> On further thought, I think we need to restrict the join pushdown only for
> outer joins. Only those joins can produce NULL rows. If we go with that
> change, we will need my changes as well and a testcase with inner join.

I think the added test in foreign_join_ok() would restrict only the outer
joins from being pushed down (and further, only those with placeholdervars
in their targetlist being referred to above their level).  Do you have any
query handy as example where unintended push-down failure occurs?

I tried the following example where the join {b1, b2} is pushed down
whereas {b1, b2, b3} is not, which seems reasonable because the
placeholdervar corresponding to subq.a referred to in select targetlist is
traceable to b3:

explain verbose
select b1.a, b2.a, subq.a
from fbase1 as b1 left join fbase2 b2 on (b1.a = b2.a) left join (select 1
as a from fbase3 as b3) as subq on (subq.a = b2.a);                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------Nested
LoopLeft Join  (cost=205.69..225.41 rows=10 width=12)  Output: b1.a, b2.a, (1)  Join Filter: (1 = b2.a)  ->  Foreign
Scan (cost=105.69..124.23 rows=10 width=8)        Output: b1.a, b2.a        Relations: (public.fbase1 b1) LEFT JOIN
(public.fbase2b2)        Remote SQL: SELECT r1.a, r2.a FROM (public.base1 r1 LEFT JOIN
 
public.base2 r2 ON (((r1.a = r2.a))))  ->  Materialize  (cost=100.00..101.04 rows=1 width=32)        Output: (1)
-> Foreign Scan on public.fbase3 b3  (cost=100.00..101.03 rows=1
 
width=32)              Output: 1              Remote SQL: SELECT NULL FROM public.base3
(12 rows)

truncate base1;
truncate base2;
truncate base3;
insert into base1 select generate_series (1, 20);
insert into base2 select generate_series (1, 10);
insert into base3 select generate_series (1, 1);

select b1.a, b2.a, subq.a
from base1 as b1 left join base2 b2 on (b1.a = b2.a) left join (select 1
as a from base3 as b3) as subq on (subq.a = b2.a);a  | a  | a
----+----+--- 1 |  1 | 1 2 |  2 | 3 |  3 | 4 |  4 | 5 |  5 | 6 |  6 | 7 |  7 | 8 |  8 | 9 |  9 |10 | 10 |10 | 10 |11 |
 |12 |    |13 |    |14 |    |15 |    |16 |    |17 |    |18 |    |19 |    |20 |    |
 
(21 rows)

Missing something?

Thanks,
Amit