Re: [MonetDB-users] incorrect handling of NULL values in "IN" statements?

29 Nov 2007


      On Nov 26, 2007 4:47 PM, m h  wrote:
...
My understanding that is that NULL is never equal to anything
including itself.  Thus to test for NULL values, one must use the IS
operator.  I'm wondering if the following is a bug.  The NOT IN will
return the null row, instead of the empty set.  Are NULL values
outside that query?
CREATE TABLE query_users (
        user_id INT NOT NULL AUTO_INCREMENT,
        user_name VARCHAR(20),
        PRIMARY KEY (user_id)
);
INSERT INTO query_users (user_id, user_name) VALUES (1, 'matt');
INSERT INTO query_users (user_id, user_name) VALUES (2, 'fred');
INSERT INTO query_users (user_id, user_name) VALUES (3, null);
SELECT user_name
FROM query_users
WHERE user_name IN ('matt', 'fred');
-- should return empty set, returns null
SELECT user_name
FROM query_users
WHERE user_name NOT IN ('matt', 'fred');
Should I file a bug (or feature request) for this?