Subject: Re: Document NULL

From: David Rowley <[email-masked]>
Date: Thu, 2 May 2024 16:15:47 +1200

On Thu, 2 May 2024 at 03:12, David G. Johnston
<[email-masked]> wrote:
> Attached is a very rough draft attempting this, based on my own thoughts and those expressed by Tom in [1], which largely align with mine.

Thanks for picking this up. I agree that we should have something to
improve this.

It would be good to see some subtitles in this e.g "Three-valued
boolean logic" and document about NULL being unknown, therefore false.
Giving a few examples would be good to, which I think is useful as it
at least demonstrates a simple way of testing these things using a
simple FROMless SELECT, e.g. "SELECT NULL = NULL;".  You could link to
this section from where we document WHERE clauses.

Maybe another subtitle would be "GROUP BY / DISTINCT clauses with NULL
values", and then explain that including some other examples using
"SELECT 1 IS NOT DISTINCT FROM NULL;" to allow the reader to
experiment and learn by running queries.

You likely skipped them due to draft status, but if not, references
back to other sections likely could do with links back to that
section, e.g "amount of precipitation Hayward" is not on that page.
Without that you're assuming the reader is reading the documents
linearly.

Another section might briefly explain about disallowing NULLs in
columns with NOT NULL constraints, then link to wherever we properly
document those.

typo:

+ <title>Handling Unkowns (NULL)</title>

Maybe inject "Values" after Unknown.

Let's bash it into shape a bit more before going any further on actual wording.

David

---

From: Tom Lane <[email-masked]>
Date: Thu, 02 May 2024 00:47:03 -0400

> Let's bash it into shape a bit more before going any further on actual wording.

FWIW, I want to push back on the idea of making it a tutorial section.
I too considered that, but in the end I think it's a better idea to
put it into the "main" docs, for two reasons:

1. I want this to be a fairly official/formal statement about how we
treat nulls; not that it has to be written in dry academic style or
whatever, but it has to be citable as The Reasons Why We Act Like That,
so the tutorial seems like the wrong place.

2. I think we'll soon be cross-referencing it from other places in the
docs, even if we don't actually move existing bits of text into it.
So again, cross-ref'ing the tutorial doesn't feel quite right.

Those arguments don't directly say where it should go, but after
surveying things a bit I think it could become section 5.2 in
ddl.sgml, between "Table Basics" and "Default Values".  Another
angle could be to put it after "Default Values" --- except that
that section already assumes you know what a null is.

I've not read any of David's text in detail yet, but that's my
two cents on where to place it.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 2 May 2024 08:23:44 -0700

> put it into the "main" docs, for two reasons:
Version 2 attached.  Still a draft, focused on topic picking and overall
structure.  Examples and links planned plus the usual semantic markup stuff.

I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data".  Don't tell Robert.

The "Data Basics" sub-section lets us readily slide this Chapter into the
main flow and here the NULL discussion feels like a natural fit.  In
hindsight, the lack of a Data chapter in a Database manual seems like an
oversight.  One easily made because we assume if you are here you "know"
what data is, but there is still stuff to be discussed, if nothing else to
establish a common understanding between us and our users.

David J.
From 7798121992154edab4768d7eab5a89be04730b2f Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email-masked]>
Date: Wed, 1 May 2024 07:45:48 -0700
Subject: [PATCH] Document NULL

---
 doc/src/sgml/data.sgml     | 169 +++++++++++++++++++++++++++++++++++++
 doc/src/sgml/filelist.sgml |   1 +
 doc/src/sgml/postgres.sgml |   1 +
 3 files changed, 171 insertions(+)
 create mode 100644 doc/src/sgml/data.sgml

diff --git a/doc/src/sgml/data.sgml b/doc/src/sgml/data.sgml
new file mode 100644
index 0000000000..2b09382494
--- /dev/null
+++ b/doc/src/sgml/data.sgml
@@ -0,0 +1,169 @@
+<chapter id="data">
+ <title>Data</title>
+
+ <para>
+  This chapter provides definitions for, and an overview of, data.
+  It discusses the basic design of the metadata related to values
+  and then goes on to describe the special value NULL which typically
+  represents &quot;unknown&quot;
+ </para>
+
+ <sect1 id="data-basics">
+  <title>Data Basics</title>
+  <para>
+   All literals, columns, variables, and expression results in PostgreSQL
+   are typed, which are listed in the next chapter.  Literals and columns
+   must only use one of the concrete types while variables can use
+   either a concrete type or a pseudo-type.  Expression results
+   are limited to concrete types and the pseudo-type record described below.
+  </para>
+  <para>
+   The pseudo-types prefixed with &quot;any&quot; implement polymorphism
+   in PostgreSQL.  Polymorphism allows a single function specification
+   to act on multiple concrete types.  At runtime, the function body
+   associates concrete types to all polymorphic types based upon the
+   conrete argument of its inputs. See ... for more details.
+  </para>
+  <para>
+   The record pseudo-type is also polymorphic in nature but allows
+   the caller of the function to specify the row-like structure of
+   output within the containing query. See ... for more details.
+   The ROW(...) expression (see ...) will also produce a record
+   result comprised of the named columns.
+  </para>
+ </sect1>
+
+  <sect1 id="data-null">
+   <title>Unknown Values (NULL)</title>
+   <para>
+    This section first introduces the meaning of NULL and then goes
+    on to explain how different parts of the system behave when faced
+    with NULL input.
+   </para>
+
+  <sect2 id="data-null-model">
+   <title>NULL in Data Models</title>
+   <para>
+    Generally NULL is assumed to mean "unknown".  However,
+    in practice meaning comes from context and so a model design may state that
+    NULL is to be used to represent "not applicable" - i.e., that a value is not
+    even possible.  SQL has only the single value NULL while there are multiple
+    concepts that people have chosen to apply it to.  In any case the behavior
+    of the system when dealing with NULL is the same regardless of the meaning
+    the given to it in the surrounding context.
+   </para>
+   <para>
+    NULL also takes on a literal meaning of "not found" when produced as the
+    result of an outer join.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-usage">
+   <title>NULL Usage</title>
+   <para>
+    As NULL is treated as a data value it, like all values, must have
+    a data type.  NULL is a valid value for all data types.
+   </para>
+
+   <para>
+    A NULL literal is written as unquoted NULL.  Its type is unknown but
+    can be cast to any concrete data type.  The [type 'string'] syntax
+    however will not work as there is no way to express NULL using single
+    quotes and unlike.
+   </para>
+
+   <para>
+    The presence of NULL in the system results in three-valued logic.
+    In binary logic every outcome is either true or false.  In
+    three-valued logic unknown, represented using a NULL value, is
+    also an outcome.  Aspects of the system that branch based upon
+    whether a condition variable is true or false thus must also
+    decide how to behave when then condition is NULL.  The remaining
+    sub-sections summarize these decisions.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-cardinalrule">
+    <title>The Cardinal Rule of NULL</title>
+   <para>
+    The cardinal rule, NULL is never equal or unequal to any non-null
+    value (or itself).  [NULL = anything yields NULL].
+    Checking for NULL has an explicit test documented
+    [here] and additionally there are distinctness tests that treat NULL like
+    a value equal to itself and unequal to any other value.
+   </para>
+   <para>
+    There is also a cardinal warning: when dealing with composite types
+    [see ...] the expressions; composite IS NULL and composite IS NOT NULL
+    are not the opposites of each other in the case where some of the
+    composite's fields are NULL.  Write NOT(composite IS NULL) instead.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-opertions">
+   <title>NULLs in Operations</title>
+   <para>
+    As a general expectation, operator invocation expressions where one of inputs
+    will result in a NULL output.
+    [add: 1 + NULL yields NULL]
+    [concatenate: 'text' || NULL yields NULL]
+    Operators that behave otherwise should document their deviation from this norm.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-functions">
+   <title>NULLs in Normal Function Calls</title>
+   <para>
+    Function specifications has a "strictness" attribute that, when set to "strict"
+    (a.k.a. "null on null input") will tell the executor to return NULL for any
+    function call having at least one NULL input value, without executing the
+    function.  Most SQL standard functions behave strictly, and in many cases
+    non-standard functions may exist that do not.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-aggregates">
+   <title>NULLs in Aggregate and Window Processing</title>
+   <para>  (needs research)
+    When executing an aggregate or window function the state tracking
+    component will remain unchanged even if the underlying processing
+    function returns NULL, whether from being defined strict (see above)
+    or simply returns a NULL value upon execution.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-filters">
+   <title>NULLs in Filters</title>
+   <para>
+    A WHERE clause that evaluates to NULL for a given row will exclude that row.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-constraints">
+   <title>NULLs in Constraints</title>
+   <para>
+    It is possible to define validation expressions on
+    tables that ensure only values passing those expressions are inserted.  While
+    this seems like it would behave the same as a filter, the choice here,
+    when an expression evaulates to NULL, is allow the row to be inserted.  See
+    [check constraints] in create table for details.
+   </para>
+   <para>
+    The NOT NULL column constraint is largely syntax sugar for the corresponding
+    column IS NOT NULL check constraint, though there are metadata differences
+    described in create table.
+   </para>
+  </sect2>
+
+  <sect2 id="data-null-grouping">
+   <title>NULLs When Grouping</title>
+   <para>
+    In the context of both DISTINCT and GROUP BY it is necessary that all inputs
+    resolve to being either equal to or not equal to all other values.  These features
+    use distinctness (see ...) instead of simple equality in order to handle
+    NULL like a definite value equal to itself and unequal to all other values.
+   </para>
+  </sect2>
+ </sect1>
+
+</chapter>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 38ec362d8f..2b4fc79b8a 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -14,6 +14,7 @@
 
 <!-- user's guide -->
 <!ENTITY array      SYSTEM "array.sgml">
+<!ENTITY data       SYSTEM "data.sgml">
 <!ENTITY datatype   SYSTEM "datatype.sgml">
 <!ENTITY ddl        SYSTEM "ddl.sgml">
 <!ENTITY dml        SYSTEM "dml.sgml">
diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml
index ec9f90e283..bd0e4007ee 100644
--- a/doc/src/sgml/postgres.sgml
+++ b/doc/src/sgml/postgres.sgml
@@ -101,6 +101,7 @@ break is not needed in a wider output rendering.
   </partintro>
 
   &syntax;
+  &data;
   &ddl;
   &dml;
   &queries;
-- 
2.34.1

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 03 May 2024 08:47:20 +0200

On Thu, 2024-05-02 at 08:23 -0700, David G. Johnston wrote:
> Version 2 attached.  Still a draft, focused on topic picking and overall structure.

I'm fine with most of the material (ignoring ellipses and typos), except this:

I see a substantial difference there:

  SELECT conname, contype,
         pg_get_expr(conbin, 'not_null'::regclass)
  FROM pg_constraint
  WHERE conrelid = 'not_null'::regclass;

         conname        │ contype │   pg_get_expr    
  ══════════════════════╪═════════╪══════════════════
   check_null           │ c       │ (id IS NOT NULL)
   not_null_id_not_null │ n       │ ∅
  (2 rows)

There is also the "attnotnull" column in "pg_attribute".

I didn't try it, but I guess that the performance difference will be measurable.
So I wouldn't call it "syntactic sugar".

Perhaps: The behavior of the NOT NULL constraint is like that of a check
constraint with IS NOT NULL.

Yours,
Laurenz Albe

---

From: jian he <[email-masked]>
Date: Fri, 3 May 2024 16:14:27 +0800

> +    described in create table.

the system does not translate (check constraint column IS NOT NULL)
to NOT NULL constraint,
at least in domain.

for example:
create domain connotnull integer;
alter domain connotnull add not null;
\dD connotnull

drop domain connotnull cascade;
create domain connotnull integer;
alter domain connotnull add check (value is not null);
\dD