Re: CASE in where statement. BUG ??

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.