Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8 - Mailing list pgsql-bugs

From Pavel Stehule
Subject Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
Date
Msg-id CAFj8pRAyxTr1m-Azy7Ear6G9DgRHFsmpnoaAV2mQ2iecDbyZXg@mail.gmail.com
Whole thread Raw
In response to BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
List pgsql-bugs


ú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


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

pgsql-bugs by date:

Previous
From: Christophe Pettus
Date:
Subject: Re: BUG #16550: Problem with pg_service.conf
Next
From: "David G. Johnston"
Date:
Subject: Re: BUG #16550: Problem with pg_service.conf