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

On Wed, Nov 28, 2007 at 10:37:29PM -0700, m h wrote:

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?

I just checked in a fix, ie not needed. Niels

------------------------------------------------------------------------- SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl