Thread: Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
From
Tom Lane
Date:
"Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes: > SIMPLE=> create or replace procedure same_values_proc(a text, b text) as $body$ > SIMPLE$> begin > SIMPLE$> assert a = b; > SIMPLE$> end;$body$ language plpgsql; > CREATE PROCEDURE > SIMPLE=> > SIMPLE=> call same_values_proc(least('a','b'),'a'); > ERROR: could not determine which collation to use for string comparison > HINT: Use the COLLATE clause to set the collation explicitly. Yeah, same here. I think somebody forgot to run assign_expr_collations() on CALL arguments. regards, tom lane
Re: Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure
From
Peter Eisentraut
Date:
On 21/11/2018 19:19, Tom Lane wrote: > "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes: >> SIMPLE=> create or replace procedure same_values_proc(a text, b text) as $body$ >> SIMPLE$> begin >> SIMPLE$> assert a = b; >> SIMPLE$> end;$body$ language plpgsql; >> CREATE PROCEDURE >> SIMPLE=> >> SIMPLE=> call same_values_proc(least('a','b'),'a'); >> ERROR: could not determine which collation to use for string comparison >> HINT: Use the COLLATE clause to set the collation explicitly. > > Yeah, same here. I think somebody forgot to run assign_expr_collations() > on CALL arguments. This appears to fix it. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
Re: Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure
From
Peter Eisentraut
Date:
On 21/11/2018 19:19, Tom Lane wrote: > "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes: >> SIMPLE=> create or replace procedure same_values_proc(a text, b text) as $body$ >> SIMPLE$> begin >> SIMPLE$> assert a = b; >> SIMPLE$> end;$body$ language plpgsql; >> CREATE PROCEDURE >> SIMPLE=> >> SIMPLE=> call same_values_proc(least('a','b'),'a'); >> ERROR: could not determine which collation to use for string comparison >> HINT: Use the COLLATE clause to set the collation explicitly. > > Yeah, same here. I think somebody forgot to run assign_expr_collations() > on CALL arguments. This appears to fix it. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: > On 21/11/2018 19:19, Tom Lane wrote: >> Yeah, same here. I think somebody forgot to run assign_expr_collations() >> on CALL arguments. > This appears to fix it. I think this should be fine as a band-aid patch. As I mentioned previously, I'm not really happy with our generally-unprincipled approach to where collation assignment is called from ... but a bug-fix patch should probably not be tasked with making that better. Especially not with less than a week till 11.2. regards, tom lane
Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: > On 21/11/2018 19:19, Tom Lane wrote: >> Yeah, same here. I think somebody forgot to run assign_expr_collations() >> on CALL arguments. > This appears to fix it. I think this should be fine as a band-aid patch. As I mentioned previously, I'm not really happy with our generally-unprincipled approach to where collation assignment is called from ... but a bug-fix patch should probably not be tasked with making that better. Especially not with less than a week till 11.2. regards, tom lane
Re: Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure
From
Peter Eisentraut
Date:
On 05/02/2019 15:46, Tom Lane wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> On 21/11/2018 19:19, Tom Lane wrote: >>> Yeah, same here. I think somebody forgot to run assign_expr_collations() >>> on CALL arguments. > >> This appears to fix it. > > I think this should be fine as a band-aid patch. As I mentioned > previously, I'm not really happy with our generally-unprincipled > approach to where collation assignment is called from ... but a > bug-fix patch should probably not be tasked with making that > better. Especially not with less than a week till 11.2. committed -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure
From
Peter Eisentraut
Date:
On 05/02/2019 15:46, Tom Lane wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> On 21/11/2018 19:19, Tom Lane wrote: >>> Yeah, same here. I think somebody forgot to run assign_expr_collations() >>> on CALL arguments. > >> This appears to fix it. > > I think this should be fine as a band-aid patch. As I mentioned > previously, I'm not really happy with our generally-unprincipled > approach to where collation assignment is called from ... but a > bug-fix patch should probably not be tasked with making that > better. Especially not with less than a week till 11.2. committed -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services