I just tracked down a bug in my software due to an "unexpected" behavior in Postgres.. Can someone clarify why this doesn't work (I haven't tried it on MSSQL or anything else, so I'm not sure if this is the official SQL standard or anything)..
CREATE TABLE test
(
value uuid
);
INSERT INTO test VALUES ('00000000-0000-0000-0000-000000000000');
INSERT INTO test VALUES ('11111111-1111-1111-1111-111111111111');
INSERT INTO test VALUES (null);
select * from test where value != '00000000-0000-0000-0000-000000000000';
What I expect to get is two rows: the '11111111-1111-1111-1111-111111111111' row and the null row, as both those values are in fact not '00000000-0000-0000-0000-000000000000'. However, I only get the first one.
I can change my query to:
select * from test where value is null or value != '00000000-0000-0000-0000-000000000000';
and that will give me the null rows, or rows that don't match that UUID. Is there a better way of writing this query? Thanks!
Mike
