Thread: plpgsql help - nested loops

I'm trying to compare the fields of two loop record variables.  The loops
are nested, the compare is done inside the inner one.  If given this:
 raise notice ''sloop.serialnumber=%, rloop.serialnumber=%'',   sloop.serialnumber, rloop.serialnumber;
 if sloop.serialnumber=rloop.serialnumber then   raise notice '' SN match''; else   raise notice '' SN mis-match''; end
if;

the output is
 NOTICE:  sloop.serialnumber=<NULL>, rloop.serialnumber=<NULL> NOTICE:   SN mis-match

However, if you change the comparison to
 if sloop.serialnumber=rloop.serialnumber or   (sloop.serialnumber=null and rloop.serialnumber=null) then   raise
notice'' SN match''; else   raise notice '' SN mis-match''; end if;
 

everything is fine.

Question: does null=null evaluate to true, false, or null?  If in psql you
do SELECT null=null; it returns true.  Am I missing something?  Attached
is the full trigger code..

Thanks,
-Cedar

See:
http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic

I think it might clarify the situation.  If you find any errors on the page, 
please let me know.

Ordinary equality comparions (=,>=,<=) between a NULL and anything else 
always results in NULL, normally.  NULL in boolean comparisons, IS, IS NOT, 
AND, and OR can give different results.  Your equals comparison was always 
giving NULL when comparing the two fields together directly.  You were 
getting a confusing result when comparing equality of one field directly with 
NULL, which normally would be NULL too except that PostgreSQL is doing some 
rewriting of the expressing behind your back, changing anything it sees with 
an equality operator and a literal NULL into a boolean comparison "field IS 
NULL" (true if field is NULL) instead of "field = NULL" (normally always NULL 
in the absence of a write you aren't seeing within the database).

I think that is what the deal is.  The proper way to check for null, if it is 
a possibility (no NOT NULL constraint), is to use boolean operators (IS, IS 
NOT) explicitly to check.


On Tuesday 02 January 2001 18:20, Cedar Cox wrote:

> > I'm trying to compare the fields of two loop record variables.  The loops
> are nested, the compare is done inside the inner one.  If given this:
>
>   raise notice ''sloop.serialnumber=%, rloop.serialnumber=%'',
>     sloop.serialnumber, rloop.serialnumber;
>
>   if sloop.serialnumber=rloop.serialnumber then
>     raise notice '' SN match'';
>   else
>     raise notice '' SN mis-match'';
>   end if;
>
> the output is
>
>   NOTICE:  sloop.serialnumber=<NULL>, rloop.serialnumber=<NULL>
>   NOTICE:   SN mis-match
>
> However, if you change the comparison to
>
>   if sloop.serialnumber=rloop.serialnumber or
>     (sloop.serialnumber=null and rloop.serialnumber=null) then
>     raise notice '' SN match'';
>   else
>     raise notice '' SN mis-match'';
>   end if;
>
> everything is fine.
>
> Question: does null=null evaluate to true, false, or null?  If in psql you
> do SELECT null=null; it returns true.  Am I missing something?  Attached
> is the full trigger code..
>
> Thanks,
> -Cedar

----------------------------------------
Content-Type: TEXT/PLAIN; name="matchitems"
Content-Transfer-Encoding: BASE64
Content-Description: 
----------------------------------------

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

On Wed, 3 Jan 2001, Robert B. Easter wrote:

> See:
> http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic
> 
> I think it might clarify the situation.  If you find any errors on the page, 
> please let me know.
> 
> Ordinary equality comparions (=,>=,<=) between a NULL and anything else 
> always results in NULL, normally.  NULL in boolean comparisons, IS, IS NOT, 
> AND, and OR can give different results.  Your equals comparison was always 
> giving NULL when comparing the two fields together directly.  You were 
> getting a confusing result when comparing equality of one field directly with 
> NULL, which normally would be NULL too except that PostgreSQL is doing some 
> rewriting of the expressing behind your back, changing anything it sees with 
> an equality operator and a literal NULL into a boolean comparison "field IS 
> NULL" (true if field is NULL) instead of "field = NULL" (normally always NULL 
> in the absence of a write you aren't seeing within the database).
> 
> I think that is what the deal is.  The proper way to check for null, if it is 
> a possibility (no NOT NULL constraint), is to use boolean operators (IS, IS 
> NOT) explicitly to check.

Ok, makes sense.. This should be, if there isn't, documented somewhere
about the rewriting.  So I guess the proper way would be more like the
second example except using 'is':
 if sloop.serialnumber=rloop.serialnumber or   (sloop.serialnumber is null and rloop.serialnumber is null) then   raise
notice'' SN match''; else   raise notice '' SN mis-match''; end if;
 

Of course, I'd use isnull instead.  I wish I didn't have to type that
little bit more just to compare two variables.. ;)

Thanks,
-Cedar