Re: Weird "could not determine which collation to use for string comparison" with LEAST/GREATEST on PG11 procedure
Peter Eisentraut <[email protected]> Tue, 5 Feb 2019 15:11:32 +0100
| Newsgroups | gmane.comp.db.postgresql.sql,gmane.comp.db.postgresql.bugs |
|---|---|
| Organization | 2ndQuadrant |
| Message-ID | <[email protected]> |
On 21/11/2018 19:19, Tom Lane wrote: > "Voillequin, Jean-Marc" <[email protected]> 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
0001-Add-collation-assignment-to-CALL-statement.patch
(text/plain, 2.3 KB)
From 5c68d4c057f2ed41d03faf46e20e33df24990305 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <[email protected]> Date: Tue, 5 Feb 2019 15:08:53 +0100 Subject: [PATCH] Add collation assignment to CALL statement Otherwise functions that require collation information will not have it if they are called in arguments to a CALL statement. Reported-by: Jean-Marc Voillequin <[email protected]> Discussion: https://www.postgresql.org/message-id/flat/1EC8157EB499BF459A516ADCF135ADCE39FFAC54%40LON-WGMSX712.ad.moodys.net --- src/backend/parser/analyze.c | 2 ++ src/test/regress/expected/create_procedure.out | 7 +++++++ src/test/regress/sql/create_procedure.sql | 11 +++++++++++ 3 files changed, 20 insertions(+) diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 7f5773582b..e3544efb6f 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -2636,6 +2636,8 @@ transformCallStmt(ParseState *pstate, CallStmt *stmt) true, stmt->funccall->location); + assign_expr_collations(pstate, node); + stmt->funcexpr = castNode(FuncExpr, node); result = makeNode(Query); diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 5b9b83839c..211a42cefa 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -139,6 +139,13 @@ AS $$ SELECT NULL::int; $$; CALL ptest6(1, 2); +-- collation assignment +CREATE PROCEDURE ptest7(a text, b text) +LANGUAGE SQL +AS $$ +SELECT a = b; +$$; +CALL ptest7(least('a', 'b'), 'a'); -- various error cases CALL version(); -- error: not a procedure ERROR: version() is not a procedure diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index b64293ed66..89b96d580f 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -101,6 +101,17 @@ CREATE PROCEDURE ptest6(a int, b anyelement) CALL ptest6(1, 2); +-- collation assignment + +CREATE PROCEDURE ptest7(a text, b text) +LANGUAGE SQL +AS $$ +SELECT a = b; +$$; + +CALL ptest7(least('a', 'b'), 'a'); + + -- various error cases CALL version(); -- error: not a procedure -- 2.20.1