Обсуждение: Recursive CTE and collation
Hi, 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. 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. The database I use is created with en_US collation : testpart=# \l testpart | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | testpart=# show lc_collate ; lc_collate ------------- en_US.UTF-8 regards, -- Sébastien
=?UTF-8?Q?S=c3=a9bastien_Lardi=c3=a8re?= <sebastien@lardiere.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
On 14/06/2019 15:39, Tom Lane wrote: > =?UTF-8?Q?S=c3=a9bastien_Lardi=c3=a8re?= <sebastien@lardiere.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". Indeed, ::text is wrong, > 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. Good to know, > 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. yes, thanks, -- Sébastien
=?UTF-8?Q?S=c3=a9bastien_Lardi=c3=a8re?= <sebastien@lardiere.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
On Fri, Jun 14, 2019 at 5:19 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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. Thanks for taking time and effort to explain this really interesting piece of code. > 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. I've fixed the query both on the post and in my repository, thanks. Thanks also to Sebastien for pointing out my error. Luca