Pavel Menshchikov wrote:

> Kjell,
>
>
>>>case when 'a' in ('b', nullif(1, 1)) then 1 else 0 end
>>>
>>>I would expect this to return null, because the null item in the "in"
>>>list could possibly be an 'a', which would return 1. But it actually
>>>returns 0.
>
> KR> Ah, I see! Case always returns one of the then-values or the else-value,
> KR> no matter what. In my case the when-predicate evaluates to UNKNOWN, so
> KR> the then-value is not used. It proceeds to the else-value and picks
> KR> that. All in accordance with the SQL standard.
>
> KR> Right?
> Are you sure that the condition is evaluated to UNKNOWN? I think it is
> evaluated to false: 'a'<>'b' and 'a'<>null.

The expression 'a'<>'b' would evaluate to false and 'a'<>null would
evaluate to null. Anding them together should yield false. But that's
not the expression I have - I have the negation of its its DeMorgan
equivalent:

'a' = 'b' or 'a' = null

The problem is that DeMorgan doesn't work with three-state logic - null
messes it up. So your example is, in fact, irrelevant.

What I meant was this:

The case expression will return the first then-value whose
when-predicate evaluates to true, and if none of them evaluates to true
the else-value will be returned (if no else is specified, else null is
implied).

In my case, the expression 'a' in ('b', nullif(1, 1)) evaluates to null.
Since null is not true (nor is it false, but that's irrelevant here),
the else-value will be returned.

Also, just a note: in my SQL book they call null UNKNOWN in the context
of three-state logic, like in when-predicates.

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64