Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
От | Pavel Stehule |
---|---|
Тема | Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8 |
Дата | |
Msg-id | CAFj8pRBf1MjYkoYLzDyvufgB6H0OGJp9GBJBRasdoVC37Ytvnw@mail.gmail.com обсуждение исходный текст |
Ответ на | BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8 (PG Bug reporting form <noreply@postgresql.org>) |
Список | pgsql-bugs |
Hi
út 21. 7. 2020 v 16:56 odesílatel PG Bug reporting form <noreply@postgresql.org> napsal:
The following bug has been logged on the website:
Bug reference: 16549
Logged by: Sławomir Nowakiewicz
Email address: slawomir.nowakiewicz@rubix.com
PostgreSQL version: 12.1
Operating system: Linux
Description:
"CASE" try to cast a string to integer when a condition is not met. I
checked this function also on PostgreSQL 11.2 - the result is the same.
BEGIN;
CREATE TABLE public.temp_data_type
(
data_type_name text NOT NULL,
data_type_storage_type text NOT NULL DEFAULT ''::text
);
INSERT INTO public.temp_data_type
VALUES
('INTEGER','int'),
('LOOKUP_TABLE','string');
CREATE OR REPLACE FUNCTION public.temp_item_attribute_create(p_datatype
text, p_values text[])
RETURNS integer
LANGUAGE 'plpgsql'
VOLATILE
AS $BODY$
DECLARE
value_int integer;
i integer;
BEGIN
i:=1;
--RAISE NOTICE 'datatype: %', p_datatype;
--RAISE NOTICE 'attribute.values[i]: %',p_values[i];
value_int := CASE WHEN p_datatype::TEXT IN (SELECT data_type_name FROM
temp_data_type WHERE data_type_storage_type = 'int') THEN
p_values[i]::INTEGER
ELSE
NULL::INTEGER
END;
RETURN value_int;
END;
$BODY$;
ALTER FUNCTION public.temp_item_attribute_create(text,text[])
OWNER TO postgres;
SELECT temp_item_attribute_create('LOOKUP_TABLE','{SHELL}');
--ROLLBACK;
ERROR: 22P02: invalid input syntax for type integer: "SHELL"
CONTEXT: SQL statement "SELECT CASE WHEN p_datatype::TEXT IN (SELECT
data_type_name FROM temp_data_type WHERE data_type_storage_type = 'int')
THEN
p_values[i]::INTEGER
ELSE
NULL::INTEGER
END"
PL/pgSQL function temp_item_attribute_create(text,text[]) line 10 at
assignment
LOCATION: pg_strtoint32, numutils.c:259
It is not a bug - Postgres try to evaluate some expressions in different order in the optimization stage. Now, Postgres is more aggressive in query parameter evaluation.
The safe variant of your code looks like:
IF EXISTS(SELECT data_type_name FROM temp_data_type WHERE data_type_storage_type = 'int' AND p_datatype = data_type_name) THEN
value_int := p_values[i]::integer;
ELSE
value_int := NULL;
ENDIF;
or wrap parameter to simple volatile function:
create or replace function to_text(text) returns text as $$ begin return $1; end $$ language plpgsql volatile;
CREATE OR REPLACE FUNCTION public.temp_item_attribute_create(p_datatype text, p_values text[])
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
value_int integer;
i integer;
BEGIN
i:=1;
--RAISE NOTICE 'datatype: %', p_datatype;
--RAISE NOTICE 'attribute.values[i]: %',p_values[i];
value_int := CASE WHEN p_datatype::TEXT IN (SELECT data_type_name FROM
temp_data_type WHERE data_type_storage_type = 'int') THEN
to_text(p_values[i])::INTEGER
ELSE
NULL::INTEGER
END;
RETURN value_int;
END;
$function$
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
value_int integer;
i integer;
BEGIN
i:=1;
--RAISE NOTICE 'datatype: %', p_datatype;
--RAISE NOTICE 'attribute.values[i]: %',p_values[i];
value_int := CASE WHEN p_datatype::TEXT IN (SELECT data_type_name FROM
temp_data_type WHERE data_type_storage_type = 'int') THEN
to_text(p_values[i])::INTEGER
ELSE
NULL::INTEGER
END;
RETURN value_int;
END;
$function$
Regards
Pavel
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Jehan-Guillaume de RorthaisДата:
Сообщение: Re: Buffers from parallel workers not accumulated to upper nodes with gather merge