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

26 Nov 2007


      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');

m h

m h

Niels Nes

m h

tags

participants (2)