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@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
/messages/by-id/16545-affff840bc4e72ca@postgresql.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

ú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

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

č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

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<mailto:tgl@sss.pgh.pa.us>> napsal:
Pavel Stehule <pavel.stehule@gmail.com<mailto: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