Hello,
is null = null true or also null ?
more precisely I have this kind of situation in a mission critical
project and I'm,sadly, not an expert in SQL.
But until now I used null values with a specific meaning in my database,
and I didn't knew that
I would come in this situation:
=>create table dummy (a int, b int);
insert into dummy values (1);
insert into dummy values (2);
insert into dummy values (3);
--- this work as expected
=>select * from dummy where a = 1 and a in (select a from dummy where a
!= 3 );
a|b
-+-
1|
(1 row)
--- this one also
=> select a from dummy where a = 1 intersect select a from dummy where a
!= 3 ;
a
-
1
(1 row)
---- !!!!!!!!
=> select a,b from dummy where a = 1 intersect select a,b from dummy
where a != 3 ;
a|b
-+-
(0 rows)
it appears that two null records are not equal if they are both null.
I tried also
select b = b from dummy where b is null;
and I get three empty rows.
First question: is this correct? is this SQL conformant?
2: if I change the sources for the operator to compare nulls as
they where values
will it have too many side effects?
one possibility I have is to create a new type with a custum operator
"=" for each field type I use
in this compond filter query but I'd like to know if there are other
solutions before I start to do
this long coding.
I would appreciate very much and kind of help.
Thanks in advance
Marko Mikulicic
