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

ú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
>
>
I reduced this problem to simple DO script

do $$
declare
varv text = 'hello';
varc text = 'xxx';
begin
raise notice '%', case when 'int' = varc then varv::int else null end;
end;
$$;
NOTICE: <NULL>
DO

But with small change

postgres=# do $$
declare
varv text = 'hello';
varc text = 'xxx';
begin
raise notice '%', case when 'int' = (select varc) then varv::int else
null end;
end;
$$;
ERROR: invalid input syntax for type integer: "hello"
CONTEXT: SQL statement "SELECT case when 'int' = (select varc) then
varv::int else null end"
PL/pgSQL function inline_code_block line 6 at RAISE

What is interesting - it fails only when the subquery is in CASE condition
expression. If is somewhere else, then it doesn't fail

Regards

Pavel