The following bug has been logged online:

Bug reference: 1406
Logged by: manikanti sreedhar reddy
Email address: manikant (AT) intoto (DOT) com
PostgreSQL version: 7.4.2
Operating system: Windows
Description: subsequent WHEN/ELSE is getting validated, eventhough
prior WHEN condition is true
Details:

Subsequent WHEN/ELSE is getting validated, eventhough prior WHEN condition
is true.

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);

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!

Thanks,

-Sreedhar Reddy

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"manikanti sreedhar reddy" <manikant (AT) intoto (DOT) com> writes:
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.

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)