Re: BUG #1406: subsequent WHEN/ELSE is getting validated, eventhough prior WHEN condition is true

"manikanti sreedhar reddy" <manikant@intoto.com> writes:
> Let us take an example:

> select (CASE WHEN POSITION('/' IN '172.16.1.1-172.16.1.25')=0 THEN FALSE
> ELSE (INET('172.16.1.1-172.16.1.25'::INET) >>  INET('internalnetwork'::INET)
> ) END);

I get:

regression=# select (CASE WHEN POSITION('/' IN '172.16.1.1-172.16.1.25')=0 THEN FALSE
regression(# ELSE (INET('172.16.1.1-172.16.1.25'::INET) >>  INET('internalnetwork'::INET)
regression(# ) END);
ERROR:  invalid input syntax for type inet: "172.16.1.1-172.16.1.25"
regression=#

which it is.

> In this, example since the first when condition is always true (since / is
> not present in '172.16.1.1-172.16.1.25' and POSITION('/' IN
> '172.16.1.1-172.16.1.25')=0 always evaluated to true) the expected output is
> false.  Whereas postgresql is evaluating the INET('intervalnetwork'::INET)
> expression which is in ELSE case, which is not supposed to happen!

This is not a bug.  It's about on par with having written

case ... else @(*%$*&@!*$ end;

and expecting the thing not to throw a syntax error.  The reason is that
'172.16.1.1-172.16.1.25'::INET is an illegal constant of the inet
datatype.  It gets rejected before the parser has even worked its way up
to the point of interpreting the CASE construct.

You could work around the problem by forcing the expression to represent
run-time conversion of text constants:

ELSE (INET('172.16.1.1-172.16.1.25'::text) >> INET('internalnetwork'::text)

However this will only avoid the problem when the previous WHEN clause
folds to a constant TRUE.  Constant-folding will be applied to
potentially reachable arms of a CASE, meaning for example that

SELECT WHEN x>0 THEN false
ELSE (INET('172.16.1.1-172.16.1.25'::text) >> INET('internalnetwork'::text)
) END) FROM table1;

will fail during constant folding, even if every x in table1 is > 0.
Again, I do not consider that a bug.

            regards, tom lane