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

On Friday 19 December 2003 09:02, Marek Lewczuk wrote:
> 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.

But until it's evaluated ''<>'' it doesn't know it is false. When building the
CASE expression, it's trying to map types to each elements and notes that ''
is not an int. Only after this stage will it actually try to evaluate the
expression.

--
Richard Huxton
Archonet Ltd