Re: TEXT vs VARCHAR join qual push down diffrence, bug or expected? - Mailing list pgsql-hackers

Jeevan Chalke <jeevan.chalke@enterprisedb.com> writes:
> On Tue, Sep 22, 2015 at 12:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It strikes me that this function is really going about things the wrong
>> way.  Rather than trying to determine the output collation per se, what
>> we ought to be asking is "does every operator in the proposed expression
>> have an input collation that can be traced to some foreign Var further
>> down in the expression"?

> IIUC, you are saying that collation check for output collation is not
> necessary for all OpExpr/FuncExpr/ArrayRef etc.
> Should we remove code blocks like
>                 collation = r->resultcollid;
>                 if (collation == InvalidOid)
>                     state = FDW_COLLATE_NONE;
>                 else if (inner_cxt.state == FDW_COLLATE_SAFE &&
>                          collation == inner_cxt.collation)
>                     state = FDW_COLLATE_SAFE;
>                 else
>                     state = FDW_COLLATE_UNSAFE;

> and just bubble up the collation and state to the next level?

Removing that entirely would be quite incorrect, because then you'd be
lying to the parent node about what collation your node outputs.

After thinking a bit more about the existing special case for non-foreign
Vars, I wonder if what we should do is change these code blocks to look
like
               collation = r->resultcollid;               if (collation == InvalidOid)                   state =
FDW_COLLATE_NONE;              else if (inner_cxt.state == FDW_COLLATE_SAFE &&                        collation ==
inner_cxt.collation)                  state = FDW_COLLATE_SAFE;
 
+        else if (collation == DEFAULT_COLLATION_OID)
+            state = FDW_COLLATE_NONE;               else                   state = FDW_COLLATE_UNSAFE;

That is, only explicit introduction of a non-default collation causes
a subexpression to get labeled FDW_COLLATE_UNSAFE.  Default collations
would lose out when getting merged with a nondefault collation from a
foreign Var, so they should work all right.

The core point here is that we're going to send the expression to the
remote without any COLLATE clauses, so the remote's parser has to
come to the same conclusions we did about which collation to apply.
We assume that default-collation-throughout will work all right.
Nondefault collations will work as long as they originate from foreign
Vars, because then the remote parser should see the equivalent far-end
collations originating from those Vars --- and those collations win when
combined with default collations.
        regards, tom lane