Thread: BUG #1406: subsequent WHEN/ELSE is getting validated, eventhough prior WHEN condition is true
BUG #1406: subsequent WHEN/ELSE is getting validated, eventhough prior WHEN condition is true
From
"manikanti sreedhar reddy"
Date:
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.
Re: BUG #1406: subsequent WHEN/ELSE is getting validated, eventhough prior WHEN condition is true
From
Tom Lane
Date:
"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)