Re: Recursive CTE and collation

=?UTF-8?Q?S=c3=a9bastien_Lardi=c3=a8re?= <sebastien(at)lardiere(dot)net> writes:
> While playing with the query showed here :
> https://fluca1978.github.io/2019/06/12/PartitioningCTE.html
> I've seen something strange with v12 (actually compiled after
> f43608bda2111a1fda514d1bed4df313ee2bbec3 so more recent than beta1) :

> psql: ERROR:  recursive query "inheritance_tree" column 3 has collation
> "default" in non-recursive term but collation "C" overall
> LINE 4:             , NULL::text AS table_parent_name
>                       ^
> HINT:  Use the COLLATE clause to set the collation of the non-recursive
> term.

Yeah. Your query is really wrong as it stands, because it's trying
to union "NULL::text" with a column of type "name". That accidentally
works in previous releases, but as of v12, "name" has acquired
collatability, and the recursive-union rules don't allow papering that
over.

I notice that it does work in a regular union:

regression=# select null::text union select null::name collate "C";
text
------

(1 row)

but I believe that recursive union is intentionally stricter.

> It work correctly with 11, and with 12, we can workaround by adding
> COLLATE "C" after NULL::text, so I don't know if it's a bug or a new
> feature, but it break things, at least.

I'd suggest using "NULL::name" instead.

regards, tom lane