Hello 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.

KR> The expression 'a'<>'b' would evaluate to false and 'a'<>null would
KR> evaluate to null. Anding them together should yield false. But that's
AFAIK, 'a'=null evaluates to false, not to null.

KR> not the expression I have - I have the negation of its its DeMorgan
KR> equivalent:
KR> 'a' = 'b' or 'a' = null
My mistake: giving you the expression ('a'<>'b' and 'a'<>null) I ment
that CASE would choose its else-part (i.e. the expression is evaluated
to true and the expression is equal to not('a'='b' or 'a'=null)). If
'a'=null evaluates to false (and not to null or "unknown"), DeMorgan
is OK.

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

KR> What I meant was this:

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

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

KR> Also, just a note: in my SQL book they call null UNKNOWN in the context
KR> of three-state logic, like in when-predicates.
I suppose FB has two-state logic, not three-state. Probably it's the
SQL standard deviation (if the SQL standard states the three-state
logic - I'm not sure).


HTH
--
Best regards,
Pavel Menshchikov
http://www.ls-software.com