Re: BUG #15287: postgres_fdw: the "WHERE date_trunc('day', dt) = 'YYYY-MM-DD' does not push to remote.

>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:

PG> the clause:
PG> WHERE date_trunc('day'::text, dt::timestamp without time zone) = 'yyyy-mm-dd'
PG> does not get shipped to the foreign server.

Just to expand and clarify my response from irc:

This is the relevant part of the parse tree (as shown by
debug_print_parse) annotated for convenience:

{FUNCEXPR
:funcid 2020 = date_trunc(text,timestamp without time zone)
:funcresulttype 1114 = timestamp without time zone
:funcretset false
:funcvariadic false
:funcformat 0
:funccollid 0
:inputcollid 100 = "default"
:args (
{CONST
:consttype 25
:consttypmod -1
:constcollid 100 = "default"
:constlen -1
:constbyval false
:constisnull false
:location 55
:constvalue 7 [ 28 0 0 0 100 97 121 ]
}
{VAR -- the foreign table is the only table in the query,
:varno 1 -- so this var is remote
:varattno 3
:vartype 1114 = timestamp without time zone
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 3
:location 68
}
)
:location 44
}

foreign_expr_walker's FuncExpr case first walks the args, which gives an
inner_cxt.state == FDW_COLLATE_NONE since the Const's collid of "default"
is explicitly treated the same as InvalidOid (but note that this is NOT
what assign_collations_walker did: it assigned the function's
inputcollid as 100 (default), not InvalidOid).

Then we get here:

/*
* If function's input collation is not derived from a foreign
* Var, it can't be sent to remote.
*/
if (fe->inputcollid == InvalidOid)
/* OK, inputs are all noncollatable */ ;
else if (inner_cxt.state != FDW_COLLATE_SAFE ||
fe->inputcollid != inner_cxt.collation)
return false;

so with inputcollid==100 and inner_cxt.state == FDW_COLLATE_NONE, the
walker bails out at this point.

--
Andrew (irc:RhodiumToad)