null != null ???
От | Fran Fabrizio |
---|---|
Тема | null != null ??? |
Дата | |
Msg-id | 3BD97F0E.A19DDFE5@mmrd.com обсуждение исходный текст |
Ответы |
Re: null != null ???
Re: null != null ??? Re: null != null ??? |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: