Re: Recursive CTE and collation

=?UTF-8?Q?S=c3=a9bastien_Lardi=c3=a8re?= <sebastien(at)lardiere(dot)net> writes:
> On 14/06/2019 15:39, Tom Lane wrote:
>> I notice that it does work in a regular union:
>> regression=# select null::text union select null::name collate "C";
>> but I believe that recursive union is intentionally stricter.

I took a closer look at the code to refresh my memory about this,
and the actual rule for recursive unions is that the output of
the union has to have the same column types/collations that were
inferred from the non-recursive (first) side alone. This is needed
because when we do parse analysis of the recursive side, those
types/collations are what we'll assume for any references to the
recursive union's result. It's too late to change those decisions
when we find out what the UNION actually produces.

(You could imagine doing the parse analysis more than once in hopes
of arriving at a stable result, but ugh. I don't think the SQL spec
requires any such thing.)

So what we have here is that in v11, you were union'ing text (collation
"default") with name (no collation), and you got text with collation
"default" because text is a preferred type over name. So it worked OK.
In v12, you're union'ing text (collation "default") with name (collation
"C"). You still get text output because text is still the preferred
type, but the collation resolution rules consider "default" to not be
preferred so the chosen output collation is "C". Ooops.

Obviously there's more than one way you could fix the mismatch, but
I think that changing the NULL to type "name" is the nicest.

regards, tom lane