Hi,

regarding to a bug in tracker
(http://tracker.firebirdsql.org/browse/CORE-2951) I found this is a bit
interesting to all that don't know about it.

Let's have this test case:

CREATE TABLE TEST_A (ID INTEGER);

INSERT INTO TEST_A VALUES (1);
INSERT INTO TEST_A VALUES (2);
INSERT INTO TEST_A VALUES (3);

Now run this query:

SELECT
ID,
CASE
WHEN ID IN (1, NULL) THEN 'IT''S IN'
ELSE
'IT''S NOT IN'
END
FROM
TEST_A

in words - we want to select ID and text "It's in" in case when value of
ID is 1 or NULL or text "It's not in" in case when the value of ID is
not 1 or NULL
The result is as we're expecting:
ID CASE
1 IT'S IN
2 IT'S NOT IN
3 IT'S NOT IN

Now run this query:

SELECT
ID,
CASE
WHEN ID NOT IN (1, NULL) THEN 'IT''S NOT IN'
ELSE
'IT''S IN'
END
FROM
TEST_A

in words - we want to select ID and text "It's in" in case when value of
ID is 1 or NULL or text "It's not in" in case when the value of ID is
not 1 or NULL
The result looks pretty strange and buggy:
ID CASE
1 IT'S IN
2 IT'S IN
3 IT'S IN

Now let's add new record to the table TEST_A:
insert into test_a (id) values (null);

and rerun the queries. The first one will get another strange result -
newly added record will return text "It's not in". In case of second
query that newly added record will return text "It's in" and it's right.

Pretty confusing isn't?

Read more how and why it happen here (thanks Dmitry for explanation)
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!777.entry
http://articles.techrepublic.com.com/5100-22_11-5319615.html

Milan