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
