I have a situation where the left and right side of a where clause both
evaluate to null independently, but when compared to
each other, they don't equate. Observe:
monitoring=# SELECT count(*) FROM current_status c WHERE c.subunit_id is
null;
count
-------
1
(1 row)
So there's one row in current_status that has a subunit_id equal to
null. Good so far.
monitoring=# select
subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)));
subunitid
-----------
(1 row)
monitoring=# select
subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)))
is null;
?column?
----------
t
(1 row)
Ok, so my function call to subunitId evaluates to null. Just to confirm
that it's returning null and not one or more blanks, the
second query indeed shows that it is equal to null. Still good.
However....
monitoring=# SELECT count(*) FROM current_status c WHERE
c.subunit_id=subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)));
count
-------
0
(1 row)
This is an odd result. Both sides of that where clause evaluate to
null, so it is null=null, yet this is not evaluating to true
because I'm getting no rows back. Just to confirm that null=null
evaluates to true (double-checking my sanity):
monitoring=# select null=null;
?column?
----------
t
(1 row)
Ok, quite bizarre. And now for the grand finale:
monitoring=# SELECT count(*) FROM current_status c WHERE c.subunit_id is
null AND
subunitId(NULL::varchar,unitId('MYSQL'::varchar,componentId('INFRASTRUCTURE'::varchar)))
is null;
count
-------
1
(1 row)
If you compare them independently to null, you get the match on one
row. You compare them to each other, you get
no rows. And yet, I've just shown that null=null should evaluate to
true.
Can someone smarter than me tell me what I am missing?
-Fran
