Hi,

I'm a bit surprised by the results I'm getting so although I do get what
I *want* I need to know if this is also what I'm *supposed* to get. :-)

This returns 1 as expected:
case when 'a' in ('a', 'b') then 1 else 0 end

This returns 0 as expected:
case when 'a' in ('c', 'b') then 1 else 0 end

Now to the tricky part - nulls...

This returns 1 as expected:
case when 'a' in ('a', nullif(1, 1)) then 1 else 0 end
case when 'a' in (nullif(1, 1), 'a') then 1 else 0 end

So far, so good. But now this:
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.

Is this really in accordance with the SQL standard?

If it's not, is it an "as designed" deviation from the standard or is it
a bug that will be fixed in the future?

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