The following bug has been logged on the website:
Bug reference: 15287
Logged by: Ireneusz Pluta
Email address: ipluta@wp.pl
PostgreSQL version: 10.3
Operating system: FreeBSD 11.1
Description:
the clause:
WHERE date_trunc('day'::text, dt::timestamp without time zone) =
'yyyy-mm-dd'
does not get shipped to the foreign server.
as seen on #postgresql:
[18:13] <RhodiumToad> date_trunc takes a mix of collatable and
non-collatable parameters (text is collatable, timestamp is not)
[18:13] <RhodiumToad> this is why I wanted the debug version of the query
tree, to see the collation ids
[18:14] <RhodiumToad> so given date_trunc('day', col), what happens is that
the walker first recurses into the args:
[18:14] <RhodiumToad> 'day' is a Const node with default collation, so the
inner_cxt.state gets set to NONE
[18:15] <RhodiumToad> "col" is a remote Var of a non-collatable type, so
inner_cxt.state still gets set to NONE
[18:15] <RhodiumToad> but then in the T_FuncExpr case in the walker, we get
to this line (468 in master):
[18:16] <RhodiumToad> if (fe->inputcollid == InvalidOid) ; else if
(inner_cxt.state != FDW_COLLATE_SAFE || ...) return false;
[18:17] <RhodiumToad> fe->inputcollid is DEFAULT_COLLATION_OID, not
InvalidOid, and inner_cxt.state is NONE, not SAFE, so the walker bails out
at that point
[18:17] <RhodiumToad> and reports the expression as "not safe for remote"
[18:18] <irqq_> should it be reported as a bug and expected to be
corrected?
[18:18] <RhodiumToad> basically this is confusion over the difference
between collid=InvalidOid, meaning "not of a collatable type", and
=DEFAULT_COLLATION_OID meaning "of a collatable type but no specified
collation"
[18:18] <RhodiumToad> yes it should be reported as a bug