From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Darryl Dixon <darryl.dixon@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18451: NULL fails to coerce to string when performing string comparison
Date: Tue, 30 Apr 2024 22:53:45 -0400
Message-ID: <1859814.1714532025@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvoeD9rWT0Wd6edZVJfWgaK5Nc2B7yz-SQ65KNpSjovgJg@mail.gmail.com>
References: <18451-dc130ad59b7bd7c9@postgresql.org>
<CAKFQuwZ0p7v7QOd_oH37P88dDUAV-wiRanE9K=6qsjG9QN+4tg@mail.gmail.com>
<CAMfewD3G_prBNTfQF183CWA=mObBTzprczVMvMSWk3T0i4++Xg@mail.gmail.com>
<CAKFQuwZvPW+EH1cDCjti8EC_ke5-84Z6HBPjxfos4q4QUxuv-A@mail.gmail.com>
<1825308.1714516246@sss.pgh.pa.us>
<CAApHDvoeD9rWT0Wd6edZVJfWgaK5Nc2B7yz-SQ65KNpSjovgJg@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Wed, 1 May 2024 at 10:30, Tom Lane <tgl@sss.pgh.pa.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, david.g.johnston@gmail.com, darryl.dixon@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18451: NULL fails to coerce to string when performing string comparison
In-Reply-To: <1859814.1714532025@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox