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

>>>>> "PG" == PG Bug reporting form  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)