Re: Is NULL equal to NULL or not? - Mailing list pgsql-general
From | Tom Lane |
---|---|
Subject | Re: Is NULL equal to NULL or not? |
Date | |
Msg-id | 21280.1039481214@sss.pgh.pa.us Whole thread Raw |
In response to | Re: Is NULL equal to NULL or not? (Mike Mascari <mascarm@mascari.com>) |
List | pgsql-general |
Mike Mascari <mascarm@mascari.com> writes: >> On Mon, 9 Dec 2002 jco@cornelius-olsen.dk wrote: >>> I've noticed that >>> select NULL=NULL; >>> yields TRUE. It is also possible to select rows in this manner. >> >> That's NOT because null EQUALS null, but because it allowed you to assign >> one null value to another. which technically can be done, so it came out >> true. > It *is* because null EQUALS null, on version 7.1. '=' is the > comparison operator. Not quite ... thanks for playing. NULL has never equaled NULL, in any version of PG AFAICT --- the comparison operator would always yield NULL if either input was NULL. That behavior is per SQL spec. Beginning in release 6.5 there was a special kluge for Access compatibility, originally this two-line patch: 1999-03-14 00:14 momjian * src/backend/parser/: gram.c, gram.y: I have a problem with Access97 not working properly when entering new records using a sub form, i.e. entering a new order/orderlines or master and detail tables. The problem is caused by a SQL statement that Access97 makes involving NULL. The syntax that fails is "column_name" = NULL. The following attachment was provided by -Jose'-. It contains a very small enhancement to gram.y that will allow Access97 to work properly with sub forms. Can this enhancement be added to release 6.5? <<gram.patch>> Thanks, Michael which basically hacked the parser so that the syntax expression = NULL would be treated as expression IS NULL But note that this is a *parse time* transformation, and would only fire when the right-hand operand of '=' is the literal keyword NULL. (Thus, it does not fire in jco's plpgsql example, where the operands are both plpgsql variables.) This should never have been made the default behavior IMHO, and certainly not the sole behavior, because it blatantly broke SQL compatibility, not to mention violating any usable concept of what NULL means. As of 7.2 it's a disabled-by-default option, which is the way it should have been handled to begin with (though I suppose the lack of a flexible runtime-parameter-configuration mechanism back then might have had something to do with the original decision). But 6.5, 7.0, and 7.1 lasted long enough to allow a lot of people to become confused :-( NULL is not "=" to NULL. It is not "<>" NULL, either. It's off in its own little universe. Except for a very small number of special syntaxes like "x IS NULL" and "x IS NOT NULL", any standard SQL operation applied to one or more NULL inputs will yield a NULL output. In particular equality comparison will yield NULL. (It may help you to realize that "IS" is not a kind of equals operator here --- rather the entire phrase "IS NULL" or "IS NOT NULL" is a special operator. For comparison, "x IS 3" is not an acceptable way of spelling "x = 3".) regards, tom lane
pgsql-general by date: