Обсуждение: BUG #1406: subsequent WHEN/ELSE is getting validated, eventhough prior WHEN condition is true

The following bug has been logged online:

Bug reference:      1406
Logged by:          manikanti sreedhar reddy
Email address:      manikant@intoto.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

PGSQL version is 8.0.0 beta4.

"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

Hi,

This kind of issue came, when i am porting the MySQL queries to
PostGreSQL.  Here is the query in MySQL, it does not give syntax error,
whereas PGSQL errors.

Simplified form:
mysql> select (if (locate('/','iprange')=0,0,INET_ATON('iprange')));
+-------------------------------------------------------+
| (if (locate('/','iprange')=0,0,INET_ATON('iprange'))) |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

For this situation, how to achieve the same without failing during constant
folding.

PGSQL does not fail during the folding, if table column is used and table
column does not have valid data.

example:
create table test (col varchar(25));
insert into test values ('ANY');
insert into test values ('172.16.1.1/32');

SELECT * FROM test WHERE (CASE WHEN POSITION('/' IN col)=0 THEN FALSE ELSE
(INET(col::INET)>>INET('172.16.1.5'::INET)) END);

Because of this deviation, i want rate it as bug.  I understand that, the
query is getting discarded during syntax checking phase in earlier case,
where as in the above case it is syntactically correct.

-Sreedhar Reddy


At 09:04 PM 1/17/2005, you wrote:
>"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
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)