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

From Slawomir Nowakiewicz
Subject RE: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
Date
Msg-id VI1PR0101MB2318D45A908F5C87584AC0FA88760@VI1PR0101MB2318.eurprd01.prod.exchangelabs.com
Whole thread Raw
In response to Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-bugs

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

pgsql-bugs by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: BUG #16549: "CASE" not work properly , the function works properly on PostgreSQL 9.6.8
Next
From: Jehan-Guillaume de Rorthais
Date:
Subject: Re: Buffers from parallel workers not accumulated to upper nodes with gather merge