Re: BUG #18451: NULL fails to coerce to string when performing string comparison

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> On Wed, 1 May 2024 at 10:30, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Yeah, there's a documentation gap here. I dug around a little and
>> really couldn't find anything anywhere in our SGML docs that explains
>> NULL in any detail; we tend to assume that you've already heard of it.

> What is really generic enough about SQL NULLs to put somewhere
> generic?

I think the key points I'd want to get across include:

* Any value can be NULL rather than a "normal" value of its datatype.

* There are various ways to think about the meaning of NULL, but
an often-useful viewpoint is that it represents an unknown value.

* A majority of SQL operations, including nearly all built-in
functions and operators, are strict meaning they yield NULL
if any input is NULL. This is consistent with interpreting
NULL as "unknown".

Depending on how ambitious we wanted to be, we could add
examples illustrating these points. For instance we could
explain the behavior of the boolean operators (such as
"true OR null => true", "true AND null => null") as being
consistent with the "unknown" interpretation. Some of this
material could be moved or copied from existing text. There
is attraction in centralizing the treatment, but on the
other hand those examples are all pretty on-point where
they are. I wouldn't have a problem with being a bit
repetitious, though. It's not like these facts are going
to change and need updates.

> I mean NULL = NULL is NULL rather than true, but NULLs are
> treated as equal in DISTINCT and GROUP BY.

It would not hurt to say that the SQL standard isn't 100%
logically consistent about how it handles NULLs. But this
particular point isn't that bad: we could introduce the
definition of distinctness ("IS [NOT] DISTINCT FROM") and
then say that grouping operations use that behavior rather
than what "=" does.

The bottom line is that I think nowadays a lot of people
learn SQL from our documentation, rather than coming to
our docs with pre-existing SQL knowledge. So we need to
fill in these sorts of explanatory gaps.

regards, tom lane