Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8

Hi

út 21. 7. 2020 v 16:56 odesílatel PG Bug reporting form <
noreply(at)postgresql(dot)org> napsal:

> The following bug has been logged on the website:
>
> Bug reference: 16549
> Logged by: Sławomir Nowakiewicz
> Email address: slawomir(dot)nowakiewicz(at)rubix(dot)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 similar to
https://www.postgresql.org/message-id/flat/16545-affff840bc4e72ca%40postgresql.org

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$

Regards

Pavel