Re: [GENERAL] CASE in where statement. BUG ??

[Marek Lewczuk]

Alexander Litvinov wrote:
This is normaol behavior.


I belive that I have found a bug - or maybe it was done on purpose. Have
a look at this query:
SELECT integer_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN
integer_field = '' ELSE integer_field = 0 END);
Result: ERROR:  invalid input syntax for integer: ""


Here CASE have int type and you are trying to cast '' to int. Error.
Well, I see that you have not run this query: In this case the query is 
trying to make integer_field = '' --> but it shouldn't, becouse "CASE 
WHEN '' <> ''" is false, so "ELSE integer_field = 0" should be used.



SELECT text_field FROM bugtable WHERE (CASE WHEN '' <> '' THEN
text_field = '' ELSE text_field = '1' END);
Result: 1 rows fetched (0,02 sec)


Here CASE have text type. Everything is fine.
Yes, becouse "CASE WHEN '' <> ''" is send false and is using "text_field 
= 1".

Please execute this insert:
INSERT INTO bugtable (integer_field, text_field) VALUES (0, '1');
Then, executing both selects should return  "1 rows fetched (0,02 sec)"...

Check it out.



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])