Обсуждение: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8

Поиск
Список
Период
Сортировка

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

От
PG Bug reporting form
Дата:
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

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

От
Pavel Stehule
Дата:
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 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

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

От
Pavel Stehule
Дата:


ú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

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

От
Tom Lane
Дата:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> What is interesting - it fails only when the subquery is in CASE condition
> expression. If is somewhere else, then it doesn't fail

If eval_const_expressions can simplify the CASE test condition itself
to constant-true or constant-false, then it throws away the unreachable
result expression(s) without const-simplifying them.  So even if there
would have been a run-time error there, you don't see it.

Of course the error can only happen because we're trying to generate a
custom plan for the expression (with plpgsql variable values inserted
as constants not params).  That's a bit silly in this example, but
it wouldn't happen if there weren't a sub-SELECT in the expression.
That forces use of the full planner and plancache machinery.

            regards, tom lane

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

От
Pavel Stehule
Дата:


čt 23. 7. 2020 v 0:17 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> What is interesting - it fails only when the subquery is in CASE condition
> expression. If is somewhere else, then it doesn't fail

If eval_const_expressions can simplify the CASE test condition itself
to constant-true or constant-false, then it throws away the unreachable
result expression(s) without const-simplifying them.  So even if there
would have been a run-time error there, you don't see it.

Of course the error can only happen because we're trying to generate a
custom plan for the expression (with plpgsql variable values inserted
as constants not params).  That's a bit silly in this example, but
it wouldn't happen if there weren't a sub-SELECT in the expression.
That forces use of the full planner and plancache machinery.

Thank you for explanation

Regards

Pavel

                        regards, tom lane

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

От
Slawomir Nowakiewicz
Дата:

Hi,

I did little change in Pavel code and this work properly, anybody can explain this?

do $$

declare

  varv text = 'hello';

  varc text = 'xxx';

begin

  raise notice '%', case when 'int' = (select varc) then (select varv)::int else null end;

end;

$$;

--

Kind Regards

Sławomir Nowakiewicz

 

From: Pavel Stehule <pavel.stehule@gmail.com>
Sent: 23 July 2020 06:55
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Slawomir Nowakiewicz <Slawomir.Nowakiewicz@rubix.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8

 

 

 

čt 23. 7. 2020 v 0:17 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:
> What is interesting - it fails only when the subquery is in CASE condition
> expression. If is somewhere else, then it doesn't fail

If eval_const_expressions can simplify the CASE test condition itself
to constant-true or constant-false, then it throws away the unreachable
result expression(s) without const-simplifying them.  So even if there
would have been a run-time error there, you don't see it.

Of course the error can only happen because we're trying to generate a
custom plan for the expression (with plpgsql variable values inserted
as constants not params).  That's a bit silly in this example, but
it wouldn't happen if there weren't a sub-SELECT in the expression.
That forces use of the full planner and plancache machinery.

 

Thank you for explanation

 

Regards

 

Pavel


                        regards, tom lane