Document NULL
Hi,
Over in [1]/messages/by-id/1859814.1714532025@sss.pgh.pa.us it was rediscovered that our documentation assumes the reader
is familiar with NULL. It seems worthwhile to provide both an introduction
to the topic and an overview of how this special value gets handled
throughout the system.
Attached is a very rough draft attempting this, based on my own thoughts
and those expressed by Tom in [1]/messages/by-id/1859814.1714532025@sss.pgh.pa.us, which largely align with mine.
I'll flesh this out some more once I get support for the goal, content, and
placement. On that point, NULL is a fundamental part of the SQL language
and so having it be a section in a Chapter titled "SQL Language" seems to
fit well, even if that falls into our tutorial. Framing this up as
tutorial content won't be that hard, though I've skipped on examples and
such pending feedback. It really doesn't fit as a top-level chapter under
part II nor really under any of the other chapters there. The main issue
with the tutorial is the forward references to concepts not yet discussed
but problem points there can be addressed.
I do plan to remove the entity reference and place the content into
query.sgml directly in the final version. It is just much easier to write
an entire new section in its own file.
David J.
Attachments:
v1-0001-Document-NULL.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Document-NULL.patchDownload
From a068247e92e620455a925a0ae746adc225ae1339 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 1 May 2024 07:45:48 -0700
Subject: [PATCH] Document NULL
---
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/null.sgml | 79 ++++++++++++++++++++++++++++++++++++++
doc/src/sgml/query.sgml | 2 +
3 files changed, 82 insertions(+)
create mode 100644 doc/src/sgml/null.sgml
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 38ec362d8f..ac4fd52978 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -10,6 +10,7 @@
<!-- tutorial -->
<!ENTITY advanced SYSTEM "advanced.sgml">
<!ENTITY query SYSTEM "query.sgml">
+<!ENTITY null SYSTEM "null.sgml">
<!ENTITY start SYSTEM "start.sgml">
<!-- user's guide -->
diff --git a/doc/src/sgml/null.sgml b/doc/src/sgml/null.sgml
new file mode 100644
index 0000000000..5f95b2494e
--- /dev/null
+++ b/doc/src/sgml/null.sgml
@@ -0,0 +1,79 @@
+<sect1 id="tutorial-null">
+ <title>Handling Unkowns (NULL)</title>
+
+ <indexterm zone="tutorial-null">
+ <primary>NULL</primary>
+ </indexterm>
+
+ <para>
+ Looking again at our example weather data you will note that we do not know
+ the amount of precipitation Hayward. We communicated that implicitly by
+ not including the prcp column in the insert. Explicitly, we can communicate
+ this fact by writing. [example using null]. When a column is not specified
+ in an insert the default value for that column is recorded and the default
+ default value is NULL.
+ </para>
+
+ <para>
+ As a value NULL crops up all throughout the database and interacts with many
+ features. The main portion of this book will detail the interactions specific
+ features have with NULL but it is helpful to have a reference page where one
+ can get an overview.
+ </para>
+
+ <para>
+ First, like all values, NULLs are typed. But since any value can be unknown
+ NULL is a valid value for all data types.
+ </para>
+
+ <para>
+ Second, when speaking 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>
+ The cardinal rule, NULL is never equal or unequal to any non-null
+ value; and when asked to be combined with a known value in an operation the
+ result of the operation becomes unknown. e.g., both 1 = NULL and 1 + NULL
+ result in NULL. Exceptions to this are documented. See [chapter] for
+ details on how to test for null. Specifically, note that concept of
+ distinctness is introduced to allow for true/false equality tests.
+ </para>
+
+ <para>
+ Extending from the previous point, function calls are truly a mixed bag.
+ Aggregate functions in particular will usually just ignore NULL inputs
+ instead of forcing the entire aggregate result to NULL. 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.
+ </para>
+
+ <para>
+ A WHERE clause that evaluates to NULL for a given row will exclude that row.
+ This was demonstrated in the tutorial query where cities with prcp > 0 were
+ requested and Hayward was not returned due to this and the cardinal rule.
+ </para>
+
+ <para>
+ While not yet discussed, 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 as a WHERE clause on a table, 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>
+ 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 instead of simple equality in order to handle
+ NULL like a definite value equal to itself and unequal to all other values.
+ </para>
+
+</sect1>
diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
index 59962d6e85..f9a8686365 100644
--- a/doc/src/sgml/query.sgml
+++ b/doc/src/sgml/query.sgml
@@ -907,4 +907,6 @@ DELETE FROM <replaceable>tablename</replaceable>;
</para>
</sect1>
+&null;
+
</chapter>
--
2.34.1
On Wed, May 1, 2024, 16:13 David G. Johnston <david.g.johnston@gmail.com>
wrote:
Hi,
Over in [1] it was rediscovered that our documentation assumes the reader
is familiar with NULL. It seems worthwhile to provide both an introduction
to the topic and an overview of how this special value gets handled
throughout the system.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.I'll flesh this out some more once I get support for the goal, content,
and placement. On that point, NULL is a fundamental part of the SQL
language and so having it be a section in a Chapter titled "SQL Language"
seems to fit well, even if that falls into our tutorial. Framing this up
as tutorial content won't be that hard, though I've skipped on examples and
such pending feedback. It really doesn't fit as a top-level chapter under
part II nor really under any of the other chapters there. The main issue
with the tutorial is the forward references to concepts not yet discussed
but problem points there can be addressed.I do plan to remove the entity reference and place the content into
query.sgml directly in the final version. It is just much easier to write
an entire new section in its own file.David J.
"The cardinal rule, NULL is never equal or unequal to any non-null value."
This implies that a NULL is generally equal or unequal to another NULL.
While this can be true (e.g. in aggregates), in general it is not. Perhaps
immediately follow it with something along the lines of "In most cases NULL
is also not considered equal or unequal to any other NULL (i.e. NULL = NULL
will return NULL), but there are occasional exceptions, which will be
explained further on."
Regards
Thom
On Wed, May 1, 2024 at 8:12 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
Hi,
Over in [1] it was rediscovered that our documentation assumes the reader
is familiar with NULL. It seems worthwhile to provide both an introduction
to the topic and an overview of how this special value gets handled
throughout the system.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.I'll flesh this out some more once I get support for the goal, content,
and placement. On that point, NULL is a fundamental part of the SQL
language and so having it be a section in a Chapter titled "SQL Language"
seems to fit well, even if that falls into our tutorial. Framing this up
as tutorial content won't be that hard, though I've skipped on examples and
such pending feedback. It really doesn't fit as a top-level chapter under
part II nor really under any of the other chapters there. The main issue
with the tutorial is the forward references to concepts not yet discussed
but problem points there can be addressed.I do plan to remove the entity reference and place the content into
query.sgml directly in the final version. It is just much easier to write
an entire new section in its own file.
Reviewed the documentation update and it's quite extensive, but I think
it's better to include some examples as well.
Regards
Kashif Zeeshan
Show quoted text
David J.
On Thu, 2 May 2024 at 03:12, David G. Johnston
<david.g.johnston@gmail.com> 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
David Rowley <dgrowleyml@gmail.com> writes:
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
On Wed, May 1, 2024 at 9:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
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:
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.
Attachments:
v2-0001-Document-NULL.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Document-NULL.patchDownload
From 7798121992154edab4768d7eab5a89be04730b2f Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
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 "unknown"
+ </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 "any" 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
Hi David
I reviewed the documentation and it's very detailed.
Thanks
Kashif Zeeshan
Bitnine Global
On Thu, May 2, 2024 at 8:24 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
Show quoted text
On Wed, May 1, 2024 at 9:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
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: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.
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:
+ 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.
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
On Fri, May 3, 2024 at 2:47 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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:
+ 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.
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
On Fri, May 3, 2024 at 1:14 AM jian he <jian.universality@gmail.com> wrote:
On Fri, May 3, 2024 at 2:47 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote: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:
+ 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.
the system does not translate (check constraint column IS NOT NULL)
to NOT NULL constraint,
at least in domain.
I'll change this but I was focusing on the fact you get identical
user-visible behavior with not null and a check(col is not null). Chain of
thought being we discuss the is not null operator (indirectly) already and
so not null, which is syntax as opposed to an operation/expression, can
leverage that explanation as opposed to getting its own special case. I'll
consider this some more and maybe mention the catalog dynamics a bit as
well, or at least point to them.
drop domain connotnull cascade;
create domain connotnull integer;
alter domain connotnull add check (value is not null);
\dD
This reminds me, I forgot to add commentary regarding defining a not null
constraint on a domain but the domain type surviving a left join but having
a null value.
David J.
On 02.05.24 17:23, David G. Johnston wrote:
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".
Please, let's not.
A stylistic note: "null" is an adjective. You can talk about a "null
value" or a value "is null". These are lower-cased (or maybe
title-cased). You can use upper-case when referring to SQL syntax
elements (in which case also tag it with something like <literal>), and
also to the C-language symbol (tagged with <symbol>). We had recently
cleaned this up, so I think the rest of the documentation should be
pretty consistent about this.
On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut <peter@eisentraut.org>
wrote:
On 02.05.24 17:23, David G. Johnston wrote:
Version 2 attached. Still a draft, focused on topic picking and overall
structure. Examples and links planned plus the usual semantic markupstuff.
I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data".Please, let's not.
If a committer wants to state the single place in the documentation to put
this I'm content to put it there while leaving my reasoning of choices in
place for future bike-shedding. My next options to decide between are the
appendix or the lead chapter in Data Types. It really doesn't fit inside
DDL IMO which is the only other suggestion I've seen (and an uncertain, or
at least unsubstantiated, one) and a new chapter meets both criteria Tom
laid out, so long as this is framed as more than just having to document
null values.
A stylistic note: "null" is an adjective. You can talk about a "null
value" or a value "is null".
Will do.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut <peter@eisentraut.org>
wrote:On 02.05.24 17:23, David G. Johnston wrote:
I chose to add a new sect1 in the user guide (The SQL Language) chapter,
"Data".
Please, let's not.
If a committer wants to state the single place in the documentation to put
this I'm content to put it there while leaving my reasoning of choices in
place for future bike-shedding. My next options to decide between are the
appendix or the lead chapter in Data Types. It really doesn't fit inside
DDL IMO which is the only other suggestion I've seen (and an uncertain, or
at least unsubstantiated, one) and a new chapter meets both criteria Tom
laid out, so long as this is framed as more than just having to document
null values.
I could see going that route if we actually had a chapter's worth of
material to put into "Data". But we don't, there's really only one
not-very-long section. Robert has justifiably complained about that
sort of thing elsewhere in the docs, and I don't want to argue with
him about why it'd be OK here.
Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls. Sure,
it's not totally ideal, but noplace is going to be entirely
perfect. I can see some attraction in dropping it under Data Types,
but (a) null is a data-type-independent concept, and (b) the
chapters before that are just full of places that assume you have
heard of nulls. Putting it in an appendix is similarly throwing
to the wind any idea that you can read the documentation in order.
Really, even the syntax chapter has some mentions of nulls.
If we did have a "Data" chapter there would be a case for
putting it as the *first* chapter of Part II.
I suppose we could address the nonlinearity gripe with a bunch
of cross-reference links, in which case maybe something under
Data Types is the least bad approach.
regards, tom lane
On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Fri, May 3, 2024 at 7:10 AM Peter Eisentraut <peter@eisentraut.org>
wrote:On 02.05.24 17:23, David G. Johnston wrote:
I chose to add a new sect1 in the user guide (The SQL Language)
chapter,
"Data".
Please, let's not.
If a committer wants to state the single place in the documentation to
put
this I'm content to put it there while leaving my reasoning of choices in
place for future bike-shedding. My next options to decide between arethe
appendix or the lead chapter in Data Types. It really doesn't fit inside
DDL IMO which is the only other suggestion I've seen (and an uncertain,or
at least unsubstantiated, one) and a new chapter meets both criteria Tom
laid out, so long as this is framed as more than just having to document
null values.I could see going that route if we actually had a chapter's worth of
material to put into "Data". But we don't, there's really only one
not-very-long section. Robert has justifiably complained about that
sort of thing elsewhere in the docs, and I don't want to argue with
him about why it'd be OK here.
OK. I was hopeful that once the Chapter existed the annoyance of it being
short would be solved by making it longer. If we ever do that, moving this
section under there at that point would be an option.
Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls.
I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.
Putting it in an appendix is similarly throwing
to the wind any idea that you can read the documentation in order.
I think we can keep the entire camel out of the tent while letting it get a
whiff of what is inside. It would be a summary reference linked to from
the various places that mention null values.
https://en.wikipedia.org/wiki/Camel%27s_nose
I suppose we could address the nonlinearity gripe with a bunch
of cross-reference links, in which case maybe something under
Data Types is the least bad approach.
Yeah, there is circularity here that is probably impossible to
completely resolve.
David J.
On Fri, May 3, 2024 at 9:00 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls.
I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.
v3 Attached.
Probably at the 90% complete mark. Minimal index entries, not as thorough
a look-about of the existing documentation as I'd like. Probably some
wording and style choices to tweak. Figured better to get feedback now
before I go into polish mode. In particular, tweaking and re-running the
examples.
Yes, I am aware of my improper indentation for programlisting and screen. I
wanted to be able to use the code folding features of my editor. Those can
be readily un-indented in the final version.
The changes to func.sgml is basically one change repeated something like 20
times with tweaks for true/false. Plus moving the discussion regarding the
SQL specification into the new null handling section.
It took me doing this to really understand the difference between row
constructors and composite typed values, especially since array
constructors produce array typed values and the constructor is just an
unimportant implementation option while row constructors introduce
meaningfully different behaviors when used.
My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.
David J.
Attachments:
v3-0001-Document-NULL.patchtext/x-patch; charset=US-ASCII; name=v3-0001-Document-NULL.patchDownload
From bea784bd683f7e022dbfb3d72832d09fc7754913 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 1 May 2024 07:45:48 -0700
Subject: [PATCH] Document NULL
---
doc/src/sgml/ddl.sgml | 2 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/func.sgml | 268 ++++++-------
doc/src/sgml/nullvalues.sgml | 719 +++++++++++++++++++++++++++++++++++
4 files changed, 837 insertions(+), 153 deletions(-)
create mode 100644 doc/src/sgml/nullvalues.sgml
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 026bfff70f..68a0fe698d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -168,6 +168,8 @@ DROP TABLE products;
</para>
</sect1>
+ &nullvalues;
+
<sect1 id="ddl-default">
<title>Default Values</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 38ec362d8f..882752e88f 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -21,6 +21,7 @@
<!ENTITY indices SYSTEM "indices.sgml">
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
+<!ENTITY nullvalues SYSTEM "nullvalues.sgml">
<!ENTITY parallel SYSTEM "parallel.sgml">
<!ENTITY perform SYSTEM "perform.sgml">
<!ENTITY queries SYSTEM "queries.sgml">
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 17c44bc338..98fba7742c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23295,7 +23295,8 @@ MERGE INTO products p
This section describes the <acronym>SQL</acronym>-compliant subquery
expressions available in <productname>PostgreSQL</productname>.
All of the expression forms documented in this section return
- Boolean (true/false) results.
+ <link linkend="nullvalues">three-valued</link> typed
+ results (true, false, or null).
</para>
<sect2 id="functions-subquery-exists">
@@ -23357,19 +23358,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
+ subquery, which must return exactly one column. The result of <token>IN</token>
+ is <quote>false</quote> if the subquery returns no rows, otherwise the left-hand expression
is evaluated and compared to each row of the subquery result.
- The result of <token>IN</token> is <quote>true</quote> if any equal subquery row is found.
- The result is <quote>false</quote> if no equal row is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any equal subquery row is found.
+ The result is <quote>false</quote> if no equal row is found.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand row yields
- null, the result of the <token>IN</token> construct will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together.
</para>
<para>
@@ -23386,21 +23385,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row.
+ The result of <token>IN</token> is <quote>false</quote> if the subquery returns no rows,
+ otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result.
- The result of <token>IN</token> is <quote>true</quote> if any equal subquery row is found.
- The result is <quote>false</quote> if no equal row is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any equal subquery row is found.
+ The result is <quote>false</quote> if no equal row is found.
</para>
<para>
- As usual, null values in the rows are combined per
- the normal rules of SQL Boolean expressions. Two rows are considered
- equal if all their corresponding members are non-null and equal; the rows
- are unequal if any corresponding members are non-null and unequal;
- otherwise the result of that row comparison is unknown (null).
- If all the per-row results are either unequal or null, with at least one
- null, then the result of <token>IN</token> is null.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are OR'd together.
</para>
</sect2>
@@ -23412,20 +23408,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</synopsis>
<para>
- The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
- is evaluated and compared to each row of the subquery result.
- The result of <token>NOT IN</token> is <quote>true</quote> if only unequal subquery rows
- are found (including the case where the subquery returns no rows).
+ The right-hand side is a parenthesized subquery, which must return exactly one column.
+ The result of <token>NOT IN</token> is <quote>true</quote> if the subquery returns no rows,
+ otherwise the left-hand expression is evaluated and compared to each row of the subquery result.
+ The result is <quote>true</quote> if only unequal subquery rows are found.
The result is <quote>false</quote> if any equal row is found.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand row yields
- null, the result of the <token>NOT IN</token> construct will be null, not true.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple inequality
+ tests are OR'd together.
</para>
<para>
@@ -23442,21 +23435,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row.
+ The result of <token>NOT IN</token> is <quote>true</quote> if the subquery returns no rows,
+ otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result.
- The result of <token>NOT IN</token> is <quote>true</quote> if only unequal subquery rows
- are found (including the case where the subquery returns no rows).
+ The result is <quote>true</quote> if only unequal subquery rows are found.
The result is <quote>false</quote> if any equal row is found.
</para>
<para>
- As usual, null values in the rows are combined per
- the normal rules of SQL Boolean expressions. Two rows are considered
- equal if all their corresponding members are non-null and equal; the rows
- are unequal if any corresponding members are non-null and unequal;
- otherwise the result of that row comparison is unknown (null).
- If all the per-row results are either unequal or null, with at least one
- null, then the result of <token>NOT IN</token> is null.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple inequality
+ tests are OR'd together.
</para>
</sect2>
@@ -23470,13 +23460,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
+ subquery, which must return exactly one column. The result of <token>ANY</token> is
+ <quote>false</quote> if the subquery returns no rows, otherwise the left-hand expression
is evaluated and compared to each row of the subquery result using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained.
- The result is <quote>false</quote> if no true result is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any true result is obtained.
+ The result is <quote>false</quote> if no true result is found.
</para>
<para>
@@ -23485,11 +23475,10 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</para>
<para>
- Note that if there are no successes and at least one right-hand row yields
- null for the operator's result, the result of the <token>ANY</token> construct
- will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -23507,16 +23496,19 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row. The result of <token>ANY</token> is
+ <quote>false</quote> if the subquery returns no rows, otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result,
using the given <replaceable>operator</replaceable>.
- The result of <token>ANY</token> is <quote>true</quote> if the comparison
- returns true for any subquery row.
- The result is <quote>false</quote> if the comparison returns false for every
- subquery row (including the case where the subquery returns no
- rows).
- The result is NULL if no comparison with a subquery row returns true,
- and at least one comparison returns NULL.
+ The result is <quote>true</quote> if the comparison returns true for any subquery row.
+ The result is <quote>false</quote> if the comparison returns false for every subquery row.
+ </para>
+
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are OR'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -23534,15 +23526,20 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
+ subquery, which must return exactly one column. The result of <token>ALL</token> is
+ <quote>true</quote> if the subquery returns no rows, otherwise the left-hand expression
is evaluated and compared to each row of the subquery result using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ALL</token> is <quote>true</quote> if all rows yield true
- (including the case where the subquery returns no rows).
+ The result is <quote>true</quote> if all rows yield true.
The result is <quote>false</quote> if any false result is found.
- The result is NULL if no comparison with a subquery row returns false,
- and at least one comparison returns NULL.
+ </para>
+
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -23563,22 +23560,21 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row. The result of <token>ALL</token> is
+ <quote>true</quote> if the subquery returns no rows, otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result,
using the given <replaceable>operator</replaceable>.
- The result of <token>ALL</token> is <quote>true</quote> if the comparison
- returns true for all subquery rows (including the
- case where the subquery returns no rows).
- The result is <quote>false</quote> if the comparison returns false for any
- subquery row.
- The result is NULL if no comparison with a subquery row returns false,
- and at least one comparison returns NULL.
+ The result is <quote>true</quote> if the comparison returns true for all subquery rows.
+ The result is <quote>false</quote> if the comparison returns false for any subquery row.
</para>
<para>
- See <xref linkend="row-wise-comparison"/> for details about the meaning
- of a row constructor comparison.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
+
</sect2>
<sect2 id="functions-subquery-single-row-comp">
@@ -23603,6 +23599,14 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
compared row-wise to the single subquery result row.
</para>
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-composites"/>, the result cannot be <quote>true</quote> in the
+ presence of null valued fields in either the row constructor or the subquery result row, as
+ the individual field tests are AND'd together.
+ Note that <literal>IS DISTINCT FROM</literal> is not an operator.
+ </para>
+
<para>
See <xref linkend="row-wise-comparison"/> for details about the meaning
of a row constructor comparison.
@@ -23670,7 +23674,8 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<productname>PostgreSQL</productname> extensions; the rest are
<acronym>SQL</acronym>-compliant.
All of the expression forms documented in this section return
- Boolean (true/false) results.
+ <link linkend="nullvalues">three-valued</link> boolean typed
+ results (true, false, or null).
</para>
<sect2 id="functions-comparisons-in-scalar">
@@ -23683,24 +23688,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized list
of expressions. The result is <quote>true</quote> if the left-hand expression's
- result is equal to any of the right-hand expressions. This is a shorthand
- notation for
-
-<synopsis>
-<replaceable>expression</replaceable> = <replaceable>value1</replaceable>
-OR
-<replaceable>expression</replaceable> = <replaceable>value2</replaceable>
-OR
-...
-</synopsis>
+ result is equal to any of the right-hand expressions.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand expression yields
- null, the result of the <token>IN</token> construct will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence null values since the multiple equality
+ tests are OR'd together.
</para>
</sect2>
@@ -23714,35 +23708,15 @@ OR
<para>
The right-hand side is a parenthesized list
of expressions. The result is <quote>true</quote> if the left-hand expression's
- result is unequal to all of the right-hand expressions. This is a shorthand
- notation for
-
-<synopsis>
-<replaceable>expression</replaceable> <> <replaceable>value1</replaceable>
-AND
-<replaceable>expression</replaceable> <> <replaceable>value2</replaceable>
-AND
-...
-</synopsis>
+ result is unequal to all of the right-hand expressions.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand expression yields
- null, the result of the <token>NOT IN</token> construct will be null, not true
- as one might naively expect.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of null values since the multiple inequality
+ tests are OR'd together.
</para>
- <tip>
- <para>
- <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
- cases. However, null values are much more likely to trip up the novice when
- working with <token>NOT IN</token> than when working with <token>IN</token>.
- It is best to express your condition positively if possible.
- </para>
- </tip>
</sect2>
<sect2 id="functions-comparisons-any-some">
@@ -23755,30 +23729,26 @@ AND
<para>
The right-hand side is a parenthesized expression, which must yield an
- array value.
- The left-hand expression
+ array value. The result of <token>ANY</token> is
+ <quote>false</quote> if the array has zero element, otherwise
+ the left-hand expression
is evaluated and compared to each element of the array using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained.
- The result is <quote>false</quote> if no true result is found (including the
- case where the array has zero elements).
+ The result is <quote>true</quote> if any true result is obtained.
+ The result is <quote>false</quote> if no true result is found.
</para>
<para>
- If the array expression yields a null array, the result of
- <token>ANY</token> will be null. If the left-hand expression yields null,
- the result of <token>ANY</token> is ordinarily null (though a non-strict
- comparison operator could possibly yield a different result).
- Also, if the right-hand array contains any null elements and no true
- comparison result is obtained, the result of <token>ANY</token>
- will be null, not false (again, assuming a strict comparison operator).
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both elements and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
<token>SOME</token> is a synonym for <token>ANY</token>.
+ <token>IN</token> is equivalent to <literal>= ANY</literal>.
</para>
</sect2>
@@ -23792,26 +23762,27 @@ AND
<para>
The right-hand side is a parenthesized expression, which must yield an
array value.
- The left-hand expression
+ The result of <token>ALL</token> is
+ <quote>true</quote> if the array has zero elements, otherwise
+ the left-hand expression
is evaluated and compared to each element of the array using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ALL</token> is <quote>true</quote> if all comparisons yield true
- (including the case where the array has zero elements).
+ The result is <quote>true</quote> if all comparisons yield true.
The result is <quote>false</quote> if any false result is found.
</para>
<para>
- If the array expression yields a null array, the result of
- <token>ALL</token> will be null. If the left-hand expression yields null,
- the result of <token>ALL</token> is ordinarily null (though a non-strict
- comparison operator could possibly yield a different result).
- Also, if the right-hand array contains any null elements and no false
- comparison result is obtained, the result of <token>ALL</token>
- will be null, not true (again, assuming a strict comparison operator).
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both elements and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
+ </para>
+
+ <para>
+ <token>NOT IN</token> is equivalent to <literal><> ALL</literal>.
</para>
+
</sect2>
<sect2 id="row-wise-comparison">
@@ -23896,20 +23867,11 @@ AND
</synopsis>
<para>
- The SQL specification requires row-wise comparison to return NULL if the
- result depends on comparing two NULL values or a NULL and a non-NULL.
- <productname>PostgreSQL</productname> does this only when comparing the
- results of two row constructors (as in
- <xref linkend="row-wise-comparison"/>) or comparing a row constructor
- to the output of a subquery (as in <xref linkend="functions-subquery"/>).
- In other contexts where two composite-type values are compared, two
- NULL field values are considered equal, and a NULL is considered larger
- than a non-NULL. This is necessary in order to have consistent sorting
- and indexing behavior for composite types.
- </para>
-
- <para>
- Each side is evaluated and they are compared row-wise. Composite type
+ Each side is evaluated and they are compared row-wise.
+ As discussed in <xref linkend="nullvalues-multielementcomparison"/>,
+ null values are treated as being equal to other null values and greater
+ than all non-null values.
+ Composite type
comparisons are allowed when the <replaceable>operator</replaceable> is
<literal>=</literal>,
<literal><></literal>,
diff --git a/doc/src/sgml/nullvalues.sgml b/doc/src/sgml/nullvalues.sgml
new file mode 100644
index 0000000000..d77235a527
--- /dev/null
+++ b/doc/src/sgml/nullvalues.sgml
@@ -0,0 +1,719 @@
+<sect1 id="nullvalues">
+ <title>Null Values</title>
+
+ <indexterm>
+ <primary>null value</primary>
+ </indexterm>
+
+ <para>
+ This section first introduces the concept of null values and then goes
+ on to explain how different parts of the system behave when provided
+ one or more null value inputs. Examples throughout this section
+ can be executed so long as the following table and rows are created first.
+ </para>
+
+ <programlisting>
+ CREATE TABLE null_examples (
+ id bigint PRIMARY KEY,
+ value integer NULL
+ );
+ INSERT INTO null_examples
+ VALUES (1, 1), (2, NULL), (3, 4);
+ </programlisting>
+
+ <sect2 id="nullvalues-model">
+ <title>Meaning</title>
+ <para>
+ Generally a null value is assumed to mean "unknown", but other interpretations
+ are common. A data model design may state that a null value
+ is to be used to represent "not applicable" - i.e., that a value is not
+ even possible. The null value also takes on a literal meaning of "not found"
+ when produced as the result of an outer join.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-usage">
+ <title>Usage</title>
+ <para>
+ A null value, like all values, must have a data type, and is valid for all data types.
+ </para>
+ <para>
+ A null value literal is written as unquoted, case insensitive, NULL.
+ Its type is the pseudo-type unknown but can be cast to any concrete data type.
+ The <link linkend="sql-syntax-constants-generic"><literal>type 'string'</literal></link>
+ syntax, however, will not work as there is no way to express
+ the <literal>NULL</literal> using single quotes.
+ </para>
+ <para>
+ <programlisting>
+ SELECT
+ NULL,
+ pg_typeof(null),
+ pg_typeof(NuLl::text),
+ cast(null as text);
+ </programlisting>
+ <screen>
+ ?column? | pg_typeof | pg_typeof | text
+ ----------+-----------+-----------+------
+ | unknown | text |
+ </screen>
+ </para>
+ <para>
+ <programlisting>
+ SELECT text NULL;
+ </programlisting>
+ <screen>
+ ERROR: column "text" does not exist
+ LINE 1: select text NUll;
+ </screen>
+ </para>
+ <para>
+ The presence of null values 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. Put a bit more formally, the
+ Law of the Excluded Middle does not hold: i.e.,
+ P OR NOT(p) != true; for all p.
+ </para>
+ <para>
+ Aspects of the system that branch based upon
+ whether a condition variable is true or false must therefore
+ decide how to behave when then input condition is a null value.
+ The remaining sub-sections summarize these decisions.
+ </para>
+ <para>
+ In there are two broad classes of such comparison tests: first determining
+ whehow a given value compares to one or more other values, and second, determining
+ how two multi-element values compare to each other.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-cardinalrule">
+ <title>Distinctness - Overcoming the Cardinal Rule of Null Values</title>
+ <para>
+ The cardinal rule, a given null value is never
+ <link linkend="functions-comparison-op-table">equal or unequal</link>
+ to any other non-null.
+ <programlisting>
+ SELECT
+ NULL = NULL as "N = N", NULL != NULL as "N != N",
+ 1 = NULL as "1 = N", 1 != NULL as "1 != N",
+ 1 = 1 as "1 = 1", 1 != 1 as "1 != 1";
+ </programlisting>
+ <screen>
+ N = N | N != N | 1 = N | 1 != N | 1 = 1 | 1 != 1
+ -------+--------+-------+--------+-------+--------
+ | | | | t | f
+ </screen>
+ However, as with many rules, there are exceptions
+ <link linkend="nullvalues-multielementcomparison">noted below</link>. Specifically, when
+ when the two compared values are part of a larger multi-element value.
+ <programlisting>
+ select array[1,2]=array[1,null];
+ </programlisting>
+ <screen>
+ ?column?
+ ----------
+ f
+ (1 row)
+ </screen>
+ </para>
+ <para>
+ Because of this SQL specification mandated rule checking for a null value has an
+ explicit<literal>IS NULL</literal> test,
+ and additionally there are distinctness tests
+ (e.g., <literal>IS DISTINCT</literal>, and <literal>IS TRUE</literal>)
+ that consider a null value equal to another null value and unequal
+ to any other value. These and other tests are described in
+ <xref linkend="functions-comparison-pred-table"></xref>
+ <programlisting>
+ SELECT id, value,
+ value IS NULL as "IS N",
+ value IS DISTINCT FROM id as "IS D",
+ value != id as "IS !="
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ id | value | IS N | IS D | IS !=
+ ----+-------+------+------+-------
+ 1 | 1 | f | f | f
+ 2 | | t | t |
+ 3 | 4 | f | t | t
+ </screen>
+ </para>
+ <para>
+ There is also a cardinal warning: when dealing with
+ <link linkend="rowtypes">composite types</link>
+ expressions; <literal>composite IS NULL</literal>
+ and <literal>composite IS NOT NUll</literal>
+ are not the opposites of each other in the case where some,
+ but not all, of the composite's fields are null values.
+ (The case where all fields are null is indistinguishable
+ from the composite as a whole being null.)
+ Write <literal>NOT(composite IS NULL)</literal> instead.
+ <programlisting>
+ SELECT
+ c,
+ c IS NULL as "c IS N",
+ NOT(c IS NULL) as "NOT c IS N",
+ c IS NOT NULL as "c IS NOT N",
+ ROW(value, value) IS NULL as "ROW(v,v) IS N",
+ ROW(value, value) IS NOT NULL as "ROW(v,v) IS NOT N"
+ FROM null_examples AS c;
+ </programlisting>
+ <screen>
+ c | c IS N | NOT c IS N | c IS NOT N | ROW(v,v) IS N | ROW(v,v) IS NOT N
+ -------+--------+------------+------------+---------------+-------------------
+ (1,1) | f | t | t | f | t
+ (2,) | f | t | f | t | f
+ (3,4) | f | t | t | f | t
+ </screen>
+ See the <link linkend="nullvalues-multielement">multi-element
+ testing section</link> below for an explanation.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-operations">
+ <title>Null Valued Operands</title>
+ <para>
+ As a general expectation, operator invocation expressions where one of inputs
+ is a null value will result in a null valued output.
+ <programlisting>
+ SELECT
+ 1 + null as "Add",
+ 'text' || null as "Concatenate";
+ </programlisting>
+ <screen>
+ Add | Concatenate
+ -----+-------------
+ |
+ </screen>
+ Operators that behave otherwise should document their deviation from this norm.
+ </para>
+ <para>
+ A notable example of this is the <literal>IN</literal> operator, which
+ uses equality, not distinctness, for testing.
+ <programlisting>
+ SELECT
+ 1 IN (1, null) as "In Present",
+ 1 IN (2, null) as "In MIssing",
+ null IN (1, 2) as "N In Non-N",
+ null IN (null, 2) as "N In N";
+ </programlisting>
+ <screen>
+ In Present | In Missing | N In Non-N | N In N
+ ------------+------------+------------+--------
+ t | | |
+ </screen>
+ This is just an extension of the multi-element testing behavior described
+ <link linkend="nullvalues-multielement">below</link>.
+ </para>
+ <para>
+ Experience shows that <literal>CASE</literal> expressions are also prone
+ to bugs since their format encourages binary logic thinking while a
+ <literal>WHEN</literal> test will not consider a null value to be a match.
+ <programlisting>
+ SELECT id, value,
+ CASE WHEN id = value THEN 'Equal' ELSE 'Not Equal' END as "Affirm",
+ CASE WHEN id != value THEN 'Not Equal' ELSE 'Equal' END as "Negate",
+ CASE WHEN value IS NULL THEN 'Null'
+ WHEN id = value THEN 'Equal'
+ ELSE 'Not Equal' END as "Safe Affirm",
+ CASE WHEN value IS NULL THEN 'Null'
+ WHEN id != value THEN 'Not Equal'
+ ELSE 'Equal' END as "Safe Negate"
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ id | value | Affirm | Negate | Safe Affirm | Safe Negate
+ ----+-------+-----------+-----------+-------------+-------------
+ 1 | 1 | Equal | Equal | Equal | Equal
+ 2 | | Not Equal | Equal | Null | Null
+ 3 | 4 | Not Equal | Not Equal | Not Equal | Not Equal
+ </screen>
+ </para>
+ <para>
+ The boolean operators <literal>AND</literal> and <literal>OR</literal>
+ will ignore the null value input if the other input is sufficient to
+ to determine the outcome.
+ <programlisting>
+ SELECT
+ true OR null as "T or N",
+ false OR null as "F or N",
+ true AND null as "T and N",
+ false AND null as "F and N";
+ </programlisting>
+ <screen>
+ T or N | F or N | T and N | F and N
+ --------+--------+---------+---------
+ t | | | f
+ </screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-composites">
+ <title>Null Valued Composite Fields</title>
+ <para>
+ When a composite type is used, a null value can be assigned to any of its fields.
+ So long as at least one field is non-null the composite value as whole exists
+ and an IS NULL test on it will return false.
+ </para>
+ <para>
+ The IS NOT NULL test on a composite performs a test the checks whether
+ all fields of the composite have non-null values. This is not the same
+ as a non-null composite value. Specifically, if the composite value has
+ a null valued field then both this test and the IS NULL test will return false.
+ </para>
+ <para>
+ Please read <xref linkend="composite-type-comparison"/> for a complete treatment
+ on how <productname>PostgreSQL</productname> handle row-wise comparison. The
+ next two multi-element parts of this section discuss those comparisons in the
+ presence of null valued fields and also in terms of the SQL specification.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-multielement">
+ <title>Testing Multi-Element Values with Null Elements</title>
+ <para>
+ Arrays and composite types are multi-element types. Here we also consider non-empty
+ <link linkend="functions-subquery">subquery results</link>
+ and the list of values specified in the
+ <link linkend="functions-comparisons-in-scalar">IN test</link>.
+ When a test is performed on one of these multi-element values
+ the system will iterate over each element, or pair of elements if the test is
+ <link linkend="row-wise-comparison">comparing two row constructors</link> to each other,
+ left-to-right, combining the results using the boolean operations
+ <link linkend="nullvalues-operations">discussed above</link>. For tests that
+ require an exhaustive search, (e.g., <literal>ALL</literal>, <literal>NOT IN</literal>)
+ the search effectively ends when a false result is found (<literal>AND</literal> combiners).
+ For tests that simply require a true result, (e.g., <literal>ANY</literal>,
+ <literal>IN</literal>) the search effectively ends when a true result is found
+ (<literal>OR</literal> combiners). Therefore:
+ <simplelist>
+ <member>
+ <literal>IN</literal> and <literal>ANY</literal>
+ (<literal>OR</literal>) cannot produce a false result in the presence of null, and
+ </member>
+ <member>
+ <literal>NOT IN</literal> and <literal>ALL</literal>
+ (<literal>AND</literal>) cannot produce a true result in the presence of null.
+ </member>
+ </simplelist>
+ This is because any exhaustive search will produce at least one null value result
+ that cannot be ignored.
+ </para>
+ <para>
+ The SQL specification requires that non-exhaustive
+ (e.g., <literal>IN</literal> and <literal>ANY</literal>) subquery tests
+ return false when there are no rows in the subquery result, and return true
+ for the exhaustive tests (i.e., <literal>ALL</literal>).
+ </para>
+ <para>
+ Note that the <link linkend="nullvalues-cardinalrule">cardinal warning</link>
+ noted above is just the application of this behavior to the
+ <literal>IS NULL</literal> and <literal>IS NOT NULL</literal>
+ tests, which are both exhaustive search tests guaranteed to produce at least one false result
+ when the composite has a mix of null and non-null values.
+ </para>
+ <para>
+ Note that the rules above are applied to situation with a predicate or a scalar value
+ are being compared to a multi-element value. The rules when two multi-element values are compared
+ to each other are discussed <link linkend="nullvalues-multielementcomparison">next</link>.
+ The two row constructor comparison case included above is also noted below.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-multielementcomparison">
+ <title>Multi-Element Comparisons</title>
+ <para>
+ The <link linkend="nullvalues-multielement">prior section</link> discussed applying
+ a predicate or a scalar value check element-wise across a multi-element value.
+ This section discusses
+ comparing two multi-element values to each other. As both array and composite typed values
+ can be stored within an index, and comparing two values in that context must not produce
+ a null valued result, considerations are made to adhere to the SQL specification where
+ possible while still making indexes, which the specification is silent on, functional.
+ Specifically, except when comparing two row constructors, null values are considered
+ equal to other null values and greater than all non-null values.
+ </para>
+ <para>
+ There are five pair-wise comparison situations to consider:
+ element-wise when the inputs are arrays, and row-wise when the inputs can be either
+ row constructors or composite typed values. While these four later combinations seem similar,
+ the fact that row constructors are query literals, while composite typed values can be stored,
+ brings about important differences in how they are treated. Please read
+ <xref linkend="composite-type-comparison"/> for a fuller treatment of this topic. Here
+ we briefly recap the five different sitautions in the presence of null values.
+ </para>
+ <sect3 id="nullvalues-multielementcomparison-array">
+ <title>Element-wise Comparisons</title>
+ <para>
+ First, null values within an array compare as equal to each other and greater than all
+ non-null values, regardless of whether the comparison involves
+ <link linkend="sql-syntax-array-constructors">array constructors</link> or array typed values.
+ <programlisting>
+ select array[1,2]=array[1,null], s, t, s = t, t > s
+ from
+ (values (array[1,2])) sv (s),
+ (values (array[1,null::integer])) st (t);
+ </programlisting>
+ <screen>
+ ?column? | s | t | ?column? | ?column?
+ ----------+-------+----------+----------+----------
+ f | {1,2} | {1,NULL} | f | t
+ </screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-rowconstructor">
+ <title>Row-Wise Mutual Row Constructor Comparisons</title>
+ <para>
+ In this situation null values produce unknown when compared to all values.
+ <programlisting>
+ select (1,2)=(1,null), (1,null::integer)=(1,null);
+ </programlisting>
+ <screen>
+ ?column? | ?column?
+ ----------+----------
+ |
+ (1 row)
+ </screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-composite">
+ <title>Row-Wise Composite Involved Comparisons</title>
+ <para>
+ In these three situations null values are considered equal to each other and greater than
+ all non-null value.
+ </para>
+ <programlisting>
+ select s, t, s = t, t < (1,2), t = (1,null::integer)
+ from (values ((1,2))) sv (s),
+ (values ((1,null::integer))) st (t);
+ </programlisting>
+ <screen>
+ s | t | ?column? | ?column? | ?column?
+ -------+------+----------+----------+----------
+ (1,2) | (1,) | f | f | t
+ </screen>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-sqlconformance">
+ <title>SQL Conformance</title>
+ <para>
+ The SQL specification requires row-wise comparison to return NULL if the
+ result depends on comparing two NULL values or a NULL and a non-NULL.
+ <productname>PostgreSQL</productname> does this only when comparing the
+ results of two row constructors (as in
+ <xref linkend="row-wise-comparison"/>) or comparing a row constructor
+ to the output of a subquery (as in <xref linkend="functions-subquery"/>).
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="nullvalues-functions">
+ <title>Null Valued Arguments in Normal Function Calls</title>
+ <para>
+ <link linkend="sql-createfunction">Function specifications</link>
+ have a "strictness" attribute that, when set to "strict"
+ (a.k.a. "null on null input") will tell the executor to return a null value for any
+ function call having at least one null valued input, without executing the
+ function.
+ </para>
+ <para>
+ Most functions, especially single argument functions, are defined with strict because without
+ non-null values to act upon they cannot produce a meaningful result. However, for multi-argument
+ functions, especially <link linkend="xfunc-sql-variadic-functions">variadic functions</link>
+ like concatenate, null values often are simply ignored.
+ This can be different than the choice made by a binary operator performing the same function,
+ like for concatenating text, but not always, like concatenating an element onto an array.
+ <programlisting>
+ SELECT
+ lower(null::text) as "Lower",
+ left('text', null) as "Left",
+ 'one' || null as "|| Text Op",
+ concat('one', null) as "concat Text Func",
+ array_append(array[1], null) as "append([], null)",
+ array[1]::integer[] || null::integer as "[] || null",
+ array[1]::integer[] || null::integer[] as "[] || null[]";
+ </programlisting>
+ <screen>
+ Lower | Left | || Text Op | concat Text Func | append([], null) | [] || null | [] || null[]
+ -------+------+------------+------------------+------------------+------------+--------------
+ | | | one | {1,NULL} | {1,NULL} | {1}
+ </screen>
+ In short, please read the documentation for the functions you use if they may receive null inputs
+ to understand how they will behave. Send a documentation comment pointing out any functions
+ that do not behave strictly but whose actual behavior in the presence of null valued input
+ is not described or readily inferred.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-aggregates">
+ <title>Null Valued Arguments in Aggregate and Window Processing</title>
+ <para>
+ When executing an aggregate or window function the state tracking
+ component will remain unchanged even if the underlying processing
+ function returns a null value, whether from being defined strict
+ or it simply returns a null value upon execution. The aggregation
+ routine will usually ignore the null value and continue processing,
+ as demonstrated in <literal>count(value)</literal> below.
+ <programlisting>
+ SELECT
+ count(*) as "Count",
+ count(value) as "Count Value",
+ count(null_examples) as "Count Composite",
+ count(row(value, value)) as "Count Row"
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ Count | Count Value | Count Composite | Count Row
+ -------+-------------+-----------------+-----------
+ 3 | 2 | 3 | 3
+ </screen>
+ Notice the "Count Row" outcome, though. While we noted in the cardinal warning
+ that a composite whose fields are all null values is indistinguishable from
+ a null value of composite type, the count aggregate does indeed distinguish them,
+ recognizing and counting the non-null composite value produced by the
+ <link linkend="sql-syntax-row-constructors">row constructor</link>
+ <literal>count(row(value, value))</literal>.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-filters">
+ <title>Null Values in Filters</title>
+ <para>
+ A <literal>WHERE</literal> clause that evaluates to a null value for a given row will exclude that row.
+ <programlisting>
+ SELECT id, value FROM null_examples WHERE value = 1;
+ </programlisting>
+ <screen>
+ id | value
+ ----+-------
+ 1 | 1
+ </screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-constraints">
+ <title>Null Values in Constraints</title>
+ <para>
+ It is possible to define validation expressions
+ (<link linkend="ddl-constraints-check-constraints">check constraints</link>)
+ 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 a null value, is to allow the row to be inserted
+ - the same as a true result.
+ <programlisting>
+ BEGIN;
+ ALTER TABLE null_examples ADD CONSTRAINT value_not_1 CHECK (value != 1);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ ERROR: check constraint "value_not_1" of relation "null_examples" is violated by some row
+ ROLLBACK
+ </screen>
+ <programlisting>
+ BEGIN;
+ ALTER TABLE null_examples ADD CONSTRAINT value_not_10 CHECK (value != 10);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ ALTER TABLE
+ ROLLBACK
+ </screen>
+ We are using a <link linkend="tutorial-transactions">transaction</link>
+ (begin and rollback) and the alter table command to add two
+ constraints to our null_examples table. The first constraint prohibits rows with a value
+ of 1, which our row with an id of 1 violates. Prohibiting the value 10 definitely allows
+ rows with ids 1 and 3 to exist, and since we are not told that some row violates our
+ constraint the null value in the row with id 2 is being accepted as well.
+ </para>
+ <para>
+ The <link linkend="ddl-constraints-not-null"><literal>NOT NULL</literal> column constraint</link>
+ produces the same answer as a <literal>column IS NOT NULL</literal> check constraint but is
+ more concise to write.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-grouping">
+ <title>Null Values When Grouping</title>
+ <para>
+ In the context of both <literal>DISTINCT</literal> and <literal>GROUP BY</literal>
+ it is necessary that all inputs resolve to being either equal to or not equal to all
+ other values. These features use <link linkend="nullvalues-cardinalrule">distinctness</link>
+ instead of simple equality in order to handle a null value like a definite value equal to
+ another null vale and unequal to all other values.
+ <programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT
+ value,
+ count(*) as "Count"
+ FROM vals
+ GROUP BY value
+ ORDER BY value;
+ </programlisting>
+ <screen>
+ value | Count
+ -------+-------
+ 1 | 2
+ 2 | 1
+ | 2
+ </screen>
+ <programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT DISTINCT value FROM vals
+ ORDER BY value NULLS FIRST;
+ </programlisting>
+ <screen>
+ value
+ -------
+
+ 1
+ 2
+ </screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-ordering">
+ <title>Null Values When Ordering</title>
+ <para>
+ In the context of <literal>ORDER BY</literal>, distinctness rules also apply,
+ though this is insufficient since it must be determined whether or not to
+ present null values before or after all non-null values. To handle
+ this, the <literal>ORDER BY</literal> clause will let you specify either
+ <literal>NULLS FIRST</literal> or <literal>NULLS LAST</literal>.
+ <programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT value FROM vals
+ ORDER BY value DESC NULLS FIRST;
+ </programlisting>
+ <screen>
+ value
+ -------
+
+
+ 2
+ 1
+ 1
+ </screen>
+ </para>
+ <para>
+ Note that when dealing with multi-element values the comparison behavior
+ <link linkend="nullvalues-multielementcomparison">described above</link> applies,
+ if the comparison determination rests upon comparing a null value to a non-null value
+ the multi-element value with the null valued component will sort greater than the one
+ with a non-null component.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-indexed">
+ <title>Null Values in Indexes</title>
+ <para>
+ The uniqueness and relative ordering rules applied to null values
+ are defined when creating an index. For the default
+ <literal>NULLS DISTINCT</literal> uniqueness, equality rules are applied.
+ Specifying <literal>NULLS NOT DISTINCT</literal> will result in
+ <literal>IS DISTINCT FROM</literal> rules being applied whereby all null
+ values are equal to each other. This setting applies to all columns in the index.
+ </para>
+ <programlisting>
+ BEGIN;
+ CREATE UNIQUE INDEX value_nulls_distinct_implicit ON null_examples (value);
+ CREATE UNIQUE INDEX value_nulls_distinct_explicit ON null_examples (value) NULLS DISTINCT;
+ INSERT INTO null_examples VALUES (4, NULL);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ CREATE INDEX
+ CREATE INDEX
+ INSERT 0 1
+ ROLLBACK
+ </screen>
+ <programlisting>
+ BEGIN;
+ CREATE UNIQUE INDEX value_nulls_not_distinct_explicit ON null_examples (value) NULLS NOT DISTINCT;
+ INSERT INTO null_examples VALUES (4, NULL);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ CREATE INDEX
+ ERROR: duplicate key value violates unique constraint "value_nulls_not_distinct_explicit"
+ DETAIL: Key (value)=(null) already exists.
+ ROLLBACK
+ </screen>
+ <para>
+ For ordering, each column in the index gets its own specification of
+ direction and null value placement similar to that found in the
+ <literal>ORDER BY</literal> clause.
+ </para>
+ <para>
+ Note that when dealing with multi-element values the comparison behavior
+ <link linkend="nullvalues-multielementcomparison">described above</link> applies,
+ if the comparison determination rests upon comparing a null value to a non-null value
+ the multi-element value with the null valued component will sort greater than the one
+ with a non-null component.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-settings">
+ <title>Null Valued Settings</title>
+ <para>
+ There are none. During initializion all settings are assigned a non-null value.
+ </para>
+ <para>
+ This is mostly meaningful for <link linkend="runtime-config-custom">custom settings</link>,
+ thus this section focuses on <link linkend="config-setting-sql">SQL interaction</link>.
+ Unlike settings created by extensions, custom settings can only be textual and the default
+ value for text here is the empty string.
+ <programlisting>
+ SHOW example.string;
+ BEGIN;
+ SELECT set_config('example.string', NULL, true);
+ SELECT current_setting('example.string') IS NULL AS "Setting Is Null";
+ ROLLBACK;
+ SHOW example.string;
+ RESET example.string;
+ SHOW example.string;
+ </programlisting>
+ <screen>
+ ERROR: unrecognized configuration parameter "example.string"
+ BEGIN
+ set_config
+ ------------
+
+ (1 row)
+
+ Setting Is Null
+ -----------------
+ f
+ (1 row)
+
+ ROLLBACK
+ example.string
+ ----------------
+
+ (1 row)
+
+ RESET
+ example.string
+ ----------------
+
+ (1 row)
+ </screen>
+ Notice two important behaviors: first, even though we passed in a null value to
+ to the <literal>set_config</literal> function, the <literal>current_setting</literal>
+ function returned a non-null value, specifically the empty string. Second, after ROLLBACK the
+ setting is still present (i.e., the error seen before creating the setting no longer appears),
+ and in fact will remain so until the session ends
+ (i.e., RESET does not restore the non-existence state.)
+ </para>
+ <para>
+ The other ways to specify settings do not have a means to specify null values,
+ a specific non-null value is required as part of the specification of the setting.
+ </para>
+ </sect2>
+
+</sect1>
--
2.34.1
On Sat, May 11, 2024, 16:34 David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Fri, May 3, 2024 at 9:00 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls.
I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.v3 Attached.
Probably at the 90% complete mark. Minimal index entries, not as thorough
a look-about of the existing documentation as I'd like. Probably some
wording and style choices to tweak. Figured better to get feedback now
before I go into polish mode. In particular, tweaking and re-running the
examples.Yes, I am aware of my improper indentation for programlisting and screen.
I wanted to be able to use the code folding features of my editor. Those
can be readily un-indented in the final version.The changes to func.sgml is basically one change repeated something like
20 times with tweaks for true/false. Plus moving the discussion regarding
the SQL specification into the new null handling section.It took me doing this to really understand the difference between row
constructors and composite typed values, especially since array
constructors produce array typed values and the constructor is just an
unimportant implementation option while row constructors introduce
meaningfully different behaviors when used.My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.
+ The cardinal rule, a given null value is never
+ <link linkend="functions-comparison-op-table">equal or unequal</link>
+ to any other non-null.
Again, doesn't this imply it tends to be equal to another null by its
omission?
Thom
Show quoted text
On Saturday, May 11, 2024, Thom Brown <thom@linux.com> wrote:
Sat, May 11, 2024, 16:34 David G. Johnston <david.g.johnston@gmail.com>
wrote:
My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.
+ The cardinal rule, a given null value is never + <link linkend="functions-comparison-op-table">equal or unequal</link> + to any other non-null.Again, doesn't this imply it tends to be equal to another null by its
omission?
I still agree, it’s just a typo now…
…is never equal or unequal to any value.
Though I haven’t settled on a phrasing I really like. But I’m trying to
avoid a parenthetical.
David J.
On Sat, May 11, 2024 at 11:00 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Though I haven’t settled on a phrasing I really like. But I’m trying to
avoid a parenthetical.
Settled on this:
The cardinal rule, a null value is neither
<link linkend="functions-comparison-op-table">equal nor unequal</link>
to any value, including other null values.
I've been tempted to just say, "to any value.", but cannot quite bring
myself to do it...
David J.
On Sat, 11 May 2024 08:33:27 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Fri, May 3, 2024 at 9:00 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:On Fri, May 3, 2024 at 8:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Having said that, I reiterate my proposal that we make it a new
<sect1> under DDL, before 5.2 Default Values which is the first
place in ddl.sgml that assumes you have heard of nulls.
I will go with this and remove the "Data Basics" section I wrote, leaving
it to be just a discussion about null values. The tutorial is the only
section that really needs unique wording to fit in. No matter where we
decide to place it otherwise the core content will be the same, with maybe
a different section preface to tie it in.v3 Attached.
Probably at the 90% complete mark. Minimal index entries, not as thorough
a look-about of the existing documentation as I'd like. Probably some
wording and style choices to tweak. Figured better to get feedback now
before I go into polish mode. In particular, tweaking and re-running the
examples.Yes, I am aware of my improper indentation for programlisting and screen. I
wanted to be able to use the code folding features of my editor. Those can
be readily un-indented in the final version.The changes to func.sgml is basically one change repeated something like 20
times with tweaks for true/false. Plus moving the discussion regarding the
SQL specification into the new null handling section.It took me doing this to really understand the difference between row
constructors and composite typed values, especially since array
constructors produce array typed values and the constructor is just an
unimportant implementation option while row constructors introduce
meaningfully different behaviors when used.My plan is to have a v4 out next week, without or without a review of this
draft, but then the subsequent few weeks will probably be a bit quiet.
+ A null value literal is written as unquoted, case insensitive, NULL.
...(snip)...
+ <programlisting>
+ SELECT
+ NULL,
+ pg_typeof(null),
+ pg_typeof(NuLl::text),
+ cast(null as text);
+ </programlisting>
It may be a trivial thing but I am not sure we need to mention case insensitivity
here, because all keywords and unquoted identifiers are case-insensitive in
PostgreSQL and it is not specific to NULL.
Also, I found the other parts of the documentation use "case-insensitive" in which
words are joined with hyphen, so I wonder it is better to use the same form if we
leave the description.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
On Tue, Jun 18, 2024 at 8:34 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
It may be a trivial thing but I am not sure we need to mention case
insensitivity
here, because all keywords and unquoted identifiers are case-insensitive in
PostgreSQL and it is not specific to NULL.
But it is neither a keyword nor an identifier. It behaves more like:
SELECT 1 as one; A constant, which have no implied rules - mainly because
numbers don't have case. Which suggests adding some specific mention there
- and also probably need to bring up it and its "untyped" nature in the
syntax chapter, probably here:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
Also, I found the other parts of the documentation use "case-insensitive"
in which
words are joined with hyphen, so I wonder it is better to use the same
form if we
leave the description.
Typo on my part, fixed.
I'm not totally against just letting this content be assumed to be learned
from elsewhere in the documentation but it also seems reasonable to
include. I'm going to leave it for now.
David J.
On Tue, 18 Jun 2024 20:56:58 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Tue, Jun 18, 2024 at 8:34 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
It may be a trivial thing but I am not sure we need to mention case
insensitivity
here, because all keywords and unquoted identifiers are case-insensitive in
PostgreSQL and it is not specific to NULL.But it is neither a keyword nor an identifier. It behaves more like:
SELECT 1 as one; A constant, which have no implied rules - mainly because
numbers don't have case. Which suggests adding some specific mention there
Thank you for your explanation. This makes a bit clear for me why the description
mentions 'string' syntax there. I just thought NULL is a keyword representing
a null constant.
- and also probably need to bring up it and its "untyped" nature in the
syntax chapter, probably here:https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC
Also, I found the other parts of the documentation use "case-insensitive"
in which
words are joined with hyphen, so I wonder it is better to use the same
form if we
leave the description.Typo on my part, fixed.
I'm not totally against just letting this content be assumed to be learned
from elsewhere in the documentation but it also seems reasonable to
include. I'm going to leave it for now.David J.
--
Yugo NAGATA <nagata@sraoss.co.jp>
Yugo NAGATA <nagata@sraoss.co.jp> writes:
On Tue, 18 Jun 2024 20:56:58 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:But it is neither a keyword nor an identifier.
The lexer would be quite surprised by your claim that NULL isn't
a keyword. Per src/include/parser/kwlist.h, NULL is a keyword,
and a fully reserved one at that.
regards, tom lane
On Tuesday, June 18, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yugo NAGATA <nagata@sraoss.co.jp> writes:
On Tue, 18 Jun 2024 20:56:58 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:But it is neither a keyword nor an identifier.
The lexer would be quite surprised by your claim that NULL isn't
a keyword. Per src/include/parser/kwlist.h, NULL is a keyword,
and a fully reserved one at that.
Can’t it be both a value and a keyword? I figured the not null constraint
and is null predicates are why it’s a keyword but the existence of those
doesn’t cover its usage as a literal value that can be stuck anywhere you
have an expression.
David J.
On Tue, 18 Jun 2024 23:02:14 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Tuesday, June 18, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yugo NAGATA <nagata@sraoss.co.jp> writes:
On Tue, 18 Jun 2024 20:56:58 -0700
"David G. Johnston" <david.g.johnston@gmail.com> wrote:But it is neither a keyword nor an identifier.
The lexer would be quite surprised by your claim that NULL isn't
a keyword. Per src/include/parser/kwlist.h, NULL is a keyword,
and a fully reserved one at that.Can’t it be both a value and a keyword? I figured the not null constraint
and is null predicates are why it’s a keyword but the existence of those
doesn’t cover its usage as a literal value that can be stuck anywhere you
have an expression.
I still wonder it whould be unnecessary to mention the case-insensitivity here
if we can say NULL is *also* a keyword.
Regards,
Yugo Nagata
David J.
--
Yugo NAGATA <nagata@sraoss.co.jp>
On Wed, Jun 26, 2024 at 8:14 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
I still wonder it whould be unnecessary to mention the case-insensitivity
here
if we can say NULL is *also* a keyword.
I went with wording that includes mentioning its keyword status.
The attached are complete and ready for review. I did some file structure
reformatting at the end and left that as the second patch. The first
contains all of the content.
I'm adding this to the commitfest.
Thanks!
David J.
Attachments:
v4-0002-Formatting.patchtext/x-patch; charset=US-ASCII; name=v4-0002-Formatting.patchDownload
From e246e15058d5d7d514fb5c7d413fca4d138cae0d Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Fri, 28 Jun 2024 13:32:41 -0700
Subject: [PATCH v4 2/2] Formatting
---
doc/src/sgml/nullvalues.sgml | 1037 +++++++++++++++++-----------------
1 file changed, 525 insertions(+), 512 deletions(-)
diff --git a/doc/src/sgml/nullvalues.sgml b/doc/src/sgml/nullvalues.sgml
index 6c4c29e305..6c7aaed37a 100644
--- a/doc/src/sgml/nullvalues.sgml
+++ b/doc/src/sgml/nullvalues.sgml
@@ -44,28 +44,28 @@
but can be cast to any concrete data type.
</para>
<para>
- <programlisting>
- SELECT
- NULL AS "Literal Null Value",
- pg_typeof(null) AS "Type of Null",
- pg_typeof(NuLl::text) AS "Type of Cast null",
- cast(null as text) AS "Cast null value";
- </programlisting>
- <screen>
- Literal Null Value | Type of Null | Type of Cast null | Cast null value
- --------------------+--------------+-------------------+-----------------
- | unknown | text |
- (1 row)
- </screen>
- </para>
- <para>
- <programlisting>
- SELECT text NULL;
- </programlisting>
- <screen>
- ERROR: column "text" does not exist
- LINE 1: select text NUll;
- </screen>
+<programlisting>
+ SELECT
+ NULL AS "Literal Null Value",
+ pg_typeof(null) AS "Type of Null",
+ pg_typeof(NuLl::text) AS "Type of Cast null",
+ cast(null as text) AS "Cast null value";
+</programlisting>
+<screen>
+ Literal Null Value | Type of Null | Type of Cast null | Cast null value
+ --------------------+--------------+-------------------+-----------------
+ | unknown | text |
+ (1 row)
+</screen>
+ </para>
+ <para>
+<programlisting>
+ SELECT text NULL;
+</programlisting>
+<screen>
+ ERROR: column "text" does not exist
+ LINE 1: select text NUll;
+</screen>
</para>
<para>
The presence of null values in the system results in three-valued logic.
@@ -92,34 +92,34 @@
neither equal nor unequal
</link>
to any value, including other null values.
- <programlisting>
- SELECT
- NULL = NULL AS "N = N",
- NULL != NULL AS "N != N",
- 1 = NULL AS "1 = N",
- 1 != NULL AS "1 != N",
- 1 = 1 AS "1 = 1",
- 1 != 1 AS "1 != 1";
- </programlisting>
- <screen>
- N = N | N != N | 1 = N | 1 != N | 1 = 1 | 1 != 1
- -------+--------+-------+--------+-------+--------
- | | | | t | f
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ NULL = NULL AS "N = N",
+ NULL != NULL AS "N != N",
+ 1 = NULL AS "1 = N",
+ 1 != NULL AS "1 != N",
+ 1 = 1 AS "1 = 1",
+ 1 != 1 AS "1 != 1";
+</programlisting>
+<screen>
+ N = N | N != N | 1 = N | 1 != N | 1 = 1 | 1 != 1
+ -------+--------+-------+--------+-------+--------
+ | | | | t | f
+ (1 row)
+</screen>
However, as with many rules, there are exceptions, which are
<link linkend="nullvalues-multielementcomparison">noted below</link>. Specifically,
when the two compared values are part of a larger multi-element value.
- <programlisting>
- SELECT
- array[1,2]=array[1,null] AS "Array Equals";
- </programlisting>
- <screen>
- Array Equals
- --------------
- f
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ array[1,2]=array[1,null] AS "Array Equals";
+</programlisting>
+<screen>
+ Array Equals
+ --------------
+ f
+ (1 row)
+</screen>
</para>
<para>
Because of this SQL specification mandated rule, checking for a null value has an
@@ -128,21 +128,21 @@
to any other value (e.g., <literal>IS DISTINCT</literal>, and <literal>IS TRUE</literal>.)
These, and other predicates, are described in
<xref linkend="functions-comparison-pred-table"/>
- <programlisting>
- SELECT id, value,
- value IS NULL AS "IS N",
- value IS DISTINCT FROM id AS "IS D",
- value != id AS "IS !="
- FROM null_examples;
- </programlisting>
- <screen>
- id | value | IS N | IS D | IS !=
- ----+-------+------+------+-------
- 1 | 1 | f | f | f
- 2 | | t | t |
- 3 | 4 | f | t | t
- (3 rows)
- </screen>
+<programlisting>
+ SELECT id, value,
+ value IS NULL AS "IS N",
+ value IS DISTINCT FROM id AS "IS D",
+ value != id AS "IS !="
+ FROM null_examples;
+</programlisting>
+<screen>
+ id | value | IS N | IS D | IS !=
+ ----+-------+------+------+-------
+ 1 | 1 | f | f | f
+ 2 | | t | t |
+ 3 | 4 | f | t | t
+ (3 rows)
+</screen>
</para>
<para>
There is also a cardinal warning: when dealing with
@@ -154,24 +154,24 @@
(The case where all fields are null is indistinguishable
from the composite as a whole being null.)
Write <literal>NOT(composite IS NULL)</literal> instead.
- <programlisting>
- SELECT
- c,
- c IS NULL AS "c IS N",
- NOT(c IS NULL) AS "NOT c IS N",
- c IS NOT NULL AS "c IS NOT N",
- ROW(value, value) IS NULL AS "ROW(v,v) IS N",
- ROW(value, value) IS NOT NULL AS "ROW(v,v) IS NOT N"
- FROM null_examples AS c;
- </programlisting>
- <screen>
- c | c IS N | NOT c IS N | c IS NOT N | ROW(v,v) IS N | ROW(v,v) IS NOT N
- -------+--------+------------+------------+---------------+-------------------
- (1,1) | f | t | t | f | t
- (2,) | f | t | f | t | f
- (3,4) | f | t | t | f | t
- (3 rows)
- </screen>
+<programlisting>
+ SELECT
+ c,
+ c IS NULL AS "c IS N",
+ NOT(c IS NULL) AS "NOT c IS N",
+ c IS NOT NULL AS "c IS NOT N",
+ ROW(value, value) IS NULL AS "ROW(v,v) IS N",
+ ROW(value, value) IS NOT NULL AS "ROW(v,v) IS NOT N"
+ FROM null_examples AS c;
+</programlisting>
+<screen>
+ c | c IS N | NOT c IS N | c IS NOT N | ROW(v,v) IS N | ROW(v,v) IS NOT N
+ -------+--------+------------+------------+---------------+-------------------
+ (1,1) | f | t | t | f | t
+ (2,) | f | t | f | t | f
+ (3,4) | f | t | t | f | t
+ (3 rows)
+</screen>
See the <link linkend="nullvalues-multielement">multi-element
testing section</link> below for an explanation.
</para>
@@ -182,35 +182,35 @@
<para>
As a general expectation, operator invocation expressions where one of inputs
is a null value will result in a null-valued output.
- <programlisting>
- SELECT
- 1 + null AS "Add",
- 'text' || null AS "Concatenate";
- </programlisting>
- <screen>
- Add | Concatenate
- -----+-------------
- |
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ 1 + null AS "Add",
+ 'text' || null AS "Concatenate";
+</programlisting>
+<screen>
+ Add | Concatenate
+ -----+-------------
+ |
+ (1 row)
+</screen>
Operators that behave otherwise should document their deviation from this norm.
</para>
<para>
A notable example of this is the <literal>IN</literal> operator, which
uses equality, not distinctness, for testing.
- <programlisting>
- SELECT
- 1 IN (1, null) AS "In Present",
- 1 IN (2, null) AS "In MIssing",
- null IN (1, 2) AS "N In Non-N",
- null IN (null, 2) AS "N In N";
- </programlisting>
- <screen>
- In Present | In Missing | N In Non-N | N In N
- ------------+------------+------------+--------
- t | | |
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ 1 IN (1, null) AS "In Present",
+ 1 IN (2, null) AS "In MIssing",
+ null IN (1, 2) AS "N In Non-N",
+ null IN (null, 2) AS "N In N";
+</programlisting>
+<screen>
+ In Present | In Missing | N In Non-N | N In N
+ ------------+------------+------------+--------
+ t | | |
+ (1 row)
+</screen>
This is just an extension of the multi-element testing behavior described
<link linkend="nullvalues-multielement">below</link>.
</para>
@@ -218,44 +218,44 @@
Experience shows that <literal>CASE</literal> expressions are also prone
to bugs since their format encourages binary logic thinking while a
<literal>WHEN</literal> test will not consider a null value to be a match.
- <programlisting>
- SELECT id, value,
- CASE WHEN id = value THEN 'Equal' ELSE 'Not Equal' END AS "Affirm",
- CASE WHEN id != value THEN 'Not Equal' ELSE 'Equal' END AS "Negate",
- CASE WHEN value IS NULL THEN 'Null'
- WHEN id = value THEN 'Equal'
- ELSE 'Not Equal' END AS "Safe Affirm",
- CASE WHEN value IS NULL THEN 'Null'
- WHEN id != value THEN 'Not Equal'
- ELSE 'Equal' END AS "Safe Negate"
- FROM null_examples;
- </programlisting>
- <screen>
- id | value | Affirm | Negate | Safe Affirm | Safe Negate
- ----+-------+-----------+-----------+-------------+-------------
- 1 | 1 | Equal | Equal | Equal | Equal
- 2 | | Not Equal | Equal | Null | Null
- 3 | 4 | Not Equal | Not Equal | Not Equal | Not Equal
- (3 rows)
- </screen>
+<programlisting>
+ SELECT id, value,
+ CASE WHEN id = value THEN 'Equal' ELSE 'Not Equal' END AS "Affirm",
+ CASE WHEN id != value THEN 'Not Equal' ELSE 'Equal' END AS "Negate",
+ CASE WHEN value IS NULL THEN 'Null'
+ WHEN id = value THEN 'Equal'
+ ELSE 'Not Equal' END AS "Safe Affirm",
+ CASE WHEN value IS NULL THEN 'Null'
+ WHEN id != value THEN 'Not Equal'
+ ELSE 'Equal' END AS "Safe Negate"
+ FROM null_examples;
+</programlisting>
+<screen>
+ id | value | Affirm | Negate | Safe Affirm | Safe Negate
+ ----+-------+-----------+-----------+-------------+-------------
+ 1 | 1 | Equal | Equal | Equal | Equal
+ 2 | | Not Equal | Equal | Null | Null
+ 3 | 4 | Not Equal | Not Equal | Not Equal | Not Equal
+ (3 rows)
+</screen>
</para>
<para>
The boolean operators <literal>AND</literal> and <literal>OR</literal>
will ignore the null value input if the other input is sufficient to
to determine the outcome.
- <programlisting>
- SELECT
- true OR null AS "T or N",
- false OR null AS "F or N",
- true AND null AS "T and N",
- false AND null AS "F and N";
- </programlisting>
- <screen>
- T or N | F or N | T and N | F and N
- --------+--------+---------+---------
- t | | | f
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ true OR null AS "T or N",
+ false OR null AS "F or N",
+ true AND null AS "T and N",
+ false AND null AS "F and N";
+</programlisting>
+<screen>
+ T or N | F or N | T and N | F and N
+ --------+--------+---------+---------
+ t | | | f
+ (1 row)
+</screen>
</para>
</sect2>
@@ -266,30 +266,30 @@
However, some usages of domains will cause the resultant column to have the domain type but
the value will be null. The common way this happens is by including the domain column's table
on the right side of a left join.
- <programlisting>
- BEGIN;
- CREATE DOMAIN domain_example AS integer NOT NULL;
- CREATE TABLE domain_examples (de_id bigint PRIMARY KEY, de_value domain_example);
- INSERT INTO domain_examples VALUES (1, 1), (2, 2);
- SELECT *, pg_typeof(de_value)
- FROM null_examples AS ne
- LEFT JOIN domain_examples AS de ON ne.id = de.de_id;
- ROLLBACK;
- </programlisting>
- <screen>
- BEGIN
- CREATE DOMAIN
- CREATE TABLE
- INSERT 0 2
- id | value | de_id | de_value | pg_typeof
- ----+-------+-------+----------+----------------
- 1 | 1 | 1 | 1 | domain_example
- 2 | | 2 | 2 | domain_example
- 3 | 4 | | | domain_example
- (3 rows)
-
- ROLLBACK
- </screen>
+<programlisting>
+ BEGIN;
+ CREATE DOMAIN domain_example AS integer NOT NULL;
+ CREATE TABLE domain_examples (de_id bigint PRIMARY KEY, de_value domain_example);
+ INSERT INTO domain_examples VALUES (1, 1), (2, 2);
+ SELECT *, pg_typeof(de_value)
+ FROM null_examples AS ne
+ LEFT JOIN domain_examples AS de ON ne.id = de.de_id;
+ ROLLBACK;
+</programlisting>
+<screen>
+ BEGIN
+ CREATE DOMAIN
+ CREATE TABLE
+ INSERT 0 2
+ id | value | de_id | de_value | pg_typeof
+ ----+-------+-------+----------+----------------
+ 1 | 1 | 1 | 1 | domain_example
+ 2 | | 2 | 2 | domain_example
+ 3 | 4 | | | domain_example
+ (3 rows)
+
+ ROLLBACK
+</screen>
Please see the details in the <link linkend="sql-createdomain-notes">
notes on the create domain page</link> for another example, as well as
commentary why this non-standard behavior exists.
@@ -353,6 +353,7 @@
<sect2 id="nullvalues-multielementpredicates">
<title>Multi-Element Predicates and Scalars</title>
+
<sect3 id="nullvalues-multielementpredicates-composites">
<title>Composite Fields</title>
<para>
@@ -367,19 +368,19 @@
as a non-null composite value. Specifically, if the composite value has
a null-valued field then both the <literal>IS NOT NULL</literal> predicate and the
<literal>IS NULL</literal> predicate will return false.
- <programlisting>
- SELECT
- ROW(1,2) IS NULL AS "Row Is Null",
- ROW(1,2) IS NOT NULL AS "Row Is Not Null",
- ROW(1,NULL) IS NULL AS "Row Is Null",
- ROW(1,NULL) IS NOT NULL AS "Row Is Not Null";
- </programlisting>
- <screen>
- Row Is Null | Row Is Not Null | Row Is Null | Row Is Not Null
- -------------+-----------------+-------------+-----------------
- f | t | f | f
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ ROW(1,2) IS NULL AS "Row Is Null",
+ ROW(1,2) IS NOT NULL AS "Row Is Not Null",
+ ROW(1,NULL) IS NULL AS "Row Is Null",
+ ROW(1,NULL) IS NOT NULL AS "Row Is Not Null";
+</programlisting>
+<screen>
+ Row Is Null | Row Is Not Null | Row Is Null | Row Is Not Null
+ -------------+-----------------+-------------+-----------------
+ f | t | f | f
+ (1 row)
+</screen>
</para>
<para>
Please read <xref linkend="composite-type-comparison"/> for a complete treatment
@@ -388,6 +389,7 @@
presence of null-valued fields, and also in terms of the SQL specification.
</para>
</sect3>
+
<sect3 id="nullvalues-multielementpredicates-arrays">
<title>Array Elements and IN Bag Members</title>
<para>
@@ -396,51 +398,55 @@
operators defined in <xref linkend="functions-comparisons"/>.
</para>
<para>
- <programlisting>
- SELECT
- 1 = ANY(array[1, 1, NULL]) AS "Any-Null-Match",
- 1 = ANY(array[1, 1]) AS "Any-NoNull-Match",
- 1 = ALL(array[1, 1, NULL]) AS "ALL-Null-Match",
- 1 = ALL(array[1, 1]) AS "All-NoNull-Match";
- SELECT
- 2 = ANY(array[1, 1, NULL]) AS "Any-Null-NoMatch",
- 2 = ANY(array[1, 1]) AS "Any-NoNull-NoMatch",
- 2 = ALL(array[1, 1, NULL]) AS "ALL-Null-NoMatch",
- 2 = ALL(array[1, 1]) AS "All-NoNull-NoMatch";
- SELECT
- 1 IN (1, 1, NULL) AS "IN-Null-Positive",
- 1 IN (1, 1) AS "IN-NoNull-Positive",
- 1 NOT IN (2, 2, NULL) AS "NotIN-Null-Positive",
- 1 NOT IN (2, 2) AS "NotIN-NoNull-Positive";
- SELECT
- 2 IN (1, 1, NULL) AS "IN-Null-Negative",
- 2 IN (1, 1) AS "IN-NoNull-Negative",
- 2 NOT IN (2, 2, NULL) AS "NotIN-Null-Negative",
- 2 NOT IN (2, 2) AS "NotIN-NoNull-Negative";
- </programlisting>
- <screen>
- Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
- ----------------+------------------+----------------+------------------
- t | t | | t
- (1 row)
-
- Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
- ------------------+--------------------+------------------+--------------------
- | f | f | f
- (1 row)
-
- IN-Null-Positive | IN-NoNull-Positive | NotIN-Null-Positive | NotIN-NoNull-Positive
- ------------------+--------------------+---------------------+-----------------------
- t | t | | t
- (1 row)
-
- IN-Null-Negative | IN-NoNull-Negative | NotIN-Null-Negative | NotIN-NoNull-Negative
- ------------------+--------------------+---------------------+-----------------------
- | f | f | f
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ 1 = ANY(array[1, 1, NULL]) AS "Any-Null-Match",
+ 1 = ANY(array[1, 1]) AS "Any-NoNull-Match",
+ 1 = ALL(array[1, 1, NULL]) AS "ALL-Null-Match",
+ 1 = ALL(array[1, 1]) AS "All-NoNull-Match";
+
+ SELECT
+ 2 = ANY(array[1, 1, NULL]) AS "Any-Null-NoMatch",
+ 2 = ANY(array[1, 1]) AS "Any-NoNull-NoMatch",
+ 2 = ALL(array[1, 1, NULL]) AS "ALL-Null-NoMatch",
+ 2 = ALL(array[1, 1]) AS "All-NoNull-NoMatch";
+
+ SELECT
+ 1 IN (1, 1, NULL) AS "IN-Null-Positive",
+ 1 IN (1, 1) AS "IN-NoNull-Positive",
+ 1 NOT IN (2, 2, NULL) AS "NotIN-Null-Positive",
+ 1 NOT IN (2, 2) AS "NotIN-NoNull-Positive";
+
+ SELECT
+ 2 IN (1, 1, NULL) AS "IN-Null-Negative",
+ 2 IN (1, 1) AS "IN-NoNull-Negative",
+ 2 NOT IN (2, 2, NULL) AS "NotIN-Null-Negative",
+ 2 NOT IN (2, 2) AS "NotIN-NoNull-Negative";
+</programlisting>
+<screen>
+ Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
+ ----------------+------------------+----------------+------------------
+ t | t | | t
+ (1 row)
+
+ Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
+ ------------------+--------------------+------------------+--------------------
+ | f | f | f
+ (1 row)
+
+ IN-Null-Positive | IN-NoNull-Positive | NotIN-Null-Positive | NotIN-NoNull-Positive
+ ------------------+--------------------+---------------------+-----------------------
+ t | t | | t
+ (1 row)
+
+ IN-Null-Negative | IN-NoNull-Negative | NotIN-Null-Negative | NotIN-NoNull-Negative
+ ------------------+--------------------+---------------------+-----------------------
+ | f | f | f
+ (1 row)
+</screen>
</para>
</sect3>
+
<sect3 id="nullvalues-multielementpredicates-subqueries">
<title>Single-Column Subquery Rows</title>
<para>
@@ -450,34 +456,35 @@
the column itself is multi-element then the thing being searched for must be a compatible
multi-element value, and the corresponding comparison behavior described in
<xref linkend="nullvalues-multielementcomparison"/> will also be applied.
-
</para>
<para>
- <programlisting>
- SELECT
- 1 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-Match",
- 1 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-Match",
- 1 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-Match",
- 1 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-Match";
- SELECT
- 2 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-NoMatch",
- 2 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-NoMatch",
- 2 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-NoMatch",
- 2 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-NoMatch";
- </programlisting>
- <screen>
- Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
- ----------------+------------------+----------------+------------------
- t | t | | t
- (1 row)
-
- Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
- ------------------+--------------------+------------------+--------------------
- | f | f | f
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ 1 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-Match",
+ 1 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-Match",
+ 1 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-Match",
+ 1 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-Match";
+
+ SELECT
+ 2 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-NoMatch",
+ 2 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-NoMatch",
+ 2 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-NoMatch",
+ 2 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-NoMatch";
+</programlisting>
+<screen>
+ Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
+ ----------------+------------------+----------------+------------------
+ t | t | | t
+ (1 row)
+
+ Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
+ ------------------+--------------------+------------------+--------------------
+ | f | f | f
+ (1 row)
+</screen>
</para>
</sect3>
+
</sect2>
<sect2 id="nullvalues-multielementcomparison">
@@ -502,69 +509,73 @@
<xref linkend="composite-type-comparison"/> for a fuller treatment of this topic. Here
we briefly recap the five different situations in the presence of null values.
</para>
+
<sect3 id="nullvalues-multielementcomparison-array">
<title>Element-wise Comparisons</title>
<para>
First situation, null values within an array compare as equal to each other and greater than all
non-null values, regardless of whether the comparison involves
<link linkend="sql-syntax-array-constructors">array constructors</link> or array typed values.
- <programlisting>
- SELECT
- array[1,2]=array[1,null] AS "Constructors",
- s, t,
- s = t AS "Stored Equality",
- t > s AS "Stored Ordering"
- FROM
- (values (array[1,2])) AS sv (s),
- (values (array[1,null::integer])) AS st (t);
- </programlisting>
- <screen>
- Constructors | s | t | Stored Equality | Stored Ordering
- --------------+-------+----------+-----------------+-----------------
- f | {1,2} | {1,NULL} | f | t
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ array[1,2]=array[1,null] AS "Constructors",
+ s, t,
+ s = t AS "Stored Equality",
+ t > s AS "Stored Ordering"
+ FROM
+ (values (array[1,2])) AS sv (s),
+ (values (array[1,null::integer])) AS st (t);
+</programlisting>
+<screen>
+ Constructors | s | t | Stored Equality | Stored Ordering
+ --------------+-------+----------+-----------------+-----------------
+ f | {1,2} | {1,NULL} | f | t
+ (1 row)
+</screen>
</para>
</sect3>
+
<sect3 id="nullvalues-multielementcomparison-rowconstructor">
<title>Row-wise Mutual Row Constructor Comparisons</title>
<para>
In this situation null values produce unknown when compared to all values.
- <programlisting>
- SELECT
- (1,2)=(1,null) AS "NonNull=Null",
- (1,null::integer)=(1,null) AS "Null=Null";
- </programlisting>
- <screen>
- NonNull=Null | Null=Null
- --------------+-----------
- |
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ (1,2)=(1,null) AS "NonNull=Null",
+ (1,null::integer)=(1,null) AS "Null=Null";
+</programlisting>
+<screen>
+ NonNull=Null | Null=Null
+ --------------+-----------
+ |
+ (1 row)
+</screen>
</para>
</sect3>
+
<sect3 id="nullvalues-multielementcomparison-composite">
<title>Row-wise Composite Involved Comparisons</title>
<para>
In these three situations null values are considered equal to each other and greater than
all non-null value.
</para>
- <programlisting>
- SELECT s, t,
- s = t AS "Stored Equals Stored",
- t < (1,2) AS "Stored LT Constructor",
- t = (1,null::integer) AS "Stored Equals Constructor"
- FROM
- (values (1,2)) AS s,
- (values (1,null::integer)) AS t;
- </programlisting>
- <screen>
- s | t | Stored Equals Stored | Stored LT Constructor | Stored Equals Constructor
- -------+------+----------------------+-----------------------+---------------------------
- (1,2) | (1,) | f | f | t
- (1 row)
- </screen>
+<programlisting>
+ SELECT s, t,
+ s = t AS "Stored Equals Stored",
+ t < (1,2) AS "Stored LT Constructor",
+ t = (1,null::integer) AS "Stored Equals Constructor"
+ FROM
+ (values (1,2)) AS s,
+ (values (1,null::integer)) AS t;
+</programlisting>
+<screen>
+ s | t | Stored Equals Stored | Stored LT Constructor | Stored Equals Constructor
+ -------+------+----------------------+-----------------------+---------------------------
+ (1,2) | (1,) | f | f | t
+ (1 row)
+</screen>
</sect3>
+
<sect3 id="nullvalues-multielementcomparison-sqlconformance">
<title>SQL Specification Conformance</title>
<para>
@@ -576,6 +587,7 @@
to the output of a subquery (as in <xref linkend="functions-subquery"/>).
</para>
</sect3>
+
</sect2>
<sect2 id="nullvalues-functions">
@@ -594,22 +606,22 @@
like concatenate, null values often are simply ignored.
This can be different than the choice made by a binary operator performing the same function,
like for concatenating text, but not always, like concatenating an element onto an array.
- <programlisting>
- SELECT
- lower(null::text) AS "Lower",
- left('text', null) AS "Left",
- 'one' || null AS "|| Text Op",
- concat('one', null) AS "concat Text Func",
- array_append(array[1], null) AS "append([], null)",
- array[1]::integer[] || null::integer AS "[] || null",
- array[1]::integer[] || null::integer[] AS "[] || null[]";
- </programlisting>
- <screen>
- Lower | Left | || Text Op | concat Text Func | append([], null) | [] || null | [] || null[]
- -------+------+------------+------------------+------------------+------------+--------------
- | | | one | {1,NULL} | {1,NULL} | {1}
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ lower(null::text) AS "Lower",
+ left('text', null) AS "Left",
+ 'one' || null AS "|| Text Op",
+ concat('one', null) AS "concat Text Func",
+ array_append(array[1], null) AS "append([], null)",
+ array[1]::integer[] || null::integer AS "[] || null",
+ array[1]::integer[] || null::integer[] AS "[] || null[]";
+</programlisting>
+<screen>
+ Lower | Left | || Text Op | concat Text Func | append([], null) | [] || null | [] || null[]
+ -------+------+------------+------------------+------------------+------------+--------------
+ | | | one | {1,NULL} | {1,NULL} | {1}
+ (1 row)
+</screen>
In short, please read the documentation for the functions you use if they may receive null inputs
to understand how they will behave. Send a documentation comment pointing out any functions
that do not behave strictly but whose actual behavior in the presence of null-valued input
@@ -626,20 +638,20 @@
or it simply returns a null value upon execution. The aggregation
routine will usually ignore the null value and continue processing,
as demonstrated in <literal>count(value)</literal> below.
- <programlisting>
- SELECT
- count(*) AS "Count",
- count(value) AS "Count Value",
- count(null_examples) AS "Count Composite",
- count(row(value, value)) AS "Count Row"
- FROM null_examples;
- </programlisting>
- <screen>
- Count | Count Value | Count Composite | Count Row
- -------+-------------+-----------------+-----------
- 3 | 2 | 3 | 3
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ count(*) AS "Count",
+ count(value) AS "Count Value",
+ count(null_examples) AS "Count Composite",
+ count(row(value, value)) AS "Count Row"
+ FROM null_examples;
+</programlisting>
+<screen>
+ Count | Count Value | Count Composite | Count Row
+ -------+-------------+-----------------+-----------
+ 3 | 2 | 3 | 3
+ (1 row)
+</screen>
Notice the "Count Row" outcome, though. While we noted in the cardinal warning
that a composite whose fields are all null values is indistinguishable from
a null value of composite type, the count aggregate does indeed distinguish them,
@@ -653,26 +665,26 @@
<title>Null Values in Where</title>
<para>
A <literal>WHERE</literal> clause that evaluates to a null value for a given row will exclude that row.
- <programlisting>
- SELECT id, value AS "Equals 1"
- FROM null_examples
- WHERE value = 1;
-
- SELECT id, value AS "Not Equal to 1"
- FROM null_examples
- WHERE value != 1;
- </programlisting>
- <screen>
- id | Equals 1
- ----+----------
- 1 | 1
- (1 row)
-
- id | Not Equal to 1
- ----+----------------
- 3 | 4
- (1 row)
- </screen>
+<programlisting>
+ SELECT id, value AS "Equals 1"
+ FROM null_examples
+ WHERE value = 1;
+
+ SELECT id, value AS "Not Equal to 1"
+ FROM null_examples
+ WHERE value != 1;
+</programlisting>
+<screen>
+ id | Equals 1
+ ----+----------
+ 1 | 1
+ (1 row)
+
+ id | Not Equal to 1
+ ----+----------------
+ 3 | 4
+ (1 row)
+</screen>
</para>
</sect2>
@@ -685,26 +697,26 @@
While this seems like it would behave the same as a where clause, the choice here,
when an expression evaulates to a null value, is to allow the row to be inserted
- the same as a true result.
- <programlisting>
- BEGIN;
- ALTER TABLE null_examples ADD CONSTRAINT value_not_1 CHECK (value != 1);
- ROLLBACK;
- </programlisting>
- <screen>
- BEGIN
- ERROR: check constraint "value_not_1" of relation "null_examples" is violated by some row
- ROLLBACK
- </screen>
- <programlisting>
- BEGIN;
- ALTER TABLE null_examples ADD CONSTRAINT value_not_10 CHECK (value != 10);
- ROLLBACK;
- </programlisting>
- <screen>
- BEGIN
- ALTER TABLE
- ROLLBACK
- </screen>
+<programlisting>
+ BEGIN;
+ ALTER TABLE null_examples ADD CONSTRAINT value_not_1 CHECK (value != 1);
+ ROLLBACK;
+</programlisting>
+<screen>
+ BEGIN
+ ERROR: check constraint "value_not_1" of relation "null_examples" is violated by some row
+ ROLLBACK
+</screen>
+<programlisting>
+ BEGIN;
+ ALTER TABLE null_examples ADD CONSTRAINT value_not_10 CHECK (value != 10);
+ ROLLBACK;
+</programlisting>
+<screen>
+ BEGIN
+ ALTER TABLE
+ ROLLBACK
+</screen>
We are using a transaction (begin and rollback) and the alter table command to add two
constraints to our null_examples table. The first constraint prohibits rows with a value
of 1, which our row with an id of 1 violates. Prohibiting the value 10 definitely allows
@@ -726,37 +738,37 @@
other values. These features use <link linkend="nullvalues-cardinalrule">distinctness</link>
instead of simple equality in order to handle a null value like a definite value equal to
another null vale and unequal to all other values.
- <programlisting>
- WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
- SELECT
- value,
- count(*) AS "Count"
- FROM vals
- GROUP BY value
- ORDER BY value;
- </programlisting>
- <screen>
- value | Count
- -------+-------
- 1 | 2
- 2 | 1
- | 2
- (3 rows)
- </screen>
- <programlisting>
- WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
- SELECT DISTINCT value
- FROM vals
- ORDER BY value NULLS FIRST;
- </programlisting>
- <screen>
- value
- -------
-
- 1
- 2
- (3 rows)
- </screen>
+<programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT
+ value,
+ count(*) AS "Count"
+ FROM vals
+ GROUP BY value
+ ORDER BY value;
+</programlisting>
+<screen>
+ value | Count
+ -------+-------
+ 1 | 2
+ 2 | 1
+ | 2
+ (3 rows)
+</screen>
+<programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT DISTINCT value
+ FROM vals
+ ORDER BY value NULLS FIRST;
+</programlisting>
+<screen>
+ value
+ -------
+
+ 1
+ 2
+ (3 rows)
+</screen>
</para>
</sect2>
@@ -768,21 +780,21 @@
present null values before or after all non-null values. To handle
this, the <literal>ORDER BY</literal> clause will let you specify either
<literal>NULLS FIRST</literal> or <literal>NULLS LAST</literal>.
- <programlisting>
- WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
- SELECT value FROM vals
- ORDER BY value DESC NULLS FIRST;
- </programlisting>
- <screen>
- value
- -------
+<programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT value FROM vals
+ ORDER BY value DESC NULLS FIRST;
+</programlisting>
+<screen>
+ value
+ -------
- 2
- 1
- 1
- (5 rows)
- </screen>
+ 2
+ 1
+ 1
+ (5 rows)
+</screen>
</para>
<para>
Note that when dealing with multi-element values the comparison behavior
@@ -804,33 +816,33 @@
values are equal to each other. This setting applies to all columns in the index.
</para>
<para>
- <programlisting>
- BEGIN;
- CREATE UNIQUE INDEX value_nulls_distinct_implicit ON null_examples (value);
- CREATE UNIQUE INDEX value_nulls_distinct_explicit ON null_examples (value) NULLS DISTINCT;
- INSERT INTO null_examples VALUES (4, NULL);
- ROLLBACK;
- </programlisting>
- <screen>
- BEGIN
- CREATE INDEX
- CREATE INDEX
- INSERT 0 1
- ROLLBACK
- </screen>
- <programlisting>
- BEGIN;
- CREATE UNIQUE INDEX value_nulls_not_distinct_explicit ON null_examples (value) NULLS NOT DISTINCT;
- INSERT INTO null_examples VALUES (4, NULL);
- ROLLBACK;
- </programlisting>
- <screen>
- BEGIN
- CREATE INDEX
- ERROR: duplicate key value violates unique constraint "value_nulls_not_distinct_explicit"
- DETAIL: Key (value)=(null) already exists.
- ROLLBACK
- </screen>
+<programlisting>
+ BEGIN;
+ CREATE UNIQUE INDEX value_nulls_distinct_implicit ON null_examples (value);
+ CREATE UNIQUE INDEX value_nulls_distinct_explicit ON null_examples (value) NULLS DISTINCT;
+ INSERT INTO null_examples VALUES (4, NULL);
+ ROLLBACK;
+</programlisting>
+<screen>
+ BEGIN
+ CREATE INDEX
+ CREATE INDEX
+ INSERT 0 1
+ ROLLBACK
+</screen>
+<programlisting>
+ BEGIN;
+ CREATE UNIQUE INDEX value_nulls_not_distinct_explicit ON null_examples (value) NULLS NOT DISTINCT;
+ INSERT INTO null_examples VALUES (4, NULL);
+ ROLLBACK;
+</programlisting>
+<screen>
+ BEGIN
+ CREATE INDEX
+ ERROR: duplicate key value violates unique constraint "value_nulls_not_distinct_explicit"
+ DETAIL: Key (value)=(null) already exists.
+ ROLLBACK
+</screen>
</para>
<para>
For ordering, each column in the index gets its own specification of
@@ -871,41 +883,41 @@
thus this section focuses on <link linkend="config-setting-sql">SQL interaction</link>.
Unlike settings created by extensions, custom settings can only be textual and the default
value for text here is the empty string.
- <programlisting>
- SHOW example.string;
- BEGIN;
- SELECT set_config('example.string', NULL, true);
- SELECT current_setting('example.string') IS NULL AS "Setting Is Null";
- ROLLBACK;
- SHOW example.string;
- RESET example.string;
- SHOW example.string;
- </programlisting>
- <screen>
- ERROR: unrecognized configuration parameter "example.string"
- BEGIN
- set_config
- ------------
-
- (1 row)
-
- Setting Is Null
- -----------------
- f
- (1 row)
-
- ROLLBACK
- example.string
- ----------------
-
- (1 row)
-
- RESET
- example.string
- ----------------
-
- (1 row)
- </screen>
+<programlisting>
+ SHOW example.string;
+ BEGIN;
+ SELECT set_config('example.string', NULL, true);
+ SELECT current_setting('example.string') IS NULL AS "Setting Is Null";
+ ROLLBACK;
+ SHOW example.string;
+ RESET example.string;
+ SHOW example.string;
+</programlisting>
+<screen>
+ ERROR: unrecognized configuration parameter "example.string"
+ BEGIN
+ set_config
+ ------------
+
+ (1 row)
+
+ Setting Is Null
+ -----------------
+ f
+ (1 row)
+
+ ROLLBACK
+ example.string
+ ----------------
+
+ (1 row)
+
+ RESET
+ example.string
+ ----------------
+
+ (1 row)
+</screen>
Notice two important behaviors: first, even though we passed in a null value to
to the <literal>set_config</literal> function, the <literal>current_setting</literal>
function returned a non-null value, specifically the empty string. Second, after ROLLBACK the
@@ -924,71 +936,72 @@
<para>
As noted in <xref linkend="json-type-mapping-table"/>, JSON has a null value
that does not get exposed at the SQL level.
- <programlisting>
- SELECT 'null'::json IS NULL AS "JSON null is NULL";
- </programlisting>
- <screen>
- JSON null is NULL
- -------------------
- f
- (1 row)
- </screen>
+<programlisting>
+ SELECT 'null'::json IS NULL AS "JSON null is NULL";
+</programlisting>
+<screen>
+ JSON null is NULL
+ -------------------
+ f
+ (1 row)
+</screen>
Additionally, the SQL operators and functions involving JSON key or array element selection,
or construction from literals, require that a number or text value be supplied as an operand
and so JSON null values cannot be targeted by those operators and functions.
- <programlisting>
- SELECT to_json(null::text);
- </programlisting>
- <screen>
- to_json
- ---------
-
- (1 row)
- </screen>
+<programlisting>
+ SELECT to_json(null::text);
+</programlisting>
+<screen>
+ to_json
+ ---------
+
+ (1 row)
+</screen>
That all said, the system will convert from SQL null values to JSON null values when in a
composite type context.
- <programlisting>
- SELECT json_build_object('value', value)
- FROM null_examples;
- </programlisting>
- <screen>
- json_build_object
- -------------------
- {"value" : 1}
- {"value" : null}
- {"value" : 4}
- (3 rows)
- </screen>
+<programlisting>
+ SELECT json_build_object('value', value)
+ FROM null_examples;
+</programlisting>
+<screen>
+ json_build_object
+ -------------------
+ {"value" : 1}
+ {"value" : null}
+ {"value" : 4}
+ (3 rows)
+</screen>
And vice versa.
- <programlisting>
- SELECT *
- FROM jsonb_to_recordset('[{"value":1},{"value":null},{"value":4}]'::jsonb) AS jtr (value integer);
- </programlisting>
- <screen>
- value
- -------
- 1
+<programlisting>
+ SELECT *
+ FROM jsonb_to_recordset('[{"value":1},{"value":null},{"value":4}]'::jsonb) AS jtr (value integer);
+</programlisting>
+<screen>
+ value
+ -------
+ 1
- 4
- (3 rows)
- </screen>
+ 4
+ (3 rows)
+</screen>
</para>
<para>
A more versatile way to process JSON is to use jsonpath. Within this context, as noted in
<xref linkend="functions-sqljson-filter-ex-table"/>, the JSON null value is considered equal
to other JSON null values. However, while equaltiy works as expected, ordering is not implemented.
- <programlisting>
- SELECT
- jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > null)') AS "GT",
- jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < null)') AS "LT",
- jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ <> null)') AS "NE";
- </programlisting>
- <screen>
- GT | LT | NE
- ----+----+-----------
- [] | [] | [1, 2, 3]
- (1 row)
- </screen>
+<programlisting>
+ SELECT
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > null)') AS "GT",
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < null)') AS "LT",
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ <> null)') AS "NE";
+</programlisting>
+<screen>
+ GT | LT | NE
+ ----+----+-----------
+ [] | [] | [1, 2, 3]
+ (1 row)
+</screen>
</para>
</sect2>
+
</sect1>
--
2.34.1
v4-0001-Document-NULL.patchtext/x-patch; charset=US-ASCII; name=v4-0001-Document-NULL.patchDownload
From 3aad04b4478dd3662131cf987b678ecc0158ac9a Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 1 May 2024 07:45:48 -0700
Subject: [PATCH v4 1/2] Document NULL
---
doc/src/sgml/datatype.sgml | 2 +-
doc/src/sgml/ddl.sgml | 2 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/func.sgml | 273 ++++----
doc/src/sgml/json.sgml | 7 +-
doc/src/sgml/nullvalues.sgml | 994 ++++++++++++++++++++++++++++
doc/src/sgml/ref/create_domain.sgml | 7 +-
doc/src/sgml/syntax.sgml | 23 +-
8 files changed, 1149 insertions(+), 160 deletions(-)
create mode 100644 doc/src/sgml/nullvalues.sgml
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 6646820d6a..c55fa607e8 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -5352,7 +5352,7 @@ WHERE ...
<row>
<entry><type>unknown</type></entry>
<entry>Identifies a not-yet-resolved type, e.g., of an undecorated
- string literal.</entry>
+ string literal. Also, the <link linkend="nullvalues-usage">null value.</link></entry>
</row>
</tbody>
</tgroup>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9b71c97bdf..9761f7578b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -168,6 +168,8 @@ DROP TABLE products;
</para>
</sect1>
+ &nullvalues;
+
<sect1 id="ddl-default">
<title>Default Values</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 38ec362d8f..882752e88f 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -21,6 +21,7 @@
<!ENTITY indices SYSTEM "indices.sgml">
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
+<!ENTITY nullvalues SYSTEM "nullvalues.sgml">
<!ENTITY parallel SYSTEM "parallel.sgml">
<!ENTITY perform SYSTEM "perform.sgml">
<!ENTITY queries SYSTEM "queries.sgml">
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2609269610..f0d19c8055 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23298,7 +23298,8 @@ MERGE INTO products p
This section describes the <acronym>SQL</acronym>-compliant subquery
expressions available in <productname>PostgreSQL</productname>.
All of the expression forms documented in this section return
- Boolean (true/false) results.
+ <link linkend="nullvalues">three-valued</link> typed
+ results (true, false, or null).
</para>
<sect2 id="functions-subquery-exists">
@@ -23360,19 +23361,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
+ subquery, which must return exactly one column. The result of <token>IN</token>
+ is <quote>false</quote> if the subquery returns no rows, otherwise the left-hand expression
is evaluated and compared to each row of the subquery result.
- The result of <token>IN</token> is <quote>true</quote> if any equal subquery row is found.
- The result is <quote>false</quote> if no equal row is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any equal subquery row is found.
+ The result is <quote>false</quote> if no equal row is found.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand row yields
- null, the result of the <token>IN</token> construct will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together.
</para>
<para>
@@ -23389,21 +23388,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row.
+ The result of <token>IN</token> is <quote>false</quote> if the subquery returns no rows,
+ otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result.
- The result of <token>IN</token> is <quote>true</quote> if any equal subquery row is found.
- The result is <quote>false</quote> if no equal row is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any equal subquery row is found.
+ The result is <quote>false</quote> if no equal row is found.
</para>
<para>
- As usual, null values in the rows are combined per
- the normal rules of SQL Boolean expressions. Two rows are considered
- equal if all their corresponding members are non-null and equal; the rows
- are unequal if any corresponding members are non-null and unequal;
- otherwise the result of that row comparison is unknown (null).
- If all the per-row results are either unequal or null, with at least one
- null, then the result of <token>IN</token> is null.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are OR'd together.
</para>
</sect2>
@@ -23415,20 +23411,17 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</synopsis>
<para>
- The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
- is evaluated and compared to each row of the subquery result.
- The result of <token>NOT IN</token> is <quote>true</quote> if only unequal subquery rows
- are found (including the case where the subquery returns no rows).
+ The right-hand side is a parenthesized subquery, which must return exactly one column.
+ The result of <token>NOT IN</token> is <quote>true</quote> if the subquery returns no rows,
+ otherwise the left-hand expression is evaluated and compared to each row of the subquery result.
+ The result is <quote>true</quote> if only unequal subquery rows are found.
The result is <quote>false</quote> if any equal row is found.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand row yields
- null, the result of the <token>NOT IN</token> construct will be null, not true.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple inequality
+ tests are OR'd together.
</para>
<para>
@@ -23445,21 +23438,18 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row.
+ The result of <token>NOT IN</token> is <quote>true</quote> if the subquery returns no rows,
+ otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result.
- The result of <token>NOT IN</token> is <quote>true</quote> if only unequal subquery rows
- are found (including the case where the subquery returns no rows).
+ The result is <quote>true</quote> if only unequal subquery rows are found.
The result is <quote>false</quote> if any equal row is found.
</para>
<para>
- As usual, null values in the rows are combined per
- the normal rules of SQL Boolean expressions. Two rows are considered
- equal if all their corresponding members are non-null and equal; the rows
- are unequal if any corresponding members are non-null and unequal;
- otherwise the result of that row comparison is unknown (null).
- If all the per-row results are either unequal or null, with at least one
- null, then the result of <token>NOT IN</token> is null.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple inequality
+ tests are OR'd together.
</para>
</sect2>
@@ -23473,13 +23463,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
+ subquery, which must return exactly one column. The result of <token>ANY</token> is
+ <quote>false</quote> if the subquery returns no rows, otherwise the left-hand expression
is evaluated and compared to each row of the subquery result using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained.
- The result is <quote>false</quote> if no true result is found (including the
- case where the subquery returns no rows).
+ The result is <quote>true</quote> if any true result is obtained.
+ The result is <quote>false</quote> if no true result is found.
</para>
<para>
@@ -23488,11 +23478,10 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</para>
<para>
- Note that if there are no successes and at least one right-hand row yields
- null for the operator's result, the result of the <token>ANY</token> construct
- will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -23510,16 +23499,19 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row. The result of <token>ANY</token> is
+ <quote>false</quote> if the subquery returns no rows, otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result,
using the given <replaceable>operator</replaceable>.
- The result of <token>ANY</token> is <quote>true</quote> if the comparison
- returns true for any subquery row.
- The result is <quote>false</quote> if the comparison returns false for every
- subquery row (including the case where the subquery returns no
- rows).
- The result is NULL if no comparison with a subquery row returns true,
- and at least one comparison returns NULL.
+ The result is <quote>true</quote> if the comparison returns true for any subquery row.
+ The result is <quote>false</quote> if the comparison returns false for every subquery row.
+ </para>
+
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both rows and null values since the multiple equality
+ tests are OR'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -23537,15 +23529,20 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized
- subquery, which must return exactly one column. The left-hand expression
+ subquery, which must return exactly one column. The result of <token>ALL</token> is
+ <quote>true</quote> if the subquery returns no rows, otherwise the left-hand expression
is evaluated and compared to each row of the subquery result using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ALL</token> is <quote>true</quote> if all rows yield true
- (including the case where the subquery returns no rows).
+ The result is <quote>true</quote> if all rows yield true.
The result is <quote>false</quote> if any false result is found.
- The result is NULL if no comparison with a subquery row returns false,
- and at least one comparison returns NULL.
+ </para>
+
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
@@ -23566,22 +23563,21 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
as described in <xref linkend="sql-syntax-row-constructors"/>.
The right-hand side is a parenthesized
subquery, which must return exactly as many columns as there are
- expressions in the left-hand row. The left-hand expressions are
+ expressions in the left-hand row. The result of <token>ALL</token> is
+ <quote>true</quote> if the subquery returns no rows, otherwise the left-hand expressions are
evaluated and compared row-wise to each row of the subquery result,
using the given <replaceable>operator</replaceable>.
- The result of <token>ALL</token> is <quote>true</quote> if the comparison
- returns true for all subquery rows (including the
- case where the subquery returns no rows).
- The result is <quote>false</quote> if the comparison returns false for any
- subquery row.
- The result is NULL if no comparison with a subquery row returns false,
- and at least one comparison returns NULL.
+ The result is <quote>true</quote> if the comparison returns true for all subquery rows.
+ The result is <quote>false</quote> if the comparison returns false for any subquery row.
</para>
<para>
- See <xref linkend="row-wise-comparison"/> for details about the meaning
- of a row constructor comparison.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both rows and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
+
</sect2>
<sect2 id="functions-subquery-single-row-comp">
@@ -23606,6 +23602,14 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
compared row-wise to the single subquery result row.
</para>
+ <para>
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, the result cannot be <quote>true</quote> in the
+ presence of null valued fields in either the row constructor or the subquery result row, as
+ the individual field tests are AND'd together.
+ Note that <literal>IS DISTINCT FROM</literal> is not an operator.
+ </para>
+
<para>
See <xref linkend="row-wise-comparison"/> for details about the meaning
of a row constructor comparison.
@@ -23673,7 +23677,8 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<productname>PostgreSQL</productname> extensions; the rest are
<acronym>SQL</acronym>-compliant.
All of the expression forms documented in this section return
- Boolean (true/false) results.
+ <link linkend="nullvalues">three-valued</link> boolean typed
+ results (true, false, or null).
</para>
<sect2 id="functions-comparisons-in-scalar">
@@ -23686,24 +23691,13 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
<para>
The right-hand side is a parenthesized list
of expressions. The result is <quote>true</quote> if the left-hand expression's
- result is equal to any of the right-hand expressions. This is a shorthand
- notation for
-
-<synopsis>
-<replaceable>expression</replaceable> = <replaceable>value1</replaceable>
-OR
-<replaceable>expression</replaceable> = <replaceable>value2</replaceable>
-OR
-...
-</synopsis>
+ result is equal to any of the right-hand expressions.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand expression yields
- null, the result of the <token>IN</token> construct will be null, not false.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence null values since the multiple equality
+ tests are OR'd together.
</para>
</sect2>
@@ -23717,35 +23711,15 @@ OR
<para>
The right-hand side is a parenthesized list
of expressions. The result is <quote>true</quote> if the left-hand expression's
- result is unequal to all of the right-hand expressions. This is a shorthand
- notation for
-
-<synopsis>
-<replaceable>expression</replaceable> <> <replaceable>value1</replaceable>
-AND
-<replaceable>expression</replaceable> <> <replaceable>value2</replaceable>
-AND
-...
-</synopsis>
+ result is unequal to all of the right-hand expressions.
</para>
<para>
- Note that if the left-hand expression yields null, or if there are
- no equal right-hand values and at least one right-hand expression yields
- null, the result of the <token>NOT IN</token> construct will be null, not true
- as one might naively expect.
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ As explained in <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of null values since the multiple inequality
+ tests are OR'd together.
</para>
- <tip>
- <para>
- <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
- cases. However, null values are much more likely to trip up the novice when
- working with <token>NOT IN</token> than when working with <token>IN</token>.
- It is best to express your condition positively if possible.
- </para>
- </tip>
</sect2>
<sect2 id="functions-comparisons-any-some">
@@ -23758,30 +23732,26 @@ AND
<para>
The right-hand side is a parenthesized expression, which must yield an
- array value.
- The left-hand expression
+ array value. The result of <token>ANY</token> is
+ <quote>false</quote> if the array has zero element, otherwise
+ the left-hand expression
is evaluated and compared to each element of the array using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ANY</token> is <quote>true</quote> if any true result is obtained.
- The result is <quote>false</quote> if no true result is found (including the
- case where the array has zero elements).
+ The result is <quote>true</quote> if any true result is obtained.
+ The result is <quote>false</quote> if no true result is found.
</para>
<para>
- If the array expression yields a null array, the result of
- <token>ANY</token> will be null. If the left-hand expression yields null,
- the result of <token>ANY</token> is ordinarily null (though a non-strict
- comparison operator could possibly yield a different result).
- Also, if the right-hand array contains any null elements and no true
- comparison result is obtained, the result of <token>ANY</token>
- will be null, not false (again, assuming a strict comparison operator).
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>false</quote> result in the presence of both elements and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
<para>
<token>SOME</token> is a synonym for <token>ANY</token>.
+ <token>IN</token> is equivalent to <literal>= ANY</literal>.
</para>
</sect2>
@@ -23795,26 +23765,27 @@ AND
<para>
The right-hand side is a parenthesized expression, which must yield an
array value.
- The left-hand expression
+ The result of <token>ALL</token> is
+ <quote>true</quote> if the array has zero elements, otherwise
+ the left-hand expression
is evaluated and compared to each element of the array using the
given <replaceable>operator</replaceable>, which must yield a Boolean
result.
- The result of <token>ALL</token> is <quote>true</quote> if all comparisons yield true
- (including the case where the array has zero elements).
+ The result is <quote>true</quote> if all comparisons yield true.
The result is <quote>false</quote> if any false result is found.
</para>
<para>
- If the array expression yields a null array, the result of
- <token>ALL</token> will be null. If the left-hand expression yields null,
- the result of <token>ALL</token> is ordinarily null (though a non-strict
- comparison operator could possibly yield a different result).
- Also, if the right-hand array contains any null elements and no false
- comparison result is obtained, the result of <token>ALL</token>
- will be null, not true (again, assuming a strict comparison operator).
- This is in accordance with SQL's normal rules for Boolean combinations
- of null values.
+ If <replaceable>operator</replaceable> can produce null valued booleans then, as explained in
+ <xref linkend="nullvalues-multielement"/>, it is not possible to see
+ a <quote>true</quote> result in the presence of both elements and null values since the multiple equality
+ tests are AND'd together. Note that <literal>IS DISTINCT FROM</literal> is not an operator.
</para>
+
+ <para>
+ <token>NOT IN</token> is equivalent to <literal><> ALL</literal>.
+ </para>
+
</sect2>
<sect2 id="row-wise-comparison">
@@ -23865,6 +23836,11 @@ AND
considered.
</para>
+ <para>
+ See <xref linkend="nullvalues-multielementcomparison-rowconstructor"/>
+ and surrounding content for additional details and examples.
+ </para>
+
<synopsis>
<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable>
</synopsis>
@@ -23899,20 +23875,11 @@ AND
</synopsis>
<para>
- The SQL specification requires row-wise comparison to return NULL if the
- result depends on comparing two NULL values or a NULL and a non-NULL.
- <productname>PostgreSQL</productname> does this only when comparing the
- results of two row constructors (as in
- <xref linkend="row-wise-comparison"/>) or comparing a row constructor
- to the output of a subquery (as in <xref linkend="functions-subquery"/>).
- In other contexts where two composite-type values are compared, two
- NULL field values are considered equal, and a NULL is considered larger
- than a non-NULL. This is necessary in order to have consistent sorting
- and indexing behavior for composite types.
- </para>
-
- <para>
- Each side is evaluated and they are compared row-wise. Composite type
+ Each side is evaluated and they are compared row-wise.
+ As discussed and shown in <xref linkend="nullvalues-multielementcomparison-composite"/>,
+ null values are treated as being equal to other null values and greater
+ than all non-null values.
+ Composite type
comparisons are allowed when the <replaceable>operator</replaceable> is
<literal>=</literal>,
<literal><></literal>,
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 54648c459c..140f94e8e3 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -129,6 +129,11 @@
the corresponding <productname>PostgreSQL</productname> types.
</para>
+ <indexterm>
+ <primary>null value</primary>
+ <secondary sortas="json">within JSON</secondary>
+ </indexterm>
+
<table id="json-type-mapping-table">
<title>JSON Primitive Types and Corresponding <productname>PostgreSQL</productname> Types</title>
<tgroup cols="3">
@@ -162,7 +167,7 @@
<row>
<entry><type>null</type></entry>
<entry>(none)</entry>
- <entry>SQL <literal>NULL</literal> is a different concept</entry>
+ <entry>An SQL null value is similar, but see <xref linkend="nullvalues-json"/> for differences.</entry>
</row>
</tbody>
</tgroup>
diff --git a/doc/src/sgml/nullvalues.sgml b/doc/src/sgml/nullvalues.sgml
new file mode 100644
index 0000000000..6c4c29e305
--- /dev/null
+++ b/doc/src/sgml/nullvalues.sgml
@@ -0,0 +1,994 @@
+<sect1 id="nullvalues">
+ <title>Null Values Overview</title>
+
+ <indexterm>
+ <primary>null value</primary>
+ </indexterm>
+
+ <para>
+ This section first introduces the concept of null values and then goes
+ on to explain how different parts of the system behave when provided
+ one or more null value inputs. Examples throughout this section
+ can be executed so long as the following table and rows are created first.
+ </para>
+
+ <programlisting>
+ CREATE TABLE null_examples (
+ id bigint PRIMARY KEY,
+ value integer NULL
+ );
+ INSERT INTO null_examples
+ VALUES (1, 1), (2, NULL), (3, 4);
+ </programlisting>
+
+ <sect2 id="nullvalues-model">
+ <title>Meaning</title>
+ <para>
+ Generally, a null value is assumed to mean "unknown", but other interpretations
+ are common. A data model design may state that a null value
+ is to be used to represent "not applicable" - i.e., that a value is not
+ even possible. The null value also takes on a literal meaning of "not found"
+ when produced as the result of an outer join.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-usage">
+ <title>Usage</title>
+ <para>
+ A null value, like all values, must have a data type, and is valid for all data types.
+ </para>
+ <para>
+ As noted in the <link linkend="sql-syntax-constants-nullvalue">synatx chapter</link>,
+ a null value literal is written using the <literal>NULL</literal> keyword.
+ Its type is the <link linkend="datatype-pseudo">pseudo-type unknown</link>
+ but can be cast to any concrete data type.
+ </para>
+ <para>
+ <programlisting>
+ SELECT
+ NULL AS "Literal Null Value",
+ pg_typeof(null) AS "Type of Null",
+ pg_typeof(NuLl::text) AS "Type of Cast null",
+ cast(null as text) AS "Cast null value";
+ </programlisting>
+ <screen>
+ Literal Null Value | Type of Null | Type of Cast null | Cast null value
+ --------------------+--------------+-------------------+-----------------
+ | unknown | text |
+ (1 row)
+ </screen>
+ </para>
+ <para>
+ <programlisting>
+ SELECT text NULL;
+ </programlisting>
+ <screen>
+ ERROR: column "text" does not exist
+ LINE 1: select text NUll;
+ </screen>
+ </para>
+ <para>
+ The presence of null values 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. Put a bit more formally, the
+ Law of the Excluded Middle does not hold: i.e.,
+ p OR NOT(p) != true; for all p.
+ </para>
+ <para>
+ Aspects of the system that branch based upon
+ whether a condition variable is true or false must therefore
+ decide how to behave when the condition is a null value.
+ The remaining sub-sections summarize these decisions, as well
+ as other behaviors.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-cardinalrule">
+ <title>Distinctness - Overcoming the Cardinal Rule of Null Values</title>
+ <para>
+ The cardinal rule, a null value is
+ <link linkend="functions-comparison-op-table">
+ neither equal nor unequal
+ </link>
+ to any value, including other null values.
+ <programlisting>
+ SELECT
+ NULL = NULL AS "N = N",
+ NULL != NULL AS "N != N",
+ 1 = NULL AS "1 = N",
+ 1 != NULL AS "1 != N",
+ 1 = 1 AS "1 = 1",
+ 1 != 1 AS "1 != 1";
+ </programlisting>
+ <screen>
+ N = N | N != N | 1 = N | 1 != N | 1 = 1 | 1 != 1
+ -------+--------+-------+--------+-------+--------
+ | | | | t | f
+ (1 row)
+ </screen>
+ However, as with many rules, there are exceptions, which are
+ <link linkend="nullvalues-multielementcomparison">noted below</link>. Specifically,
+ when the two compared values are part of a larger multi-element value.
+ <programlisting>
+ SELECT
+ array[1,2]=array[1,null] AS "Array Equals";
+ </programlisting>
+ <screen>
+ Array Equals
+ --------------
+ f
+ (1 row)
+ </screen>
+ </para>
+ <para>
+ Because of this SQL specification mandated rule, checking for a null value has an
+ explicit <literal>IS NULL</literal> predicate, and additionally there comparison
+ predicates that consider a null value equal to other null values but unequal
+ to any other value (e.g., <literal>IS DISTINCT</literal>, and <literal>IS TRUE</literal>.)
+ These, and other predicates, are described in
+ <xref linkend="functions-comparison-pred-table"/>
+ <programlisting>
+ SELECT id, value,
+ value IS NULL AS "IS N",
+ value IS DISTINCT FROM id AS "IS D",
+ value != id AS "IS !="
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ id | value | IS N | IS D | IS !=
+ ----+-------+------+------+-------
+ 1 | 1 | f | f | f
+ 2 | | t | t |
+ 3 | 4 | f | t | t
+ (3 rows)
+ </screen>
+ </para>
+ <para>
+ There is also a cardinal warning: when dealing with
+ <link linkend="rowtypes">composite types</link> in
+ expressions; <literal>composite IS NULL</literal>
+ and <literal>composite IS NOT NUll</literal>
+ are not the opposites of each other in the case where some,
+ but not all, of the composite's fields are null values.
+ (The case where all fields are null is indistinguishable
+ from the composite as a whole being null.)
+ Write <literal>NOT(composite IS NULL)</literal> instead.
+ <programlisting>
+ SELECT
+ c,
+ c IS NULL AS "c IS N",
+ NOT(c IS NULL) AS "NOT c IS N",
+ c IS NOT NULL AS "c IS NOT N",
+ ROW(value, value) IS NULL AS "ROW(v,v) IS N",
+ ROW(value, value) IS NOT NULL AS "ROW(v,v) IS NOT N"
+ FROM null_examples AS c;
+ </programlisting>
+ <screen>
+ c | c IS N | NOT c IS N | c IS NOT N | ROW(v,v) IS N | ROW(v,v) IS NOT N
+ -------+--------+------------+------------+---------------+-------------------
+ (1,1) | f | t | t | f | t
+ (2,) | f | t | f | t | f
+ (3,4) | f | t | t | f | t
+ (3 rows)
+ </screen>
+ See the <link linkend="nullvalues-multielement">multi-element
+ testing section</link> below for an explanation.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-operands">
+ <title>Null-Valued Operands</title>
+ <para>
+ As a general expectation, operator invocation expressions where one of inputs
+ is a null value will result in a null-valued output.
+ <programlisting>
+ SELECT
+ 1 + null AS "Add",
+ 'text' || null AS "Concatenate";
+ </programlisting>
+ <screen>
+ Add | Concatenate
+ -----+-------------
+ |
+ (1 row)
+ </screen>
+ Operators that behave otherwise should document their deviation from this norm.
+ </para>
+ <para>
+ A notable example of this is the <literal>IN</literal> operator, which
+ uses equality, not distinctness, for testing.
+ <programlisting>
+ SELECT
+ 1 IN (1, null) AS "In Present",
+ 1 IN (2, null) AS "In MIssing",
+ null IN (1, 2) AS "N In Non-N",
+ null IN (null, 2) AS "N In N";
+ </programlisting>
+ <screen>
+ In Present | In Missing | N In Non-N | N In N
+ ------------+------------+------------+--------
+ t | | |
+ (1 row)
+ </screen>
+ This is just an extension of the multi-element testing behavior described
+ <link linkend="nullvalues-multielement">below</link>.
+ </para>
+ <para>
+ Experience shows that <literal>CASE</literal> expressions are also prone
+ to bugs since their format encourages binary logic thinking while a
+ <literal>WHEN</literal> test will not consider a null value to be a match.
+ <programlisting>
+ SELECT id, value,
+ CASE WHEN id = value THEN 'Equal' ELSE 'Not Equal' END AS "Affirm",
+ CASE WHEN id != value THEN 'Not Equal' ELSE 'Equal' END AS "Negate",
+ CASE WHEN value IS NULL THEN 'Null'
+ WHEN id = value THEN 'Equal'
+ ELSE 'Not Equal' END AS "Safe Affirm",
+ CASE WHEN value IS NULL THEN 'Null'
+ WHEN id != value THEN 'Not Equal'
+ ELSE 'Equal' END AS "Safe Negate"
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ id | value | Affirm | Negate | Safe Affirm | Safe Negate
+ ----+-------+-----------+-----------+-------------+-------------
+ 1 | 1 | Equal | Equal | Equal | Equal
+ 2 | | Not Equal | Equal | Null | Null
+ 3 | 4 | Not Equal | Not Equal | Not Equal | Not Equal
+ (3 rows)
+ </screen>
+ </para>
+ <para>
+ The boolean operators <literal>AND</literal> and <literal>OR</literal>
+ will ignore the null value input if the other input is sufficient to
+ to determine the outcome.
+ <programlisting>
+ SELECT
+ true OR null AS "T or N",
+ false OR null AS "F or N",
+ true AND null AS "T and N",
+ false AND null AS "F and N";
+ </programlisting>
+ <screen>
+ T or N | F or N | T and N | F and N
+ --------+--------+---------+---------
+ t | | | f
+ (1 row)
+ </screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-domains">
+ <title>Null Values in Domains</title>
+ <para>
+ A domain is a user-defined data type that can have a <literal>NOT NULL</literal> constraint.
+ However, some usages of domains will cause the resultant column to have the domain type but
+ the value will be null. The common way this happens is by including the domain column's table
+ on the right side of a left join.
+ <programlisting>
+ BEGIN;
+ CREATE DOMAIN domain_example AS integer NOT NULL;
+ CREATE TABLE domain_examples (de_id bigint PRIMARY KEY, de_value domain_example);
+ INSERT INTO domain_examples VALUES (1, 1), (2, 2);
+ SELECT *, pg_typeof(de_value)
+ FROM null_examples AS ne
+ LEFT JOIN domain_examples AS de ON ne.id = de.de_id;
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ CREATE DOMAIN
+ CREATE TABLE
+ INSERT 0 2
+ id | value | de_id | de_value | pg_typeof
+ ----+-------+-------+----------+----------------
+ 1 | 1 | 1 | 1 | domain_example
+ 2 | | 2 | 2 | domain_example
+ 3 | 4 | | | domain_example
+ (3 rows)
+
+ ROLLBACK
+ </screen>
+ Please see the details in the <link linkend="sql-createdomain-notes">
+ notes on the create domain page</link> for another example, as well as
+ commentary why this non-standard behavior exists.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-multielement">
+ <title>Testing Multi-Element Values with Null Elements</title>
+ <para>
+ Arrays and composite types are multi-element types. Here we also consider non-empty
+ <link linkend="functions-subquery">subquery results</link>
+ and the list of values specified in the
+ <link linkend="functions-comparisons-in-scalar">IN test</link>.
+ </para>
+ <para>
+ When a test is performed on one of these multi-element values
+ the system will iterate over each element, or pair of elements if the test is
+ <link linkend="row-wise-comparison">comparing two row constructors</link> to each other,
+ left-to-right, combining the results using the boolean operations
+ <link linkend="nullvalues-operands">discussed above</link>. For tests that
+ require an exhaustive search, (e.g., <literal>ALL</literal>, <literal>NOT IN</literal>)
+ the search effectively ends when a false result is found (<literal>AND</literal> combiners).
+ For tests that simply require a true result, (e.g., <literal>ANY</literal>,
+ <literal>IN</literal>) the search effectively ends when a true result is found
+ (<literal>OR</literal> combiners). Therefore:
+ <simplelist>
+ <member>
+ <literal>IN</literal> and <literal>ANY</literal>
+ (<literal>OR</literal>) cannot produce a false result in the presence of null, and
+ </member>
+ <member>
+ <literal>NOT IN</literal> and <literal>ALL</literal>
+ (<literal>AND</literal>) cannot produce a true result in the presence of null.
+ </member>
+ </simplelist>
+ This is because any exhaustive search will produce at least one null value result
+ that cannot be ignored.
+ </para>
+ <para>
+ The SQL specification requires that non-exhaustive
+ (e.g., <literal>IN</literal> and <literal>ANY</literal>) subquery tests
+ return false when there are no rows in the subquery result, and return true
+ for the exhaustive tests (i.e., <literal>ALL</literal>).
+ </para>
+ <para>
+ Note that the <link linkend="nullvalues-cardinalrule">cardinal warning</link>
+ noted above is just the application of this behavior to the
+ <literal>IS NULL</literal> and <literal>IS NOT NULL</literal>
+ tests, which are both exhaustive search tests guaranteed to produce at least one false result
+ when the composite has a mix of null and non-null values.
+ </para>
+ <para>
+ The rules above, in situations where a predicate or a scalar value
+ are being compared to a multi-element value, are discussed
+ <link linkend="nullvalues-multielementpredicates">next</link>.
+ Then the rules when two multi-element values are compared
+ to each other are discussed <link linkend="nullvalues-multielementcomparison">here</link>
+ (including the two row constructor comparison case.)
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-multielementpredicates">
+ <title>Multi-Element Predicates and Scalars</title>
+ <sect3 id="nullvalues-multielementpredicates-composites">
+ <title>Composite Fields</title>
+ <para>
+ When a composite typed valued is created a null value can be assigned to any
+ of its fields (see <xref linkend="rowtypes-constructing"/> for how to do this).
+ So long as at least one field is non-null the composite value
+ as whole exists and an <literal>IS NULL</literal> predicate will return false.
+ </para>
+ <para>
+ Applying the <literal>IS NOT NULL</literal> predicate to a composite value performs
+ checks whether all fields of the composite have non-null values. This is not the same
+ as a non-null composite value. Specifically, if the composite value has
+ a null-valued field then both the <literal>IS NOT NULL</literal> predicate and the
+ <literal>IS NULL</literal> predicate will return false.
+ <programlisting>
+ SELECT
+ ROW(1,2) IS NULL AS "Row Is Null",
+ ROW(1,2) IS NOT NULL AS "Row Is Not Null",
+ ROW(1,NULL) IS NULL AS "Row Is Null",
+ ROW(1,NULL) IS NOT NULL AS "Row Is Not Null";
+ </programlisting>
+ <screen>
+ Row Is Null | Row Is Not Null | Row Is Null | Row Is Not Null
+ -------------+-----------------+-------------+-----------------
+ f | t | f | f
+ (1 row)
+ </screen>
+ </para>
+ <para>
+ Please read <xref linkend="composite-type-comparison"/> for a complete treatment
+ on how <productname>PostgreSQL</productname> handles row-wise comparison. The
+ next two multi-element parts of this section discuss those comparisons in the
+ presence of null-valued fields, and also in terms of the SQL specification.
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementpredicates-arrays">
+ <title>Array Elements and IN Bag Members</title>
+ <para>
+ Examples of applying the behavior discussed in <xref linkend="nullvalues-multielement"/>
+ to arrays, and <literal>IN</literal> and <literal>NOT IN</literal> bags, using the
+ operators defined in <xref linkend="functions-comparisons"/>.
+ </para>
+ <para>
+ <programlisting>
+ SELECT
+ 1 = ANY(array[1, 1, NULL]) AS "Any-Null-Match",
+ 1 = ANY(array[1, 1]) AS "Any-NoNull-Match",
+ 1 = ALL(array[1, 1, NULL]) AS "ALL-Null-Match",
+ 1 = ALL(array[1, 1]) AS "All-NoNull-Match";
+ SELECT
+ 2 = ANY(array[1, 1, NULL]) AS "Any-Null-NoMatch",
+ 2 = ANY(array[1, 1]) AS "Any-NoNull-NoMatch",
+ 2 = ALL(array[1, 1, NULL]) AS "ALL-Null-NoMatch",
+ 2 = ALL(array[1, 1]) AS "All-NoNull-NoMatch";
+ SELECT
+ 1 IN (1, 1, NULL) AS "IN-Null-Positive",
+ 1 IN (1, 1) AS "IN-NoNull-Positive",
+ 1 NOT IN (2, 2, NULL) AS "NotIN-Null-Positive",
+ 1 NOT IN (2, 2) AS "NotIN-NoNull-Positive";
+ SELECT
+ 2 IN (1, 1, NULL) AS "IN-Null-Negative",
+ 2 IN (1, 1) AS "IN-NoNull-Negative",
+ 2 NOT IN (2, 2, NULL) AS "NotIN-Null-Negative",
+ 2 NOT IN (2, 2) AS "NotIN-NoNull-Negative";
+ </programlisting>
+ <screen>
+ Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
+ ----------------+------------------+----------------+------------------
+ t | t | | t
+ (1 row)
+
+ Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
+ ------------------+--------------------+------------------+--------------------
+ | f | f | f
+ (1 row)
+
+ IN-Null-Positive | IN-NoNull-Positive | NotIN-Null-Positive | NotIN-NoNull-Positive
+ ------------------+--------------------+---------------------+-----------------------
+ t | t | | t
+ (1 row)
+
+ IN-Null-Negative | IN-NoNull-Negative | NotIN-Null-Negative | NotIN-NoNull-Negative
+ ------------------+--------------------+---------------------+-----------------------
+ | f | f | f
+ (1 row)
+ </screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementpredicates-subqueries">
+ <title>Single-Column Subquery Rows</title>
+ <para>
+ Examples of applying the behavior discussed in <xref linkend="nullvalues-multielement"/>
+ to subqueries using the operators defined in <xref linkend="functions-subquery"/>. Note that
+ this section covers the multiple elements being checked are rows, each having one column. If
+ the column itself is multi-element then the thing being searched for must be a compatible
+ multi-element value, and the corresponding comparison behavior described in
+ <xref linkend="nullvalues-multielementcomparison"/> will also be applied.
+
+ </para>
+ <para>
+ <programlisting>
+ SELECT
+ 1 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-Match",
+ 1 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-Match",
+ 1 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-Match",
+ 1 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-Match";
+ SELECT
+ 2 = ANY(SELECT unnest(array[1, 1, NULL])) AS "Any-Null-NoMatch",
+ 2 = ANY(SELECT unnest(array[1, 1])) AS "Any-NoNull-NoMatch",
+ 2 = ALL(SELECT unnest(array[1, 1, NULL])) AS "ALL-Null-NoMatch",
+ 2 = ALL(SELECT unnest(array[1, 1])) AS "All-NoNull-NoMatch";
+ </programlisting>
+ <screen>
+ Any-Null-Match | Any-NoNull-Match | ALL-Null-Match | All-NoNull-Match
+ ----------------+------------------+----------------+------------------
+ t | t | | t
+ (1 row)
+
+ Any-Null-NoMatch | Any-NoNull-NoMatch | ALL-Null-NoMatch | All-NoNull-NoMatch
+ ------------------+--------------------+------------------+--------------------
+ | f | f | f
+ (1 row)
+ </screen>
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="nullvalues-multielementcomparison">
+ <title>Multi-Element Comparisons</title>
+ <para>
+ The <link linkend="nullvalues-multielementpredicates">prior section</link> discussed applying
+ a predicate or a scalar value check element-wise across a multi-element value.
+ This section moves the discussion over to comparing two multi-element values to each other.
+ As both array and composite typed values
+ can be stored within an index, and comparing two values in that context must not produce
+ a null-valued result, considerations are made to adhere to the SQL specification where
+ possible while still making indexes, which the specification is silent on, functional.
+ Specifically, except when comparing two row constructors, null values are considered
+ equal to other null values and greater than all non-null values.
+ </para>
+ <para>
+ There are five pair-wise comparison situations to consider:
+ element-wise when the inputs are arrays, and row-wise when the inputs can be either
+ row constructors or composite typed values. While these four later combinations seem similar,
+ the fact that row constructors are query literals, while composite typed values can be stored,
+ brings about important differences in how they are treated. Please read
+ <xref linkend="composite-type-comparison"/> for a fuller treatment of this topic. Here
+ we briefly recap the five different situations in the presence of null values.
+ </para>
+ <sect3 id="nullvalues-multielementcomparison-array">
+ <title>Element-wise Comparisons</title>
+ <para>
+ First situation, null values within an array compare as equal to each other and greater than all
+ non-null values, regardless of whether the comparison involves
+ <link linkend="sql-syntax-array-constructors">array constructors</link> or array typed values.
+ <programlisting>
+ SELECT
+ array[1,2]=array[1,null] AS "Constructors",
+ s, t,
+ s = t AS "Stored Equality",
+ t > s AS "Stored Ordering"
+ FROM
+ (values (array[1,2])) AS sv (s),
+ (values (array[1,null::integer])) AS st (t);
+ </programlisting>
+ <screen>
+ Constructors | s | t | Stored Equality | Stored Ordering
+ --------------+-------+----------+-----------------+-----------------
+ f | {1,2} | {1,NULL} | f | t
+ (1 row)
+ </screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-rowconstructor">
+ <title>Row-wise Mutual Row Constructor Comparisons</title>
+ <para>
+ In this situation null values produce unknown when compared to all values.
+ <programlisting>
+ SELECT
+ (1,2)=(1,null) AS "NonNull=Null",
+ (1,null::integer)=(1,null) AS "Null=Null";
+ </programlisting>
+ <screen>
+ NonNull=Null | Null=Null
+ --------------+-----------
+ |
+ (1 row)
+ </screen>
+ </para>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-composite">
+ <title>Row-wise Composite Involved Comparisons</title>
+ <para>
+ In these three situations null values are considered equal to each other and greater than
+ all non-null value.
+ </para>
+ <programlisting>
+ SELECT s, t,
+ s = t AS "Stored Equals Stored",
+ t < (1,2) AS "Stored LT Constructor",
+ t = (1,null::integer) AS "Stored Equals Constructor"
+ FROM
+ (values (1,2)) AS s,
+ (values (1,null::integer)) AS t;
+ </programlisting>
+ <screen>
+ s | t | Stored Equals Stored | Stored LT Constructor | Stored Equals Constructor
+ -------+------+----------------------+-----------------------+---------------------------
+ (1,2) | (1,) | f | f | t
+ (1 row)
+ </screen>
+ </sect3>
+ <sect3 id="nullvalues-multielementcomparison-sqlconformance">
+ <title>SQL Specification Conformance</title>
+ <para>
+ The SQL specification requires row-wise comparison to return NULL if the
+ result depends on comparing two NULL values or a NULL and a non-NULL.
+ <productname>PostgreSQL</productname> does this only when comparing the
+ results of two row constructors (as in
+ <xref linkend="row-wise-comparison"/>) or comparing a row constructor
+ to the output of a subquery (as in <xref linkend="functions-subquery"/>).
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="nullvalues-functions">
+ <title>Null-Valued Arguments in Normal Function Calls</title>
+ <para>
+ <link linkend="sql-createfunction">Function specifications</link>
+ have a "strictness" attribute (<literal>pg_proc.proisstrict</literal>) that,
+ when set to "strict" (true) will tell the executor to return a null value for any
+ function call having at least one null-valued input, without executing the
+ function.
+ </para>
+ <para>
+ Most functions, especially single argument functions, are defined with strict because without
+ non-null values to act upon they cannot produce a meaningful result. However, for multi-argument
+ functions, especially <link linkend="xfunc-sql-variadic-functions">variadic functions</link>
+ like concatenate, null values often are simply ignored.
+ This can be different than the choice made by a binary operator performing the same function,
+ like for concatenating text, but not always, like concatenating an element onto an array.
+ <programlisting>
+ SELECT
+ lower(null::text) AS "Lower",
+ left('text', null) AS "Left",
+ 'one' || null AS "|| Text Op",
+ concat('one', null) AS "concat Text Func",
+ array_append(array[1], null) AS "append([], null)",
+ array[1]::integer[] || null::integer AS "[] || null",
+ array[1]::integer[] || null::integer[] AS "[] || null[]";
+ </programlisting>
+ <screen>
+ Lower | Left | || Text Op | concat Text Func | append([], null) | [] || null | [] || null[]
+ -------+------+------------+------------------+------------------+------------+--------------
+ | | | one | {1,NULL} | {1,NULL} | {1}
+ (1 row)
+ </screen>
+ In short, please read the documentation for the functions you use if they may receive null inputs
+ to understand how they will behave. Send a documentation comment pointing out any functions
+ that do not behave strictly but whose actual behavior in the presence of null-valued input
+ is not described or readily inferred.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-aggregates">
+ <title>Null-Valued Arguments in Aggregate and Window Functions</title>
+ <para>
+ When executing an aggregate or window function the state tracking
+ component will remain unchanged even if the underlying processing
+ function returns a null value, whether from being defined strict
+ or it simply returns a null value upon execution. The aggregation
+ routine will usually ignore the null value and continue processing,
+ as demonstrated in <literal>count(value)</literal> below.
+ <programlisting>
+ SELECT
+ count(*) AS "Count",
+ count(value) AS "Count Value",
+ count(null_examples) AS "Count Composite",
+ count(row(value, value)) AS "Count Row"
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ Count | Count Value | Count Composite | Count Row
+ -------+-------------+-----------------+-----------
+ 3 | 2 | 3 | 3
+ (1 row)
+ </screen>
+ Notice the "Count Row" outcome, though. While we noted in the cardinal warning
+ that a composite whose fields are all null values is indistinguishable from
+ a null value of composite type, the count aggregate does indeed distinguish them,
+ recognizing and counting the non-null composite value produced by the
+ <link linkend="sql-syntax-row-constructors">row constructor</link>
+ <literal>row(null, null)</literal>.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-where">
+ <title>Null Values in Where</title>
+ <para>
+ A <literal>WHERE</literal> clause that evaluates to a null value for a given row will exclude that row.
+ <programlisting>
+ SELECT id, value AS "Equals 1"
+ FROM null_examples
+ WHERE value = 1;
+
+ SELECT id, value AS "Not Equal to 1"
+ FROM null_examples
+ WHERE value != 1;
+ </programlisting>
+ <screen>
+ id | Equals 1
+ ----+----------
+ 1 | 1
+ (1 row)
+
+ id | Not Equal to 1
+ ----+----------------
+ 3 | 4
+ (1 row)
+ </screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-table-constraints">
+ <title>Null Values in Table Constraints</title>
+ <para>
+ It is possible to define
+ <link linkend="ddl-constraints-check-constraints">check constraint</link>
+ expressions on tables to ensure only values passing those expressions are inserted.
+ While this seems like it would behave the same as a where clause, the choice here,
+ when an expression evaulates to a null value, is to allow the row to be inserted
+ - the same as a true result.
+ <programlisting>
+ BEGIN;
+ ALTER TABLE null_examples ADD CONSTRAINT value_not_1 CHECK (value != 1);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ ERROR: check constraint "value_not_1" of relation "null_examples" is violated by some row
+ ROLLBACK
+ </screen>
+ <programlisting>
+ BEGIN;
+ ALTER TABLE null_examples ADD CONSTRAINT value_not_10 CHECK (value != 10);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ ALTER TABLE
+ ROLLBACK
+ </screen>
+ We are using a transaction (begin and rollback) and the alter table command to add two
+ constraints to our null_examples table. The first constraint prohibits rows with a value
+ of 1, which our row with an id of 1 violates. Prohibiting the value 10 definitely allows
+ rows with ids 1 and 3 to exist, and since we are not told that some row violates our
+ constraint the null value in the row with id 2 is being accepted as well.
+ </para>
+ <para>
+ The <link linkend="ddl-constraints-not-null"><literal>NOT NULL</literal> column constraint</link>
+ produces the same answer as a <literal>column IS NOT NULL</literal> check constraint but is
+ more concise to write.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-grouping">
+ <title>Null Values When Grouping</title>
+ <para>
+ In the context of both <literal>DISTINCT</literal> and <literal>GROUP BY</literal>
+ it is necessary that all inputs resolve to being either equal to or not equal to all
+ other values. These features use <link linkend="nullvalues-cardinalrule">distinctness</link>
+ instead of simple equality in order to handle a null value like a definite value equal to
+ another null vale and unequal to all other values.
+ <programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT
+ value,
+ count(*) AS "Count"
+ FROM vals
+ GROUP BY value
+ ORDER BY value;
+ </programlisting>
+ <screen>
+ value | Count
+ -------+-------
+ 1 | 2
+ 2 | 1
+ | 2
+ (3 rows)
+ </screen>
+ <programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT DISTINCT value
+ FROM vals
+ ORDER BY value NULLS FIRST;
+ </programlisting>
+ <screen>
+ value
+ -------
+
+ 1
+ 2
+ (3 rows)
+ </screen>
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-ordering">
+ <title>Null Values When Ordering</title>
+ <para>
+ In the context of <literal>ORDER BY</literal>, distinctness rules also apply,
+ though this is insufficient since it must be determined whether or not to
+ present null values before or after all non-null values. To handle
+ this, the <literal>ORDER BY</literal> clause will let you specify either
+ <literal>NULLS FIRST</literal> or <literal>NULLS LAST</literal>.
+ <programlisting>
+ WITH vals (value) AS (VALUES (1), (NULL), (1), (2), (NULL))
+ SELECT value FROM vals
+ ORDER BY value DESC NULLS FIRST;
+ </programlisting>
+ <screen>
+ value
+ -------
+
+
+ 2
+ 1
+ 1
+ (5 rows)
+ </screen>
+ </para>
+ <para>
+ Note that when dealing with multi-element values the comparison behavior
+ <link linkend="nullvalues-multielementcomparison">described above</link> applies,
+ if the comparison determination rests upon comparing a null value to a non-null value
+ the multi-element value with the null-valued component will sort greater than the one
+ with a non-null component.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-indexed">
+ <title>Null Values in Indexes</title>
+ <para>
+ The uniqueness and relative ordering rules applied to null values
+ are defined when creating an index. For the default
+ <literal>NULLS DISTINCT</literal> uniqueness, equality rules are applied.
+ Specifying <literal>NULLS NOT DISTINCT</literal> will result in
+ <literal>IS DISTINCT FROM</literal> rules being applied whereby all null
+ values are equal to each other. This setting applies to all columns in the index.
+ </para>
+ <para>
+ <programlisting>
+ BEGIN;
+ CREATE UNIQUE INDEX value_nulls_distinct_implicit ON null_examples (value);
+ CREATE UNIQUE INDEX value_nulls_distinct_explicit ON null_examples (value) NULLS DISTINCT;
+ INSERT INTO null_examples VALUES (4, NULL);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ CREATE INDEX
+ CREATE INDEX
+ INSERT 0 1
+ ROLLBACK
+ </screen>
+ <programlisting>
+ BEGIN;
+ CREATE UNIQUE INDEX value_nulls_not_distinct_explicit ON null_examples (value) NULLS NOT DISTINCT;
+ INSERT INTO null_examples VALUES (4, NULL);
+ ROLLBACK;
+ </programlisting>
+ <screen>
+ BEGIN
+ CREATE INDEX
+ ERROR: duplicate key value violates unique constraint "value_nulls_not_distinct_explicit"
+ DETAIL: Key (value)=(null) already exists.
+ ROLLBACK
+ </screen>
+ </para>
+ <para>
+ For ordering, each column in the index gets its own specification of
+ direction and null value placement similar to that found in the
+ <literal>ORDER BY</literal> clause.
+ </para>
+ <para>
+ Note that when dealing with multi-element values the comparison behavior
+ <link linkend="nullvalues-multielementcomparison">described above</link> applies,
+ if the comparison determination rests upon comparing a null value to a non-null value
+ the multi-element value with the null-valued component will sort greater than the one
+ with a non-null component.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-partitionkeys">
+ <title>Null Values in Partiton Keys</title>
+ <para>
+ At present this is typically a non-issue as <productname>PostgreSQL</productname>
+ does not support a primary key that does not include partition key columns, and
+ all columns in a primary key are forced to be have not null constraints.
+ </para>
+ <para>
+ However, should you setup a situation where a partition key column can both: have a null value
+ and, null values in that key go to a specific partition, list-based routing will work as expected.
+ There is presently no way to direct rows having null values in partition keys away from the
+ default partition for range and hash partitioning.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-settings">
+ <title>Null-Valued Settings</title>
+ <para>
+ There are none. During initializion all settings are assigned a non-null value.
+ </para>
+ <para>
+ This is mostly meaningful for <link linkend="runtime-config-custom">custom settings</link>,
+ thus this section focuses on <link linkend="config-setting-sql">SQL interaction</link>.
+ Unlike settings created by extensions, custom settings can only be textual and the default
+ value for text here is the empty string.
+ <programlisting>
+ SHOW example.string;
+ BEGIN;
+ SELECT set_config('example.string', NULL, true);
+ SELECT current_setting('example.string') IS NULL AS "Setting Is Null";
+ ROLLBACK;
+ SHOW example.string;
+ RESET example.string;
+ SHOW example.string;
+ </programlisting>
+ <screen>
+ ERROR: unrecognized configuration parameter "example.string"
+ BEGIN
+ set_config
+ ------------
+
+ (1 row)
+
+ Setting Is Null
+ -----------------
+ f
+ (1 row)
+
+ ROLLBACK
+ example.string
+ ----------------
+
+ (1 row)
+
+ RESET
+ example.string
+ ----------------
+
+ (1 row)
+ </screen>
+ Notice two important behaviors: first, even though we passed in a null value to
+ to the <literal>set_config</literal> function, the <literal>current_setting</literal>
+ function returned a non-null value, specifically the empty string. Second, after ROLLBACK the
+ setting is still present (i.e., the error seen before creating the setting no longer appears),
+ and in fact will remain so until the session ends
+ (i.e., RESET does not restore the non-existence state.)
+ </para>
+ <para>
+ The other ways to specify settings do not have a means to specify null values,
+ a specific non-null value is required as part of the specification of the setting.
+ </para>
+ </sect2>
+
+ <sect2 id="nullvalues-json">
+ <title>Null Values in JSON</title>
+ <para>
+ As noted in <xref linkend="json-type-mapping-table"/>, JSON has a null value
+ that does not get exposed at the SQL level.
+ <programlisting>
+ SELECT 'null'::json IS NULL AS "JSON null is NULL";
+ </programlisting>
+ <screen>
+ JSON null is NULL
+ -------------------
+ f
+ (1 row)
+ </screen>
+ Additionally, the SQL operators and functions involving JSON key or array element selection,
+ or construction from literals, require that a number or text value be supplied as an operand
+ and so JSON null values cannot be targeted by those operators and functions.
+ <programlisting>
+ SELECT to_json(null::text);
+ </programlisting>
+ <screen>
+ to_json
+ ---------
+
+ (1 row)
+ </screen>
+ That all said, the system will convert from SQL null values to JSON null values when in a
+ composite type context.
+ <programlisting>
+ SELECT json_build_object('value', value)
+ FROM null_examples;
+ </programlisting>
+ <screen>
+ json_build_object
+ -------------------
+ {"value" : 1}
+ {"value" : null}
+ {"value" : 4}
+ (3 rows)
+ </screen>
+ And vice versa.
+ <programlisting>
+ SELECT *
+ FROM jsonb_to_recordset('[{"value":1},{"value":null},{"value":4}]'::jsonb) AS jtr (value integer);
+ </programlisting>
+ <screen>
+ value
+ -------
+ 1
+
+ 4
+ (3 rows)
+ </screen>
+ </para>
+ <para>
+ A more versatile way to process JSON is to use jsonpath. Within this context, as noted in
+ <xref linkend="functions-sqljson-filter-ex-table"/>, the JSON null value is considered equal
+ to other JSON null values. However, while equaltiy works as expected, ordering is not implemented.
+ <programlisting>
+ SELECT
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > null)') AS "GT",
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < null)') AS "LT",
+ jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ <> null)') AS "NE";
+ </programlisting>
+ <screen>
+ GT | LT | NE
+ ----+----+-----------
+ [] | [] | [1, 2, 3]
+ (1 row)
+ </screen>
+ </para>
+ </sect2>
+</sect1>
diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml
index ce55520348..027a145f2c 100644
--- a/doc/src/sgml/ref/create_domain.sgml
+++ b/doc/src/sgml/ref/create_domain.sgml
@@ -197,9 +197,10 @@ CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replacea
Domain constraints, particularly <literal>NOT NULL</literal>, are checked when
converting a value to the domain type. It is possible for a column that
is nominally of the domain type to read as null despite there being such
- a constraint. For example, this can happen in an outer-join query, if
- the domain column is on the nullable side of the outer join. A more
- subtle example is
+ a constraint. For example, this can happen in
+ <link linkend="nullvalues-domains">an outer-join query</link>, if
+ the domain column is on the nullable side of the outer join.
+ A more subtle example is
<programlisting>
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
</programlisting>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 4dfbbd0862..7aa811a448 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -281,9 +281,9 @@ U&"d!0061t!+000061" UESCAPE '!'
</indexterm>
<para>
- There are three kinds of <firstterm>implicitly-typed
+ There are four kinds of <firstterm>implicitly-typed
constants</firstterm> in <productname>PostgreSQL</productname>:
- strings, bit strings, and numbers.
+ strings, bit strings, numbers, and the null value.
Constants can also be specified with explicit types, which can
enable more accurate representation and more efficient handling by
the system. These alternatives are discussed in the following
@@ -834,6 +834,25 @@ CAST ( '<replaceable>string</replaceable>' AS <replaceable>type</replaceable> )
usage, as is the function-call syntax.
</para>
</sect3>
+
+ <sect3 id="sql-syntax-constants-nullvalue">
+ <title>The Null Value Constant</title>
+ <indexterm>
+ <primary>null value</primary>
+ <secondary>constant</secondary>
+ </indexterm>
+ <para>
+ The null value represents an unknown value and its constant, the keyword <literal>NULL</literal>,
+ when evaluated in an expression, likewise yields a value of <literal>unknown</literal> type.
+ See <xref linkend="nullvalues"/> for an overview of how the system behaves in the presence
+ of a null value in various contexts.
+ </para>
+ <para>
+ Due to the typing of a null value as <literal>unknown</literal> it is often necessary to use
+ a cast, as described in the previous section, to convert it to the specific type needed.
+ However, implicit casting is performed when contextual information is available.
+ </para>
+ </sect3>
</sect2>
<sect2 id="sql-syntax-operators">
--
2.34.1
On Sat, Jun 29, 2024 at 4:40 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
The attached are complete and ready for review. I did some file structure reformatting at the end and left that as the second patch. The first contains all of the content.
I'm adding this to the commitfest.
Thanks!
David J.
in doc/src/sgml/nullvalues.sgml
can we mention
\pset null NULL
command, then NULL means this value is NULL.
you can also see doc/src/sgml/func.sgml
(The above example can be copied-and-pasted
into <application>psql</application> to set things up for the following
examples.
-------------------------------------------------------------
in doc/src/sgml/nullvalues.sgml
see the attached for one example output
in doc/src/sgml/nullvalues.sgml we have
one_whitespace<programlisting>
two_whitespace<programlisting>
three_whitespace<programlisting>
four_whitespace<programlisting>
i think you need zero whitespace for tag <programlisting>. like
<programlisting>
</programlisting>
https://tdg.docbook.org/tdg/4.5/programlisting
says whitespaces are significant.
<<>>
As noted in <xref linkend="json-type-mapping-table"/>, JSON has a null value
that does not get exposed at the SQL level.
<<>>
i feel like this sentence is weird. since these two are different things.
I think some of the query and query output can be combined into one
<programlisting>.
no need one <programlisting> for the query, one <screen> for the output.
see example in doc/src/sgml/datatype.sgml.
<programlisting>
SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
interval
---------------------------------------
3 years 3 mons 700 days 133:17:36.789
</programlisting>
Attachments:
image.pngimage/png; name=image.pngDownload
�PNG
IHDR � ��P� sBIT|d� IDATx^���U��?�%��ED@@T�(*v�h,Q�I�%U�����&7Qc��&�\����c5�&V{A)�(Ho���u���i�Lcf����<�����k�{�}F��|[}+( @� @� @� @� @� @� @�dZ��J-� @� @� @� @� @� @� �� @� @� @� @� @� @� @�@� ���\ @� @� @� @� @� @� \� @� @� @� @� @� @� @� %& \Xb�r @� @� @� @� @� @� @��p�{� @� @� @� @� @� @� ���pa�]p�%@� @� @� @� @� @� @� ��� @� @� @� @� @� @� Pb��%v�-� @� @� @� @� @� @� � @� @� @� @� @� @� @�@� ���\ @� @� @� @� @� @� \� @� @� @� @� @� @� @� %& \Xb�r @� @� @� @� @� @� @��p�{� @� @� @� @� @� @� ���pa�]p�%@� @� @� @� @� @� @� ��� @� @� @� @� @� @� Pb��%v�-� @� @� @� @� @� @� � @� @� @� @� @� @� @�@� ���\ @� @� @� @� @� @� \� @� @� @� @� @� @� @� %& \Xb�r @� @� @� @� @� @� @��p�{� @� @� @� @� @� @� ���pa�]p�%@� @� @� @� @� @� @� ��� @� @� @� @� @� @� Pb��%v�-� @� @� @� @� @� @� � @� @� @� @� @� @� @�@� ���\ @� @� @� @� @� @� \� @� @� @� @� @� @� @� %& \Xb�r @� @� @� @� @� @� @��p�{� @� @� @� @� @� @� ���pa�]p�%@� @� @� @� @� @� @� ��� @� @� @� @� @� @� Pb��%v�-� @� @� @� @� @� @� � @� @� @� @� @� @� @�@� ���\ @� @� @� @� @� @� \� @� @� @� @� @� @� @� %& \Xb�r @� @� @� @� @� @� @�@k @�@���o�!�\��|�-�����Y%��o�������w��}�������-��.{�}aF���w_�{�>\w��u��� �}W�� @� @� @� @� @� \@������ �b�m�^��0�zgQX�jmX�nC���M���6apx��}�b��?43�5�7����9������U1���V!t��.� ���Y0�O��u��Q @� @� @� @� @� @�@���E�9�@�Xz}�wu�l����W�_�qj`��Ma��3�^
OO�6l�T�!�c������O�O���c��m��y��p���������wKO-�������{���Z�*%y�X�-���
�����W��������w��on}tr��w�m��w?Y��M�����s���G��zw����@U��Nx-<�����9��>�j��������c��.���CB�6[W�� @� @� @� @� @� @`��p���@X���f�����S����=�Y���9��tU���?�O�<��p�!�t\c�4�����?�����sM��n���)������q
V{���%'\��Wo-Xv��[��;2���k��� �����X?���oB���)!�������)�3_����p���SV/��B�`? @� @� @� @� @� 4W�V�u��M�@�KW��~�&���������-��/cfO{��u��~�����jr������q~�������B��i�5h��}��9�317X���Y�!<:yf���"��_�>q����O**X�?���0}&|��w��+�G�>
�'@� @� @� @� @� @�@c
�\����E�R�����C��u��g��S���)����x'|�w�
+V���{
Q��������;�N���e�������g��^;�����m�#��nM�R��g^y�)L�NsX�v}�������tr��qp�l�U�G�n3�|�1���>r@�����������U:�q#v���
B�*����~t���)�������w�v��w���s���]��qcX�|uX��������
��g_y+�������c�_������ @� @� @� @� @� �- \�V �B�����o8�����j�o�-th�&'\8j�A�+���^a�)X�K��z��a�3��~('u��
���;�kS������1�����O�c\�[�6���\������}L{�-/0z��9��Y�,3�^������������~�W-�u��JF �c��]^�s���y�)#���mZo]�G
A?�����B��^��z��w��.93l��S�cx� @� @� @� @� @� �*��Tn�Z�@���]�c���Ti>W^pBh��U���c��k��_\tR����y�n?���9�Sg������k�~�:o�0�����9o��u��u�q��[��uYu�-�+�0Y�2Fq;������]c&�Z� ���j������9�4�N��L�� 0{�����b���#�
�^vN��9��`abH��:�����1��v�|w�������+b � @� @� @� @� @� �Y@���� �4��\�3���:6���5h����n���0rX���w�^;��w�s�#��(j�����q�����+���G���7��i|��O:0��������?�&0z�]rv|p�k�X�^�'��6l�W[o�*5\��j��T������ k��.��S
�9kTH�_���c���:?r���C�3�oc_,v8� @� @� @� @� @� @�$�����`�H�_`�������X��2��cH�[��uU<vDn���sl�7��%��w=U~����im�#\s�=w�>�`��������nnKi��}���u��pyxy��:�5?����)��4��C��^�{^�8r�����_�������m���]������u�u0 @� @� @� @� @� -U�uK]�u(u��pa�Z�����~�s��2��_�>th��Q��q���,����������V�5q>��V��6|��������W�-��O�:.����C��>�������]N�p�s�f�����Z���f�������qS7�9��o/
�b�yE�w��j��9����v����M�B�j��,^�~����-���k�u���c�c�0x����~�>�����y����}4=�����K�{��a��msNV���v!�w�s9�����j�)����Mc}����Y����:�~�����������KV�)3�
���������^]� {�n�= ����+o.�z��&���m�aP�a���6�39u4}���!�����c�6�w\���~N�R!�>��z|.���$,�c����m���C�0�O��[��2����4����
��k����y������mCz��w5��.]���}�����3{M�'����3�����)s�l��K�?_���x @� @� @� @� @� [F@�p��;+�X�?��/���J��Z�~C��ox`b.l�����o�-3�5�6�O���z��L@��;,��/c��;g���:�]t�A
��I3��/�������<x���:����5���;���$v6�n��Z
����
z����3�j�N{��a��M��I����T��R����3����� 1�TS��j�������)h���t�E�6��������
�t/z�����p����;��=���>��q~��p�������}����C��������V
r� �����\K+&�'���gN��<��By�Q��M���:���2��������Y�c�g�y�S��'Xm�����g����c��������2����� ��?���������o�A}�9���\�.Em���>_{ifyx=�N1@�����r����_���Q��CH��T)xy�������}m|������}�����OY���-]���z�����z7l����P�V������y�>�V���������:�
�ua���}p��/���Rg���O���G�>v���c�0��x��������?���}��n1 {���QyP��l�0gu�� @� @� @� @� @�@���o�7���YKV�.�.u���/�7U����i���v�j���>wa��{�-?�>�����^�}�(��*u=J��C����}%<����K�����F
���ln�jQ�=6^�_��xy�n��U������*��Lx-��#�Tc�-�n�b�����.d�V
y��cb����W�y
����V������PUs}g�����w<�r������b�b+�.�~LxsA�����R�r|$�`��G�_�����t�Q�.����M��B��]�<,'\��
>�����`!��;���CN��]��:���R q��2_�> \�����`��:SP��14w[��T��x�cS�=OO�|bT8���k:���S��Ja����?36���:����?��lx���B��[H�p���~��������#
9,g��J�AS������k��6TXvp�/~s���D�����Z|�5��nu~^q���Y����Dz���n���=?�l��jc? @� @� @� @� @�@��*u �'�R������%v-L�-��+��1���NL���`yG��m�����=nS����t��Ue������R�����)��T���K���.vT+[���U��J������������a��-��>��*��)0���u�"X56����b�x�f�C
h������N}�=�R���W�Qc�-�$�K/��U�n���U
���x�Zt�����g����c@����c`��]v=3�������e�n�_��]E_��b��_��C{��<=/���U�
����]�1�lN������S������`a�_����fA���US���-0uH��z��]Oe����9�=7e��������n����B�����%++{��m���K�����
�{#=wS7��j�!}���� @� @� @� @� P�:��E���H�����B#�-;e��9���_�F���~&���e���V!p�A(�sS�N����Y��|��u�?a������J���Q�����a$/7������ ��*?E�����FU*���C��U
��U
���?�
��`�����=O��y-P�|�s�L�1�[���+�}�L��?!��[e�:h�p����ki�:�}��w��b3�4rXH���'�������`{��W��>_��:�]��{�_���0p��5��1�v�?Cv084|����#�
��zv�6~�),Y�~xi�;a���B
y���S�2���R����W�������w�1<�����ik:���s���2.LsAf�V<��=��{;��R@4uLyqF�3v�Kk,����������|<'t^�y�[1]���0���y�p�Q���{�,X�:�>>eV����B�gY�N���s��j g�cq�/����������{�#��|��m�:,��������2��o�.����O�������bg��@m����I��'�������1=_��'_��v����M5^�T)�|�A����;���k������K��1����/����d��������3������k�|2L���)��� ���[6p��Mo�UZ�+s���o����,0z��{V�$� @� @� @� @� @� JP@��/�%�|��i)�S��[����X)L�]#(�R��������<_�R
N�qD������#cv}z�~aL"�zgq��p8b�A����2G�������)��l���:�Z)p�])�Sz�i��b���)�3���G�zT����[����Gd��:�eo�b�^q�C!�*�*~����a��;T`���k���d\�ca
�J�����p����pL������
����~H8���B
�U��Z���� �����&�:7f[�zm&�������CQe��}`�;��~�r��)$�|�&������+�����?���o)H���
�6|" ��������^��7���>fxu������/�����:�������pl
������9��dM�)��\8(� �-^�:v=|&���$3Lz���#��{�T���mH�'�n�� IDAT��-W_g=< ��CgcH�������}�a���*ua�#5?�������t��������pv����zf�N;t�����+�z=����?�s�wj��~��gg~���?� bVV��.J__9��LW�'_��?�( @� @� @� @� @� e���A�@�X�rs������p���a�ks3���������
�b��Q�B�\���B���������������V��";5�v�)�p��,���)�~p�1����!/\���u�Q9�L!����H��{)M(uM�>�P���KV����]v����ng���/�����OYe�0{�������/���e��{S����s2���Ja�������V���)9KK�'{���=���9,�>��o8���`ae���]&v� ��*��ms����N�%����j��n��^{o���gM�-��c���/�\cX�W���W_:%�����B{),Z]�P��y6g�OYi�0{���s��O��nS�9}���~���e����M1�j`�����|��`a�X)L��K� ��{����9����V����e��5|�����������R'��_pbH[�+uU��������Bvg��A��`ae�OS�=Uh���1l#@� @� @� @� @� hy��-��Z�������(R�h���
��XW�86,L��y��w?��!�/��GG}���}x���nMM�3u������L-u J�������dyw�t�K>9�Eu���)�saZ�>�����#�����bw�����������'�)�ZH�w-L����bg���J��:�UuL
ewFLA������������k�}.g���ce+[���������Z���'^�y�O�����KY������5F��hE�izv���N���_�
�_o��<S�����B�6�5�N���~�b{���W{���}1���.1������������_ON-*��>W7��K��_��L���Vz.`;-��eZ����Tl��u��Xh�������zy��5�%�w����W
$+ @� @� @� @� @� u.���� 4A��)���/�4��_�n������~��P�/��4fC�~��/�is�����t`C����/�>/������������#w��_s�o�J�k�Z!�y�-����k+����8l��B�vm�O�:�=����N�.=bh����N��S
A����na���Rj���9� [�j�� XL�y�^9�?=mN�]e�x?<;%f��G�Wt`�SG/f���w�����=8�����F��W�_��j�A���c���J!S ;��y��*�H�����uLy�SR���Z��_�[����}1v"���b(����< 9gW��W�n���{�����{��}��E��T�w�a�j'�E @� @� @� @� @� UVc3��,���6�[�uZ��w�/\u[�?v1lJ�~��4gJ��x`h��J:��k�����`y7�d��s�nJ<
:���G��cQ����'���Z uS;"\��� 5�g�Z�4L��8z�]�>Q=�2`�n9�������}�G&�v��o���j����s:B���Ko�S�Z&����e6u�;f��+����{��m���]���5�6�����
Y��o[a��/��{�bv/�����!��S���J]����J�\��gpe�V�-=���=�X@1���;�s|]�M�n��2~m�p��W��5=�����o��9�|d�9]��� @� @� @� @� @� �|��������� �F�e������ W��X&�K��a�v�C��]B�n�����t���u!����� <���a�3��
���a��p���c�n����2��N�����/�]�y���c8��}�;��^���O��./�{gzt�[���&�H�����g���'�*?�]OL
9p����;4�,�&[��v d���2+�����yeZc&������6`��v��m���jve����d�<`�l�]#��vD+d:mc��v�ge�FU�{����;�v��m�^�gR��J>t����o2�Z��.v���6[o��W�A�9������9Tsx��=��i�J_?d����� |
V�C��gq~����9�F����0���;,���e����w����=�����5���b�1�������B��z�Va���?�:wh���Z��L��B��
�}�-���3��=��������.��m����#w��?�r��C� @� @� @� @� ��t.lz�����@
��(����??:�{�����v�����k�N�&�W�W�'m;���2��w����1<�]���e7<V�YW/��� ���/��=9]z���/��`LC�31|y�cS�OsBt5���R
=���;g�����_��Sk.�2����s)���K3��ypbn�����I�m�r����y� w���V�E�z1��k^V�9{�ky���w������m+��*�T�
���Nv���9(^��+=|mVX�l���WL�`~mj��]+<'g�]X�P����}H��Nz�T���`��E�����^a����mS���_@�V�[��6�G��yA�
7�_���z�����=a��y-^�~��o�
_��_9��*'� @� @� @� @� @� y-�7�]j��-?;�����6���'����c�����G=f}�� ����a���;2��!��/:9����>NQ��c�[�O����O������b����E����YVs�/
��7!\x�����&���9:�����c&�R ������|gq�K�Z���!�B����9bF�8�l��"F�p��ya������9���].���w���Y���������5d��d��*�R��g��}��cSG�����*����-�����C~w�B��V���]U�7U���@U�������.�Q[�}���#���K�=O����'_����1������<��d����A� @� @� @� @� P?���u���(4�o�yX��R���~j�����Z>����������4��U,]Y~�K�=&t���Q��TO�����?7=�2gA�ox`b8v��ap��9��*4|g���T��~���C~����+�������FT�����p�3S��W�
]*�OS�0�v��wX��li��U����/��v�����f����k�g�R��R���_��x��Z}�v}�!���X
���:uh[�C>�G������0f������e:�G��m���3n���+�_[��S�������B��kU��n����>kE|=9uv�6{~|6, s�[���1���
�]����!+oK����?��wL?g�g���������W��H���O��F�>>j����=*]�� @� @� @� @� @� ��p��� �
��o��#��������;"��xE���s��r��~6���E1��?�<<����<m����43������O?l�p����M�$���9G��~vK�������M����:.\w��9���Z5��4����z7L��V�'�����!;���w
��|�M���f�S���y��z���Ut8�p`������{��=;=��mj:�%��a�2��h����oW�pU]�-�zn��S���b 3�S�����!������}P�������v[��VV�����]:O���6�#p}����������sBz5V�+�+e]����w����c�
���t�/������G�>:9�5j�A���G�����"@� @� @� @� @� �/ ��/�{2
P�cSv'��`�i����>�s�O>y�>�q�L�����\Y���K���5����Iv���:zx�T��|7�����m�����Js0�RsL�����n�@^��4��������r�YSW����:���_7d�+�S8�S��9_�[��=6V ������;�[>\��ko�����t�Yz"�;�5T���0g��m[��X�`��������~��/dC�4���Ki�4|�On�O�U!��}�v1���lh�������kK�������bx�O>>3zD�VE�����$|��7�_����v��zs0 @� @� @� @� @�@����e\G� P��:�
����KV���d��z?OU������Bv}z�~���bc��7=�x?s��������u��X�n��\�!�y�-/?�5w>����R'�Tm�+Y��L��J���h��C/�/�����E��`�.��=4����;�-�N��������5b�~�����m�������;T(�c.���p�3���$�/��I������C�N�7������xKW~��J���{����m^U
4���i�b���:����kh���gy����A�1��]��#���}���{^y�G�1����P�B����c�� ���0>r����=�!}{�~���MWvN���2��z5��]����WN;8���a������3z��w*0�.�i��yz��g�
>6 @� @� @� @� @� JU��o����u PA�[��9���-*���
���h?���9�~|�^���R���y����_�/��a��_�V�}��8i�n���[���� )��O��?�,?������o~$\���3���U[��|�R��7Nk�����0`�n!����=���o�3���������9����R�\f��8\�_eW�����pB�sP���j;����}�%���
.l�s���HV�\��Uk���K�_O��v��GH�QY=5uv8����<v� b�� ��������w���u1��f��L��6�|����*�G��;����,�wYV`�6������i����c�����~kI�����T�w���#w�|������lx���9sM�k/���p�OlI��Z @� @� @� @� @� � ���:� �.������
���c�����)UV��G����tU��~cit�����}@��e�c�g����a�v�k�)b
������J���}as@v��wr��[��*���E��u�!���:�^{�ES�����"��.1 �Bb�5���XC�9?�8w������k���.{���c�?g�����S�������o�y��C�X��Un��U�S%/���;y]���A���_��9�k{���n�m��?!�T{��'���-*X���<���n����_���W����\�:���{�uk8 @� @� @� @� @�@.l���lI�Ks�1�����z}����a�{��6n��,L]��w�Qa����l��O����!�����[�g�=���m]\����.�����-^�~��Q}�����.|���CY����f�Lw��!u�+�R�w���e��{�~!u.,��6BW�V�t�����W���|���
Y;���3��"��}pv7����q#r���3�?��Z����#�r���{�����E�g�;��>&0m���>/������5�o���Sf�[�~Muc
��}oY���q�������c��F
����9�����k/9#t��!g�cb�hE� @� @� @� @� @ �[��(�4Y������!7�R�_����{Cv�,u�����l�`a��mbe�:�w{C$�����[}Rn��z����?vX��S�����Df[�vm��W����W�Y������&�l�pW��h���{i�����nSL>����������:��]����
�&�����H;�]�B*z����;�0DX�jM��`I!������~9�?3�C��)b�]v������r:K1T&��:����n|���z��z;�+s��=5�N?l�Z}�Lz�v��t-�+y������o��s�����Ro�u����m�{PqA���1�[rS]s{������_�;��7U^�"@� @� @� @� @� hd���F#���h��>3=gr)�TL��bV�`����a�����5|p��g�����.����K���3�2D���_ukx)+����:2�3���E�'�H|��a��g_ ��nyeu�S�G�5tl�&�*�RX1�R7��XE����}4�[_U������P��7�/��is2�f�����}�������%+VW��W���U�!u7�������KW
�����}n������2��@������'��w����sV�UgT�k{� tv���3�����S1���J����������O������'Bz>��R���u\�0�:�
g���VC?������Z��c&��s����X���Ls,��Y�!���+��G�]�1M��%�Clv����b�
��Ts����6`�����Kds�'�� @� @� @� @� ��t.l�W�� 4�@�Zx�#�rF?��.g)VLM������3�������
��_8!�n�������9Ts�g����G�6���a�I�l�WL����B���*�.[��j1���}�����`�����}n������u�&�\�zmQf7<0���k�����3���X�x��p���g`!��1p�������>VtH��s�}RG�������pc���c�K��Z/�7bhH��������\���u�����W��Jv]x����c�Z����=1evQ���S�4�FW��6>3O>hX�����\X��+j��s�V��b�������������.�g���y�����52O @� @� @� @� @� ��6�S�<�I�@= ��tU��hy�G[�|u��5�
k�m(�[������v�w�_���C���0�������a��j��C�_���aeV�������������
�xJ�����p���7�������{B2kj�\���6`�n��'���y���E��)�w^h������d�T
d��o�t�,�������:��Cv(_J�8v��9K�.�T�A���+~'L���+l��7����Va{Cl����K����K
>�_��R��6u���6����ww=U��
:���14���|7��z�������R:~e����e�R����[���w���
c���%�7<0!7P<bh���:v ���������Ck����FK�������
��n�H� �w��9����������K����|?�SjMs��O���i��x}K��6l��g������9/������F� @� @� @� @� PB
��)!@K%���,XN����G^|}^(��]
e����U�}��Q������OO�� �|]����_��Q���_��� FY��/.:)�.N
U�sZ���:QRP���f�NW5��j=ns�ge�����N����������9�����86��H!�\?&<6yVQ�kI;����k|����S���������kp^���'��KVV;V�`�~���zU;h
/����=�=���������U��_�s+��j[��\!Hv����{��Nt���U����I#wC����n
���\�];_�� |9�
�������3���]��.����R(��J�9i�g^��0~�9����%����V��<4o��._��]5�S������9#\�������H��/��������a����w���KW�?���+o�
8Vy@=����>�]����_������0x���_�e~^hn�%�w7E���DH��b~�{���+�����ln��K� @!?;� IDAT� @� @� @� P�-c�o\# �R���ON�|���!�[��k�m�.�SM����.�ef��.�� B
0<6yfH����c��c�B^���p����7���/����� �E��3,�����N���+�_��S.:��J��Z�Nki����@������\�Y����p��sT��U�U�r���?p�p����uR�����9��F��/���)l�j��*v��;a>��k[Y�,uR����'�|���c
f^u���1S�.]�����3����������?�9\p������7e��')s���C���*��z�!��]CV�m�e�3�����R@��1<��i��5(���5dN��vN������k��J�^��G�����������-a�{���M�n
y���A���}���6��x�.���f�[��� <9uvH�`S}����=
}� v����Q8���������y���������Y��������g�11P�������C��o�}���IV������ �����s�RSg���y
����k��93c7���xm�����!��-0�v��%�����^]:��T����c�s2=s�����/�.�G�3(��W���m�=6��%!u��i��ay^��Q{
i���9����|'^��!�t?����e�����)�6`��}����K��]f�]��6���f��X�:.�C�w��OY��~�'�?��p`���+c�z�����Sf�;���,p;2�����������.������}=j��L��=���?�gaY����Y��{�~%�����J?�
�$7ss$@� @� @� @� @� h���q5*�&#�~q?L�W����_�h���4�1����_�b�������
���~Kc��>j���U�~9?�2�O�7�^~�.��}s�gU0��� �d�M������������;�ewiJ!����M�Phe]�R��g�$L���Rv��}H��'^�]�wt�A�
�
��� �e���3&uIK_��y
�������)�������\�������^������8�k�|*��*�qR/�R����������U�0�{�����sg~-)>u�L�b}����.�4v����2{=O����R�5� ��{N\�����e~8.�����X�>��=��Ov�[�<�c���O��j?�����k������.<1����2]S�9u�-��[����\�bX��8�B+]��|�����~���������Z_�!� 1��B�e5=v����i/9u�����g��1�����>��A��S]o���|����m�N�|�U
�����)t��5�U
~���*{�6 @� @� @� @� @� �hU���l-V�O�^��v��R��k�vjHa�v��R�zv���N����mKoH�����v�s:����%�o.����k�Z��ai��9,�N��^Uv����)@q�%g���[g�x?�b@�ni=c�����5Gz����e�������-�����z\{�a�x=R q��
0��m����<=�vUUe�����N'�Nl}{u��%kS�u�n��'3!�B����#����{�[�/�tr����@W:_
W�.��8������2��C�n����:c��;�?}�]��c�5_?�^��KW� ��=p��gf���We���}���C�vl�&��j�O��W�P��?<t(����SW��������O�x�WrJ< =���Q���OS0n�+�����o��]�w��� uM]{�
&�k/>#�V @� @� @� @� @� �t.t/ha�zw
�����X�p���o�g�� ��XM��[#��'<,�����Uq��/�zP��uc�;��.e�=���6����sJ.)��*��+1D������:���G�����������>8xn��� a���2����b;��=�i1�Uv���R�#�R��5�7�/���y0u�K�1S�������<�B����*�K��3G�>1j��BSe�k�m�;�VTy\}���������/�;����e����C8����9�����T��Ka���7w,fn)X���}�����q/�G&�Q�;_�x���]8l���I�� �Z�����W�����p�����%++=4�+����1���|����&�|��czc�������s�?���+��f�W��W!��?���o��#�������,�b�_�zM��t��;�t��)�Kofu��3}�~z�~�����T�����Q��������K���j�3sG���1�a���yO����5��+�a�[.;;\w�s�����t
���^�+���[����^V)t�B��V}��LS����w����;� �|rjx��9a��e5������g
9���5�o @� @� @� @� @� �����X��l+&PZ��o�`��E����Z�v} �2R'���op��5�1c����+o������� ]�hw��|��a��M��aB
D4�j.�lL��o������.�c��u6�m��
��n����=��R���,�s��V��z/,Z�*?����&v�����V�.���8��%����1t������e����_�0(SS��!+up���3�^��0�z��L�}|�@c�K]K;�{�>*]��^Y����J�X�C|��5x��������y5#^���-�������6d�@��f�n�2����=��W��W���?�������x���+�������g���K\���!�Pw������������c(wyy��}�6��}c�1u���@���z�?]L�3?�"�d��
��6�u��mH������i������M��y?�'���*>��m�!��1�l3$>� @� @� @� @� @� ������ @�@I��S@k�//(��[$ @� @� @� @� @� �)��4�m� @� @� @� @� @� @� @�t�K��[9 @� @� @� @� @� @� ���pa�^x�&@� @� @� @� @� @� @� ��.,�ko� @� @� @� @� @� @� P���%z�-� @� @� @� @� @� @� JW@��t��� @� @� @� @� @� @� @�@�
����l @� @� @� @� @� @� (]�����VN� @� @� @� @� @� @� %* \X��� @� @� @� @� @� @� @�t�K��[9 @� @� @� @� @� @� ���V�*��[6 @� @� @� @� @� @� (I�K��[4 @� @� @� @� @� @� ���pa)_}k'@� @� @� @� @� @� @� ��.,��n� @� @� @� @� @� @� P����|��� @� @� @� @� @� @� JR@��$/�E @� @� @� @� @� @� @�@)����v @� @� @� @� @� @� (I�����M� @� @� @� @� @� @� �, \X�W�� @� @� @� @� @� @� @�$�K��[4 @� @� @� @� @� @� ���pa)_}k'@� @� @� @� @� @� @� ��.,��n� @� @� @� @� @� @� P����|��� @� @� @� @� @� @� JR@��$/�E @� @� @� @� @� @� @�@)����v @� @� @� @� @� @� (I�����M� @� @� @� @� @� @� �, \X�W�� @� @� @� @� @� @� @�$�K��[4 @� @� @� @� @� @� ���pa)_}k'@� @� @� @� @� @� @� ��.,��n� @� @� @� @� @� @� P����|��� @� @� @� @� @� @� JR@��$/�E @� @� @� @� @� @� @�@)����v @� @� @� @� @� @� (I�����M� @� @� @� @� @� @� �, \X�W�� @� @� @� @� @� @� @�$�K��[4 @� @� @� @� @� @� ���pa)_}k'@� @� @� @� @� @� @� ��.,��n� @� @� @� @� @� @� P����|��� @� @� @� @� @� @� JR@��$/�E @� @� @� @� @� @� @�@)����v @� @� @� @� @� @� (I�����M� @� @� ��X�lY���+����v���}����p��G�����aNjT @� @� @� @� ����&s 4��_~9l��V��C=�9L� @� (��^5jT���2KE��W^ �����w�s�=�{���v����[o�����q���
�uh��\sM��[����_�Z
������^�z�;l������L���[�>-G�i���3�(o�;�iL�,��@!�!���.���O<Q�L9��S���l� @� @� h ��-�*Z��;����0��_�����F� u�4iR<xp���Ox���2�c ��o�1t��=������7���
�����/�N�:��N;-����vu���������'@�&�������:+��5+�k�Z�>�/���p�Ee:~����i���W�X;����s�����������|F5�kbF���gCS�
�o���w��� @� @� h���M����zX�|y�����������ml @�1~����+���1OY�\������m�9sf�?~���\a,4�U�Ve~�;��Zr�5.]�4L�<9\{��-y�-fmS�N����=�X�kJ������!��w�uWA��8�4#?w4��e�Z���1c�K/��Y���C�6���\v�e���g�y&|�3�)x�w�ygH]LV�\~���u��|l��x���f~vjjH�gT����M�z���}Z��
����7-������oN�U���\��y @� @� H���%"p�M7���W��6�r�� Jd��I�@KHA�o|�[4\X���iS���m��r)��
�?>���?l��B���w��p�
�{��pa���s�������M�3�n~�&@��)�VV�\rI���w��~����V�����@S�0������>�j�r^'P��
-��7��or�����
��@����9�����u1g @� @� ���y�� �+P��p���������������Wh.���6l[o���r�s����3�:�_���p�]6'.@��,`����/~����o;������_�b�_P ���{�o���!�c�=6r�!% d�6����@����={v�:��:O���Ng�qF�4iR�g��?$P���/^^x��&�*�QM����-.����/A�O���/t2��B��G�@s(��95�5������f� @� @� hn������/�ZL�<9L�81sd���=z�1c���o�9\}���C���!h|���e�bWQ�v�y����O{�ht�b��F�`=����O�K5�����)S�5�K/�4�/E���Q�W�� 4-���O�g��u�\�������Z�qJe��z(l���I.�gT��,&E`�x6l�K��h����i���4�@C<'q����X�� @� @� 4a�VMxn�F�@= �u-L��x�����O���l��p�����YC� ��Hx���=I
�7�9�0E/��@�n2}��Z�
)p�M7�>��!Oal @���][>��g�>�n'���E���R����"M����
���*E��H��
bAX�(*\P�^�Z EQT�
X(� �����o��w�<�d��d��s���Y�d�L>I&�d�3/�������z���y���83 � 0.�7�K�� ���
,���}����|#� � � �
������\@���>�l��k���y�����Yd7���Oo%p����e��~{���k���^�<�Q�2s��5�x�3�.��b�=�\s����Zo�����/�{��^��������*f���2��~��W6[o��9��S��y��j�O�Ss�����[�,���F��Zh�����n�����*�����<���4�,��Yl����+�����3�0>�`��>�����y/��R������<�)�F�V_}u��W��5(k�[����ws�����>��n��E]���9�y��~�m���S?jtL�u�M6)�U>��������[����fy��8��<T���z�;C��:�:�8� �����jQ��Z|����k�+���r^��F��^��w�1c�����5R�QG����Mm�_3��vZ��V��(�\^��[������l��F.�:����c��������������{��r{������O������G�I�AUi��j�<�|���L IDAT�E^U������f���re���h��Q?tm���x��)�)���?����w�u���G?j^���u�\�u����4��_����L
z?��]���J+��R�����$�����|��_M
2R����o~��EV5��N;�d���'�{���.kT~������[���e�6���=����C�q��8��7�����q����E]����6��<� Opn*������_�z�����������~v1��_��T~��\���&uy�o��������Fg����vt�����o{t{�����?���eU��F,�8��s��;��������t����>e�����������w�m���[h�k�c�V0�{���a�f�����u�}��F�������G��F� �m���O�}P��O~���8����m~���s�����|�sHv))�aX�tY_K��Q���T_�Q��g�:����Y�[����vt
�^�z��z�{�+_i>�����o�9z*S����k����.w������������ApJ�Yv��7�Kl}~� '�P�-����������E/zQ����o|c1��kay���?��?����+:�E�tu�6�:���������^�!W��z��|����]z�
7t���+��j+W����M�q��l��;J9���\��_V[m�#����3���G�M���/xAt�.����r�!����������W=����kV��)���������~t�bU��r�/��o��Q=I�9ap�[l1����
����>��rM�:T��|�{�M7���Twn��)�V�{TWe~l�o��s�I'�wi�O�ZV}Z��gp]�U������uu�������|�+uY(~;����e�9���e��I�
���g������/�����|�:��������G��W�3���W��0m��P�W��������n���+5����~�S���q#�f��s��e��~�������:��~����*��|��H�y�e� ���i�*��}�������{������������u�Y�}[��O~R��b�4�g�Nt�~n�w`����u,T�}��_���z'������w6z��&�V�.�m���W}K�`�_��_��V��[�Yw����9���/K�=!%��g?+�y��_<c�Q�9u���O����< � � � � �Y���%!��|,��/~QC���1L������f?�l#�$����!�X�_o���c��~IZol��/�|��$mk����F����f�
l�0g���i6h3�-��6��F���
�k�����b�SO=u`*�#b�:�_��`��y+O����Z�:m@���/,/:�o���c�5�p�m0��6h���6L��l�����`�yN����.s�Uw.�X��}��\�
�,��O�#������6d,���{��?��n=6�h`���Q����X������O���\���>��O�E_��W��O��q{�~�8�F���>��"�a9<+�v�M7�4x��^������@��m[M1�[��#n������m�[�~�P`���|�v�]�hJ�aKm�t�m���m�[������'>1PYl�j�m/l��FSm8W��u�����}������a�lP����q�d��-/��m�0w�(�s�>�b)�y;�I,�iad8�?��s^;�����U>�����AX��
������x`;h<t��k_��#6��lc���jb��A4�����=�L���/~���e������u���Y+/M�i8��Z������i���R������9��l�����#����c��g�m����z�=0v���F��y��� 9��p�9\�2�{�����:���Dg������lC���lp��_����YXf���u�������
l�/���?�:�G�7��;%���g�Y�k���?s������u����
�O�v���������}�� ��v��&{n��Z�?D�������'�t�m���}�f�mj��]��
���Sl����-�r;L��Gj��l.�zgJ�M���A��^���{T�e~�I��6h?�=������o4�c�]�+U.��l�����~���>T,c������vD0�<��=1���T�n�����"�����>���E�=^�k:����[o8M��<�;��d;%�Q����fw����mg33�M)R����S����>=g�(���L��~{���n[3��Tt����
6�1O��M���{b����o*uy�]�m����l�al'������/�~��w���\�vW��F}��k��c�����[n�e�9����a����p�{DX-����g�u��W��U1����H9�9��y��%e/<�l'3���SW�?���I��� � � � � Yfvsl���@`���3J�MQ#&���W/��Dq#�5�.|�5�����(��]R�kT�X��u�����F-T��f��^+m�r��zfW�� @���w]mK��k�����w��U�?J����T#!*i���^�U/���j���o�G�������*o���6>r���C��W���5��Av�eR����X��^���zO�z��Z�b�e������6rv�}����U����C�O��]���j�����th�����e�~��]�F�S��;����hVJ�1���_I�/�0M����@�:�5�F�Q��!����HM�6\5�K���+4r�F�+'�Nf<����"�b�~�7rT�{���hnUI=~k�M]�)W#B��ZT���U�A��5�Q����?����g�n�K9A�b����5*�FF��z�������u�+���H��K�i��s�����2�)�y5j�y���z���?�����[������mc2��Q4:�F��D\��l� ����Y�Z���t~�_�*W��lu�k�H]�:O��tz�{�SlJ��z�����Y���/����~���U�5%yj���{����O}�+��b�v�-����'�U������vU��Z�u�{������4�{���:Va���>(�~�c��������=AI=�k�P�v&�p��*����w��tO���?6D���0&3v.�9�����7��^eq8"bU65r�O���to��P���� �4����t��:�9�{��U��
�.����^������k�^�b�kSu]W�����uN���#p]w�u���^�$�k�&U_�I���K��y���'����2L��Py����������T_��0�N-W>����xVRY�{�o�[w�U�u�kG��z��o�����l@Z�i�G������O*�T����'�^����F"�~jT==w��e�_�8\4��������Q�gz��%y���.G�%��Z^Fu?
��-z�%c=;���rS���D:�����bU�{���l4bS]�u~�����U��n��[�e���?��b�i�y��L���Uz7��X�7��
e���U���U�-?=W9`;:(��Z����[*����������=Xu ����#Q�9V�<]��o�|V��w(���>��^�N:����:7}���,����Q���I�{�I���w=3���=G���E����z��EG��[�3]��V�������J�T�W��A�We�F
��Qu�QS�{��y���I�$u~}��_u�����G=����z^�sI_�m9�9��U�o�K8���Y�4bm�T��.�{a,�#��Gn����i�~��S�������������~P��I�g�/}k�w
�G��H����H��n��FW_����Pu�o�_SW��F}�'o�O�N@�������z�����������_�6U?V}W�rbI����o���*���
\4�z��\Y��w��]�M�}4�{���7�����)�������ks��u!� � � � �@�@�PEV� ������l����i�"�Bg�7��m�Q��7��
E�����j~� d`���j�x�@=��#�Y42�Fk�>�����/eb���m�;���������ht����|�42ayT-����N;����@a/���z�n`�E� �pF��.:�&�7?�� t����p1�F���/����U��m�?�(H����+�u�s�h��i_�N��
?6�����F�
{eV/��d���Q4��ZN�i��?6�tp�=��gq����F�����4�m�^�W=���C����*3��x��k6V������,����<RCt�`b��J�l��������&�������R/���9'�tR������� v-���3z����H6���o��6������� �b^x7���,���?^
7V�Z9��C�����:����e���G@����V���}�)�\�HRA��Z>������FW�
^��
G�+��]�N�����*�����h�P�C�k����+��=���1��QHq��
��h�a��.6@�X���\N�Fl�b>�6@�<[��� a��5l#�Y���=�66���&\z��n4h��]������Wy��p�p$��U� G)��������Z�~��s�����2J�3g8������&t��zR�k{l�x��H�wh��lP��j4&��l�E�4�^U������*�~�
^.�Q��Ui�]wu��N���m#���a���o�)Vfh���g��}�s�=]y�h�U)���m�ro�]w���pze|��g8�\'��kG�&����`����>^�*��E9�Mh$>�,�G����^+)#�~g��=����nf���a;����f;�����(g�(U#M�L�����t�tN���*���Q����U-No�G�����Q��9�����X�����8�U�%uq�T~&9�T���Mf������i��
�-��
����������)]^����Q�`����L�s�
��������l \���9j�g��T6h����)�Tf��C�3r��������)�8�=or�R��yr�E)�]�>����|n;M���Eyj;���~�����v��s:&9���eOy4P��:Z��#�z�F���+�:�4��w��l�����lg��|��Q�bI�u������w������{T����7����ic�
�@ @ @ @ @�[��KB ��T l���3�������PSP���=��y���*����q��F��c��j�M�Q����V�G�y����G���j�����P�RUI
�mO��~���f�~(��������������Z�&����{��r~a;BF1��U<��������
���R�{�����F����]��g?[�k{���Wa~������DlO��w[��_+�S�V`V]R�@�fGQ���{���S���!� ����m?�6�/�=����m����%����5^���qD�����Z>?�h�.�ZU��?7��[��6��~��Y�=>�%5z����+�=��e���C�������q\,�.��������j+x�3���}S��X��WW�m�&�u�\��2����7*��.5H�������q���g�Y���h}v$�h��Bv��b}��bIu��}1���L2�����
|�*���r�����zr������W����zg�:p�g%���a"����/���g�h{j�� ������.���F���1j��<�r��?���Y��U�*'}�����h�nux�0C�U��_�dF6beFS�Ky��;��������F��7wQw��tibe|�AAM�[�`�/;2t4�]���J�*��I�<�=8<�u���Lu����9���e R��B;:M�W5��:5���U���OT�c��w��.���OW�)��)�4�)�����)��y������4{����v���]��+A����Kq,�:\P��x�j]�<�[�wU��~z�T�@��l�:S����S������:����e4����9e��A������(e����R��U�*Z�����M�����L�>�����~.����,,{����::��<P�
u���,�z����(�������~�>����+g�N�?i��v���O�w�� ���ikO~D @ @ @ @ �N�/hI 0
�j����l�e�1������#,�N?��Y��&�������������m�5t�����bY���r=60�l������Yt^�8�� ��
����4�6�0|p��9��S9����zal���v�m��w;rPt^;�����~�~�~�������C=���6�����h{�76X*i���r����{����d�w���������6�p��F��6�����~���E����|����A�obl Ht��������'���6~7{��w1���L]��m�PlB�O~��k�9��y^�����O.&�����m���l��6n;2��A^�U���6�w�S]��jG�+f�gu����
�6�^{�[����:�v��������������v�h���
����}�{����n���Y�w]���[cG,�1+CN����y��I���m�h�H;n�6���`��,��:��m ��y��
Vp�T/9���m�^�>�����/n�K.�����dG�0�q��l��
p,���o���V<J=�m��2,�#W}�����]����>�Wb���������R�m��O����ml�fWcG��]T���H1�������,o�K�_l��Y���[��
�s�oG�.�0;���#0D��A�n��0�o�yt?Q�Zz��K�3�m������o������S`\e�)�����5�q�c�l�qc��r��k��=a�@���1��{���o�Xf��l��������^7�}v]�����O�Yg�u���(���vq�����������,�Jv4Is�E��uOz�+_Y5k����m��g���_����AU���.(V�g:���v�`T.'�����rn�f�mfl0by���=���>���?X5��~P�����,���S�����l����S�w����mz��sm�}���R�~���*�U�vX|sV���2�w'�c�����������U;c���������������2��@ @ @ @ @���_z�]2� ��WKc{�t�����������@#%}�����*Wo{/u�)`N)l�s�����7��6m��Co�7p�
�u�V��>�r�-�Y�W���b��^���<���1�o�Z��)6�u�]���7�-/�6�
�Ym���j������9�;"� ����a��u�S��K�Gm7��-�,��R��)�#�!���V�dG�4���o�E����6t�:�����?�Z
��R��F���{�(�Y�Qz��w/o����
wX>
0UR���[���D����r���?��H);���v�9�X���_�h�����;���S�-;�g�f���))���@T��6���m�v]�/�|SV[����=���+�6����������|��7�)�UO+����oX���k��Fl53����N;�TL��vD��w]��qo��G���s3%;]���R��*ss�������R�nS�l[�7��s<+�uh�?����Y����_;�[�*��E �������v�\e��\@iGou�t�5�.��W���b�:� ;d�P?���7�|��S���\r��*g�����z(�1�r���m��7��K��S9o��Oe����Q�\6�IS,X��QSy3�MG9����uuNy��>���c��rh���o����.*�0�i9W�?L�}��������^9���m�k��)8���T�E;�^�I�;�����re��.�m�����U�.).�:�:�[{���:��w�m4_N�����iy���=������}=>m��U��M����,��"��A�v5=�{��m���\����������S�V}����k�oJM�na�zX7�Z�0S��U�������)��=�.���8���@ @ @ @ @�����#��7]����+�����FK�Jo{�����N8��2���2�'���U�P7}��9����������h�b
���G|��`�jS�Hw~�!� �j�l�2���|��a�����'?)��h���69c�*��R�����ZI-�����l�K#G?����D���{�li�:��V��R��z����+@����=r�s8B��,���)�c�g��U�s>�P����)��/����(74ZN���n�a�i8�_U�������}o�'\�_�����|�mR# �_iY�B���W^9cu]������ IDAT�4���:o�2��+U�r];j���o5��
� �c��7]]7aY�T�����r���'g}-�:��;�f_�+e�6�En��J��F5:����4�����������N34z���6�t��Eu?���zv,�'�+�Uv��E����������^� /�ku�+���4�W����z�q�����$4zu,�>���H���Qw*�Mwu�,o+�9���*�[���m{����>s�=W�� uz��w�����OA�� ���O]���Y���[��\�H�}���a�G���u���o]�a�[f����X�7�xc������e/{Y��Q�D1�IOK��(�9�Y���I�Q��e�`��i�.�"�3�#���\|��S�8��[k����_^|[H���}'��������'� }���������q=��e����1���
@ @ @ @ �� ���>>���4��o����M���@��H
t�I�P������;�����(+PF
2:� ��!���5���u�k�!u�j������������WW?����/>�)p������-�X���������<�+��}�bU���^[lG
����{�����t�u������y���N���{�$�c�=�XE��F�y�+_����u�
7���{�c6�`�0+����7��c�Gf����Yr��� ���6�3��};��}}�h�)��&|�����`������CU�����y��w���S���6S�-������vm�2S���{���R
Q��>����[o�u�f�i�����0&#�Xd�\��:$�n���t�����~��_t������o�nr��*�>��O����g� �%�Xb�}��@D��I]�9O=o���G)ss���G����r�����9��tO���~�p� U�T'$����yIy��������CRSS9�'�h�w��Ey�}\����p����z��f�m�4���}���S������}~���z����T���G���:��>^MF]��m�cZ��Q^�������lQ�\Q^���>��C���,T��.�w���y�������w�e~������u�Q�����;w����S�oS�6u�U�[���t��Z��MWJ�D�'�t�M�v�/�����K�t�K��yk�I�YrB�u��l�u���X_�u"=�������o�Z��J��S�Vf�d�R�>�b��z��� �,�N-s������N�,J��i����]�2���K�����o�!��~.\i���5���=,��9���o`����o`ag��?�}`JJ� �i]�z&l���~O}FQ~��9e\~l@ @ @ @ �� ���
�����g�F�P���X�!H
��hWm/��L�����5�PO�����������>�����1J@y�6��G?2����*��G?��������n���v��;��s���������S���.��MR���F,w�q�0Y����>X���h��37�0j^���M/��r
9�����{J��6R>��O�����O�9���R�7c5�KM����`��������q�>�W�A��vTG�J�S�8��s��u�����f��r]�(w�y�i��Q�u�g��j��Q���=V���=vZO����>R�g��s^;M�q��=�{��� �p?��or�UV+�Y���nG��w�}]����k��/�[RW����<oR�r��c��[}���6�E^9���0����v�L����z��'�<� ���?�2~��_�M�HW��8\�w={��w|#x�Oa@��^��bW��v��������u�X6����
�c����n'5���C�l�;���s������*�Gu�����S�u=-����3��"
��9-��qNS@�G>�7bk8B��v�m7�Q�|X��*��*���gTgO���#�0*�U�u��Cu���z�<W;�|�=�����j��J��/|�E=(���w��6�$��{]��N�����p�B_'Rg�����m������@����-��}�_��Y���i;va~'Q�N�,��>O���������w�y���V��sZ�4����� ��������u����0B9�I��(�\�w��d��"� � � � �@���K^�������)���~zm�G5W�����SN1��z�V��/}�K���O:�$7��0�[�����H�����q�z6V0�Fd�#l�#�~5�����N0?����F�P�G}��b�-\�A}�GH56R��}=� �p�>XS���������7g���e|���Z������dr�~M�M���6ek��)O�=4:��\�k��!�����j���8)�����L���{8o���-�>�AN9������y�=,G�6`�US�q������6��f���.�(�����<����1�S0������0V
��Zj)7����������9����6&9�/\W�kG�4W\qE7���^hn���"�H�TcY���Na~��o�nr����5z��Ky}�k^��>��Ou :7�qU ��������\oW�]�7�;�����f��+�<�)/���xV����Gi9����}�|�S�r��)) Y��&�lb��L8�z����a���� �����z�v�6� �Y�z��0��k�q�{/���n�.���_5��o^����F�*������=�������~d������n�SW���V������S�j�s�����6�X�b��>^�mk�u�u�m��i��M,�z��b�hZ�l�����~�j�#O����~������}Z�R�����'�x���=�����V�-�dle�F�{�s�SM�#$�=���w#���G�Q=^e{�������.�������3�:}S�9x����-|^�
.�����[lZ�1G?�/�]|�&�pA���g=u���Vp��-+�N��]���cy���������}}et����c�&�.s�P�{������P��s��L���X�e��q_����g���������Y����X����w��!!n�&��������G�6�~�>�m��@ @ @ @ @ � ���,Y��������J����Q��)I8��������
hb���������r�vj0p��������|���}�E�b�&�s�=f�]w-��;��]��)�����&5�j���o�[��_���^K�'5�RG,�
+�C�����*�A��/��qiR�=e_����g?����.)�%��Q���{o7�o�s����Fm?���[���fD����E�����Y
}
���4��}>��]
Gsl����|����C�y�#`h��
���f���.&]�+�DA�J�?�wn�^X���]������-[��[�K��������`�=�p�P=L��)-���.�(���o�nTV��E��0��F���GD��o�������p�h8����$��o3����/}����������9��tM��-oq������s�9�5L�}@#�kDu@�I����FS9�&��������*�P�!5���b��#s%P��E
>Q�4����/6���5l���3�~�Y���;5/�z.����=��5������R����S])|���^��k���2������T_U���{�u���y��2�C�P��p�����{�,����VW�m��g, �72�(E���V�N�4��W\����x�w������VI��Oz���Y��c�rMw]�����0P�@�^�w���A��>�Y�Q���c����F�����U�_���������c������i)�r�w��q�C�3�6��������K���7�F8�7���Y��&ul�#�y��o��l�O]O�|���m���q�c���
@ @ @ @ � ,�oU� � �Z������E/z���>h�����pjL��L�?����b�q���)������*)i�%5b�
,�<m>L5eF��pw�������5����oUZc�5���C�8U��iz�����wM�w��$�{�Num�s��[ou��(�
:QRC�pT��|�w���i^5�������{�5R�Oj�8������[���I��h�V%5 �����\k��������qa���y��������u����v�2�{��k��j�C9��h�c7?��u�P�o�����
L�����U����7]79���N;� �(�u�����s3�te������}�.�����r������K�g��7���k�u���g�����j��n^>������o3F�nZYS9��|��s������?���d� ���m�����H=J��w�������q����Q��c�u�i+�'q���r��<��{�3w�s���f,��]Y�}�Z���������K����t�u�)g����N��tO���XX��&UoMu��s*u_���t�|��}R��+�I�#�qX���o~���g�o�S>����]@~)��WZi%<:��q��etW���~<���I�E��7e�����������;�.�E�����|qZ�Y0ud;ul�#u�L��}�����Cy��>�pb � � � � �,@pa�s 05j��Z(��jd�����n�d-���?���r�)�����m���~U,�F
/\_L�T��6�lc���?��6����~�M7��jt�����n��^{��������t��}M�O�����N,��<�M;���p�;��zf?��C��1���'�i�L=��&�������E���<�q���X�}^����+���QJ^G����U�_�n�i1B���������u4��)��<u�'5�HM~d4�������7�p����z�����H������9��5����{A>o��m����~di��
�Q��������������N��u��^�Q��i���s3������������a��Z_��K��|�xVR��@��[�mf��-5�V�S���2�^ziU�gLWp�F�Q���5�/)����2R����UI����4�O�����O|�����u��$����L��4me�$���X���.��>s�=W(_��0�AkAlI�w�I
(����|�;����f�2����*:kQ�����%�s�~��i���&����u�;�0��?/-�����L��v�?�{�?>�����F#����=��nD{�<�E���B9o��{��,��n{���?���q�.�3����}j{)/����:��~n����8��'}�H��VYk�[_�R������e4=u��u���� ��o����s}������)�� � � � � ��G���~�� c�����b�w��,����[�~�����s��jP��?�)y�p����m���z�(���4��|�+M�$�~�������Xb ���-�PB�]n�����5��O'�|r�e�9s�O���7o��/�5�����/y�K�9�������/�I�]=�x��n^5T>��3]���>���������ox��G������>��#�����g]COy�S�>)��3�����a�2�,�So�w�yg�*�:����6���Qe��[n��j�����W�"eS��'>���>g�cV���.��)C��a���eRF�U��0`�����/e{�s��.��)���ZR���y���0��HX:>��?OWXa�r�*m��E�M
�|�O����FAa�b��{��^V�B�c�����R����<�
i�]�M�����>���a����'��������Q,'U�YI��QF�H�����6����>Q��p>����)�p����w\�F�*?}��F���*�Qx��z�����\����]�M�����$�N�V�O�x���@��=-��B���3o�P?�s�kS:�I��k��;i�9�a����e~��)���������#�t��NAR�K�~�����M�G���{�����U�i'ul���������6��������_������U����5��Da'��0%�6����Met�2"���r|��/�l{R�L�,�r��C}���N�����X�1��X�;���T�I}OP�_=��t�9���Z�������C[{}����6���)�9%��G������@ @ @ @ @`t�G7d
�F
G"L�����3��z!�%�$������M+��r��/������_�)�v��}���������>����J
T��g?;��:[��/}�QcX%������w���O����K�=�1�1o|���tm��s���7��ME0�{����������
VR���;�)���;���|�C��W��g�}v1O���b���{�b�Gy� t�~�l������MR��mo{[1��G]�dV���?�������U��z�������������n�V:��K{S:��] SU�5��L
�������5�\s��})����j������4p^P��Y'�^����w_����%�\R���o�Uk���������D�7q���[ou���/(FN��Q����*�5BM����7�Q�����:R��(s�2l��{�.��}���b8�:���J�g5�������~0L��[������:� G��*X.�c���o��6�i9\�������VE)l,^�Y��^����^�hH?���l��'Qw��2~R�k~,b�`�i
�����kW��O~���^#����X���?�MV=.��<�����n���/�~�5����6��6+�CN�����Q��e����2�|�;c?�j�]�R~�����x�zh1zc�|�����|��7�a���/���>�c��5�E?���#4r��W#:k��:�Fl��7���O~��bs��R�2M�M���TF�e�:�K*��n2W�}S�mOj=�*������>���}?��;��c���8������{�5�\����y�����?WW-�w)�U-N�������35����7�/u"�����u�\�?���z��sG�g��1k�!� � � � �@��8�&.��@��
O~�����dL�N>i5���I#�|���7��sO�*��������W.T������j8��o}+��y����,����y�N���d�2�N��X��w���E��H��{��.&k�����)�2��!'�����b�@��;�~��w�s�2��s��N�������c�)Nu|��F�EU%1�r�)��V,���o����F��<��\���Njl���Zh!��C��c
���m����M
{dK��YA`k�����?��g���?�i ��O:��{�����]7z@W�U�<Dw02Q����5�q��|��F#���'UV(8��j�~��F�V��>�`w��_���uy�Gv5ZSR��s8v\�C�z���Fm
�����<j$>��*��IAt����Q%����})�u~��kUA��2����7��� �)�Q�S��T7���)
����m2�����h�L�6����8� X�Z�2��[n�5���r���G>�~S�jlK���@���_�����>kv��To��.L��y�DGD��?��F�U���s���~v1���k�����e����P_�+w8����i�����b���\��~��a���^]{�z����k�h?����_���}Y������=�4N�Oa@���J������y��z��$��>������I����������r��9�������!C�]������O���F<����O���w�����%,o��V�]J@y���w ~������[���O������;���KW���*����g�[T�>�����v�,C����Ro��k�F����J�F��<���6����Q�����zf�`Lt����yG�JzO��^��$�mW�t�:~���o�����(�S>�Q]lY_'R��>�p�u�1+��Rl��nZ��z�2:<?�,^5*�e�\� �\e�0������dY����v��N�����X����X�N��}���un������w��O��K8��?���u��~R���������#(�>��]|s����{��\vT7�Y�}���z���mjY:�7���?�����W��u�Yf��w7���0�� � � � � �@�E[��� �c��O?��]l$���+�i���s�m����� ������C#�#����,7�����U
����=�9�����}����q�| IDATX��>�(J����5��oW]u�k�� 5�T��Q�Fn<���\P������-R�|R�{5(R/���Y
������.������v� '��rZ�>��#�z�T�jl�yV�;�t5��65T�����;�}�qVc���*�I�����}����I���P��.����i��kD���G����s��K
`R�3���c�����C1��5��5�G<5��J5��U�DmG�Q�j����~�]K�5\z��p�����|��g�[�����%�]��:��(C�����>����kFAu�A�(�
�T���������_�]�jt�sT������j���k����z������W\�>�k�d�}4���b@����ud�)��x�M6i�:N
P���A��Fu.�������_]7
(�A�:?�=��b�����Q�,5�����S5lSY����/�1R�����c��C��}R�^5^�?*�L��k@^���7�5��I���W����>����CI�H�!��A]o�l���f�=���2M�m�xNr�F��������� M�~S��Y�k��T����zk��'=��1t~����u$�k-������k�����IA�*su���D��:�P9�@z5
���������{��52R�g��y������k�;��z^]R#T5@�9�� ��v��Y�Ne���
t���n_��-��{Z�+�����xV���k]p��#=��.��*�����5���s�M7��V]Q��a����V���ZR�����5��:�P�Q��>�������m���]�?�MT7U�^�P%�G�H�����
��s���^�~V9�>P^���s����su�i+�'u���������zFQ���/�S�YHu������U��������F�U�z��k*���Vu9��� H��4���}�T�^e�N�:��)��g�UWu���c������z��(w��rO�}�]��u/�3������I�n�EW�/_��N��~K�X����DN��F#����@=Mo;�wFU����GuT�����8�\U}B�n�|����.������dU�����Y���yS���w�u��'yl���s����M���������K�k��B�I5��RW�M�������9�h������T���������F���]�/�T�U��o%9�0��rlwR��dY��>�o���i��s]�K9~]���w��s�~�:�:��r��TT�B����@�R��'��]U����UuJ}7S�T�����^������=����#�zgl�������w�z��Wz?�w��x�+\��z��o������zO���;�����.���)������|��w��Q��\�
@ @ @ @ Z
�I$�r��y`?�h����`3�/��#������v��p�������P=�ABC��~h�?��[f�ev$E�
;����~�z�ZP�v$�����Y�j��*�k?�
l ��~�KZ�m�:�A0��g���
�~��`?���lC��Y��6``%�S��m�]���S��~D�������f�c_l���������d$w�Ap�6���m0X,o{o����o_�o�>f�z����]�s5���l��u�&����u� ��m�X�?�WH\^��m�T._�>r�W>Cm����}���G�'?�6��
���r����6��]�~��@�q~�`�}�m��|�el�Q1�I6:O��6ph`�����]��m�P�����
�1��6\�]�
�������?�*��>�p����C��6v����}Zc�5���[��{����*7�2�6>�\g�������r�J?�\)����r���� ��
~��mG�-�����2U���������@��\�����o>k�6�h`��.kG��QS��v1�
���.?�6��`��u�����\���i8��Z����Q���T_��:���xVR=���V�����_m��_� �+���z��'*��Reg.��|�~�
g�{����fL��a�ha�.3�2#���������T�Z�1g_vH���y��h���:^�y���,�N����l;Hi��Y��\+)��Z���������{��P�����T���A�� ���������7����|�%=K��������
����?����m����{�8�|�K��5��t�5�����W��aS�J��3��\k��:�t}����L,�������[w�������O�������}lS��]]���Q���p�di;J�ql��9��$=K���f,c�j�i*�p�{��yj3������e���m�����\j�kG�*���Q�T�a�7E7���m���;��u�����o]�EM���).�������A�.���}&����Q���~�zMuq,��
�`����?g���.�������������tn5���
f�[m�o9�9���|��E�ouJ���g�����S��?��������T����@ @ @ @ @ ��z�$!�������v������j^4,�=N��Jv����aq��.���bj$��E�gG0��l�����r$X�Qsv�$<��}`R����:�=�������%�������j_�?���mO���� '(�E��jt�}�����������Y�����ra�m�����:�$����=�5ZS��m���;����:W���@c+�y������I;������l��O
���sV
�b
����v�O���l�3���/Bo;��[��� ;��\����@v�'w��l���a������-����GWg'CY�� ����5��JvD,w������z�=��;���Er�W��S�0L����+S�����+k��jc{���HI]4\�}��:fj��+�^�2]e��>��:Ol/�5�NI����^I��=s�� vtw����u������p����ZWj#��~k��~��7��k]���u��>��V\��N;�4W��{��=�]�������$�T��r]��S^o��B-��:�z��VAt�$5�T j�� !��
�����i{o�Z�3j������|�O��t��
���=���������ZVG
�Q���|��%�����b5�W���m*{��}�n��v��� .��0W}�lS�����i���R��F��xV�uaGr��#=c�.��]������'`R���w�x���s�k��n�����T�����E����)����3�/��hd�Yf�]^�)�{\e����v4��9G�i|��e|�!a�n�6���?��J�[8_�r@��_�U&������=����:��H����]_|q�Cp��wN>�d���?�>��;��@�*��=�)�P��o]}L�C�]�Uz�V�b��h���A���<�)n>�{�;;����#��U�j������5�2_������9:����:�������EO�kp����
���>�!����;!;
�@�gy���~�1�P�[s�5w��������}��k����u�6v���]���[������et�5[7�
Zo�{��yR�������u�g�w����l��uu���&�������F
.���=����qk{���Fj�\����,J��Ky=���~�q�gr^��}���~.�;��eO�cQ��� 9�����}��u��B�7�x���{�=��������3��T���{y�9����$=PY�������
.�zs}s�S�"��r��6r�Q��^y������`����B�?���8�LzU���w=�����~�j:g�@ @ @ @ �U`!��}�IB X���
���Pal�������dl#xc�.�#��
D�m��^��P� m����c�G������U��^]]l#Fc?�M.3ly�����
�s�j���
H��~� c3]���b�r�� 09�+��g� � � ��;��x�����1�h+di@ @ @`����� � � � � P)�h�/�� , ��nc{Sv���wrc{i5�������'�����^9�?�������y���Q6&�Q�� =����U��+�(v}��w�=�E�D �L�ry;��. 0 �Fp�|t@�@ @ �\��lS~ �> � � � � =����� ���w�cn��&��v��2�0���6���|��w�fa 0� \v�en�4���_���n��!@ @ @ @ @ @ @ @ @ @ @`A �pA9��'D����b������j�J��+��b�lLG ���``.���?����Yd�E��}cG@ @ @ @ @ @ @ @ @ @ @ �M������� 0C�1�yL���W_����
O=��b~F�J�cF�b������~��.�p�=���=!� � � � � � � � � � � � r ��-����y����3g�qF��e�]f����?r��[om�_�����v�g<�F�>��Cf��W���!� � � � � � � � � � � ��-�����<,�+���9�������f���7��N:�l��6f���2K-���7o��H����o�%�\b4r�O����9��3xG @ @ @ @ @ @ @ @ @ @ @ @ �K����:^�:8��c�K,a����_��+�t������w_s�G���^�nV~C @ @ @ @ @ @ @ @ @ @ @ @�w����!������n���������{������k����j���~���K�9s���V[����:f�-�4�n���;w�$��6@ @ @ @ @ @ @ @ @ @ @ @ Y`��M#�� � � � � � � � � � � � � � ���,<59%� � � � � � � � � � � � � � �E���,��@ @ @ @ @ @ @ @ @ @ @ @ @ �� �pz�9E @ @ @ @ @ @ @ @ @ @ @ @ @ � ��YY � � � � � � � � � � � � � �#@p��+r� � � � � � � � � � � � � �@��0�@ @ @ @ @ @ @ @ @ @ @ @ @ �G����9V�@ @ @ @ @ @ @ @ @ @ @ @ @ �,fad% � � � � � � � � � � � � � L� ���s��) � � � � � � � � � � � � � Y.���J@ @ @ @ @ @ @ @ @ @ @ @ @ ����X�S@ @ @ @ @ @ @ @ @ @ @ @ @ �\���� � � � � � � � � � � � � � 0=N��"� � � � � � � � � � � � � � d �0#+A @ @ @ @ @ @ @ @ @ @ @ @ @`z.��cEN@ @ @ @ @ @ @ @ @ @ @ @ @ �"@paFV� � � � � � � � � � � � � ���\8=���"� � � � � � � � � � � � � �E���,��@ @ @ @ @ @ @ @ @ @ @ @ @ �� �pz�9E @ @ @ @ @ @ @ @ @ @ @ @ @ � ��YY � � � � � � � � � � � � � �#@p��+r� � � � � � � � � � � � � �@��0�@ @ @ @ @ @ @ @ @ @ @ @ @ �G����9V�@ @ @ @ @ @ @ @ @ @ @ @ @ �,fad% � � � � � � � � � � � � � L� ���s��) � � � � � � � � � � � � � Y.���J@ @ @ @ @ @ @ @ @ @ @ @ @ ����X�S@ @ @ @ @ @ @ @ @ @ @ @ @ �\���� � � � � � � � � � � � � � 0=N��"� � � � � � � � � � � � � � d �0#+A @ @ @ @ @ @ @ @ @ @ @ @ @`z.��cEN@ @ @ @ @ @ @ @ @ @ @ @ @ �"@paFV� � � � � � � � � � � � � ���\8=���"� � � � � � � � � � � � � �E���,��@ @ @ @ @ @ @ @ @ @ @ @ @ �� �pz�9E @ @ @ @ @ @ @ @ @ @ @ @ @ � ��YY � � � � � � � � � � � � � �#@p��+r� � � � � � � � � � � � � �@��0�@ @ @ @ @ @ @ @ @ @ @ @ @ �G����9V�@ @ @ @ @ @ @ @ @ @ @ @ @ �,fad% � � � � � � � � � � � � � L� ���s��) � � � � � � � Yw} IDAT � � � � � � Y.���J@ @ @ @ @ @ @ @ @ @ @ @ @ ����X�S@ @ @ @ @ @ @ @ @ @ @ @ @ �\���� � � � � � � � � � � � � � 0=�NOV�)#p��w������w���>dy��aV�2 � � � � � � � � � � � �� ,���f��5K.��y�2K����Y��]�o��6�����!�`
�u�}��7��
I � � � � � � � � � � � ����/�(��J�7s�^j�U�<�@�����@n���~���?��V�����G�Y����b��@B @ @ @ @ @ @ @ @ @ @ @ �y�s��4w�s������C?�Yy����?vn���� = �����!�V�.��B�f�� �l�
�G @ @ @ @ @ @ @ @ @ @ @ f �u����[n5�������f��.7k& ���0���+r�@����&�Zh!���+X�(� � � � � � � � � � � � �*��9K�5VY��~���a��o^���� = ����,!0��#��_n��-��^x��5�jX@ @ @ @ @ @ @ @ @ @ @ �Xb���
�?�����/��rf~@ �^\���C�H��w��z�.����2' � � � � � � � � � � � �B`����EY������w��bIfE �> \���A^A����.;g���( � � � � � � � � � � � P/��B���~���������_@������1� ��������j� s#� � � � � � � � � � � � -�,��[��y�j�$�#�@_.��� �(����5,��"#���@ @ @ @ @ @ @ @ @ @ @ @ �z����������_@������1� �7��&!� � � � � � � � � � � � ]
,����%=��#]n�u#�@�v���@ @ @ @ @ @ @ @ @ @ @ @ @ �� ��}<*� @ @ @ @ @ @ @ @ @ @ @ @ @ �.��U#� � � � � � � � � � � � � �G��xT� � � � � � � � � � � � � �
\�!.�F @ @ @ @ @ @ @ @ @ @ @ @ @������'@ @ @ @ @ @ @ @ @ @ @ @ @ : ��C\V� � � � � � � � � � � � � �@.��Q!O � � � � � � � � � � � � � t(@pa���@ @ @ @ @ @ @ @ @ @ @ @ @ �>
\���B�@ @ @ @ @ @ @ @ @ @ @ @ @ �P���qY5 � � � � � � � � � � � � � } ���G�<!� � � � � � � � � � � � � �� ����j@ @ @ @ @ @ @ @ @ @ @ @ @ �(@pa�
yB @ @ @ @ @ @ @ @ @ @ @ @ @�C�;�e� � � � � � � � � � � � � � �Q���>�� � � � � � � � � � � � � �@�v���@ @ @ @ @ @ @ @ @ @ @ @ @ �� ��}<*� @ @ @ @ @ @ @ @ @ @ @ @ @ �.��U#� � � � � � � � � � � � � �G��xT� � � � � � � � � � � � � �
\�!.�F @ @ @ @ @ @ @ @ @ @ @ @ @������'@ @ @ @ @ @ @ @ @ @ @ @ @ : ��C\V� � � � � � � � � � � � � �@.��Q!O � � � � � � � � � � � � � t(@pa���@ @ @ @ @ @ @ @ @ @ @ @ @ �>
\���B�@ @ @ @ @ @ @ @ @ @ @ @ @ �P���qY5 � � � � � � � � � � � � � } ���G�<!� � � � � � � � � � � � � �� ����j@ @ @ @ @ @ @ @ @ @ @ @ @ �(@pa�
yB @ @ @ @ @ @ @ @ @ @ @ @ @�C�;�e� � � � � � � � � � � � � � �Q���>�� � � � � � � � � � � � � �@�v���@ @ @ @ @ @ @ @ @ @ @ @ @ �� ��}<*� @ @ @ @ @ @ @ @ @ @ @ @ @ �.��U#� � � � � ���c�>�������
"���"H@�Q5Q�7{b7���7���(������BDM`��
�$*��E@�.J���~s�a��\`�)�^����]����3w�?_ @� @� @� @�@e.��O�� @� @� @� @� @� @� @��:.\���&@���X�dI<���q�������E��[F�F
c�V�G����SO99���?����W�V<`�hP������Z��G�k��c�\�V�����Z�}�/��o{��I���
����c�]v�M�5���n����^���;�g�}Vb���Z�q�g�<g� @� @� @� @� @� PQu+j!� @���x��������)S�����9s"y����1p��l��`���c�~��%��PR`���q���?��/V�XQb���k���q�o}o�1�?���\ @� @� @� @� @� @� TF����T�� �<����3N/z�t�M�i���`���s����oDR�0�pa�Z��^�z��bew>k����h���v������S���|���q�a=�����n��������E�b��Y��:q��bc����L�e�����K�9�w��������r��: @� @� @� @� @� @�P@��{� UL �Fx��?O�m�l�I���K������j����$����G�#�������o�������O<�����o����C�u��m|�����G�b!�8����_�"<��(&&����{6�������<S������%��p�]w�/yQzi���E�6m���� @� @� @� @� @� PQ��%i�'����*�x��e�x~��h�f�wo��y��yT�5c���������Wb�r����e�`a�>}���/)q�f����G���F�/�4�� @� @� @� @� @� @� �U@���>�"@�@)I��g�]Y%��+�,5X���V�Z��w�z������1(S����x���W�;��"yi @� @� @� @� @� @� ��"P��l�> @ b��I�d��������T��=��9]�~��q�u�W���"@� @� @� @� @� @� T����,�� �
��3���e���;������C�Iw��#��v��&E� @� @� @� @� @� �b��U���.5[�I��� ^ye\�����}��X�hQ��GQ��[� @� @� @� @� @� P��+����)��}��h���1�^su|��ge������W��~�=��h @� @� @� @� @� @�
V��f��\�&M�����L�:5���!����x��7k.L���I�����.�T��� @� @� @� @� @� @� TN����\�� 9n����v�m��9s���7�����o�m������[��o��s��:��
�����������:������{����R�2{���zR�Y�f��s� @� @� @� @� @� P���Stj�@�V�b�/��T��3f��!C�e�^���)��m���?-ZTb������^h�� @� @� @� @� @� @�@�.�������[o�u�t&HXG���_�~�G}������/��;����^���.��];��]���u����z���A����q����k�����
}�R7�" @� @� @� @� @� @�6l�b)�D��������1�G�K/�/�7�3&.\��|�������ct��.��Z.U��]���G�;��x���!����v7i��/j�V��L� @� @� @� @� @� ��*V�c[X��7�8�zh\y�U1l��1}��������}�t�����y���:������6K���5�/_^���� @� @� @� @� @� @�@�.�Y��i �!��������F���?"=���#c���j���s���L'�X�">����[�h @� @� @� @� @� @�@.�B�V ��I���[n)6m�����L���C�b�|��wj��� @� @� @� @� @� ����5��;55H�u�6��[�'�6mZ
:}�G��S�b�_|���'I� @� @� @� @� @� �b��U���.�D�~��k2�F��c�=b��vJ�\PP+V��QK� @� @� @� @� @� �������� �*0w���������j���J�}�Z�j�I'���<��x��G���� @� @� @� @� @� PE�����mj�@��7���0��X�dI:~�}��{nM����-Z�H�}�����S�b�4����D� @� @� @� @� @� �����)�VC���}��wv�o���O�9s���q�m�����I�t��!��c��sjzG��-���nN�`�������I3a��8��D���s��A� @� @� @� @� @� �lu+���� �������k��>�{G�N��k�}b����f�6��2U
�~��x|���2eJ�X�z�������xf�K/���6�kl������o�}�s&O~w�����K��+�*s�����];��� '�w���R�<������_��o�6,���������/>���r�-c��E����/��W_}5�����V�z. @� @� @� @� @� @� *��pae|*�D� �2����.]/��r�UVk��I���{c�}�-kX��b��l�n�ZfNymM�]�diy���%K�St��r�����G�M�ER%2Y;�9r���K#@� @� @� @� @� @� T���<U��:���k���g*����Q�N�2O��Y�����N�_u������W\��G�+6l���E�q�9��e�^�s� @� @� @� @� @� @�@e���"U~����k�!@����7'g���y�}.T_�������������Y_��_�
�5o�"��k���{��W�����%�cF��O?�4>����U�V���e��w��1��Ux==
�!@� @� @� @� @� @� �5���n:�����V�W@�0_)�Tr��J��l� @� @� @� @� @� T3��j�@�� ��q'v` @� ��S IDAT @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @� @� @� @� @� @� @�� ��g�� @� @� @� @� @� @� P��k��� @�@��w�
����j�p�5�:��cs�����#_�uM"�&?9������_�zM�(1��7�,��3gN�1. @� @� @� @� @� @�� V�'b?��.��#GF�~��������;�\y�x�nO`���yd������^�M�5*.���w����V[F�F
c���]��qL�^q��7���cc��ku���<d����=�Zl��.�0�����5�����c����=��S�h��6) 6�y���]l�{u����=���~ @� @� @� @� @� �"u��>m� ��@�C���1cb��e��nnQ�:w��^zY�mM��q<���k��c2���kWl�Q�FF��
m����������'��� �c���u��>�0~��3J=��b��������a��f��z����A�D�N�V�>Uep�:o��b�}��'b���Q��?S��s�O @� @� @� @� @� ����]5��] @`����[���D�j,��k�H^E��/�������?�@R�����z��H*�%�V�Zi5���.�8qb$���g�h��Q�h�"����f�������S�N�3����.
��G)�M|Fg��}�{����A� @� @� @� @� @� �J)P�R��� @`�
4k�,��-.���<xH��Sg���� lH��C���x����E����$8�������g��s����N��_��w'��N�,f����~&.���h��ey�V��aC���������a�?� @� @� @� @� @� @��
V�c[����5v�6}F<��q�5��a��!���*����K��'�L�r��WE�M�����Q^�����)S�d�&�80n���k�.�}ak��Ax��q��7�{�7��M�~{��nT��?>���I��u�L���t�E+V�#�* @� @� @� @� @� ���pa�z\6K� �u+p���g�N�kW����'������^1b�bY�F�|���3gN��m���v�1>�������{��t��G�����a�~����_��������;�*Z��:a�=�S�Ni��$���75 @� @� @� @� @� XwU3=��<�L� UX���c��K/��
��6��f��
��K_���vv;}�a\~�e�i���j������������4�V��{��t���������+��"������7�[����=�^L�p�-���o��������@A���!1M*8�)��/���~�����N��������\]�u1c��|�)1.�y;��c�M���Y�&�z����Gg*�+1� @� @� @� @� @� T��Ue��I� �����>g�����W_;��o���������w������c�����C����G��g��M7�4�.\�-J��-[�slM�H��I�����W/:�{���g��~�T6���+�_������������/6�����{��zxP>K�c���'���x��g���:uj$�$\x������_�Z�L� @� @� @� @� @� �� �raex
�@� 5F`��1q�O�
6j�(��c��m��D��+?�?���8���b��5��2���^��?�8���
�~����z�m���|�;���.]� �=��:W�x��c��6J�_�J��5�z������k�������g+&m�� 1e���L����i��R,X�S��q�A�����]c����?>.f��"�5������Q=���A�.]b�}��f��e������n���� @� @� @� @� @� @�2 V��a/ P�.���l����o���M�W����eBl���A�j[������J<������2��`H����^�����{d*����^Y- �u��1������/kJ���b�-�g����}��������>��e�]Vk�E�
+B&l�����C�t�aCWV��u��3g������/��n����g������� ������W�E�v����#G�Z����o�)^~�������O�$^9*�{~D��k��.�|��G�Z����w�}���������}-Z4_��M @� @� @� @� @� @�@>u�d P1����|0�9��bn��V��C�]����3�}��.8��X%_������d~�}���e�z����h��b��U�q�i����c���-[G��8���3*O�}��/�����%U����~���V���5+���N+�L.&�'U��T8<��s��E�[n�d��%��C�d*K�-6�}��1t�����w��/��\�����-�'�pB����bk6h� .������o��5�\x�i�E�*����_i�]#@� @� @� @� @� @�@�
�\X��#@� e����%���37n�{\���������2eJ���Ko�j�p���
f�,X�?�\�=��S��a+�.]�4����L��{l�j�8�����k��_x!�,YR�ZU���'����I�f�m����<G��b��3'������>���t��%����[n�e\x�E9�*�1�����o��^J������s�%��m�h�"g� @� @� @� @� @� TV����d�� ��@�c�)�\:tH�?���2��\wE����u�n�����
yxPz��������S'
z$�:���]���_I8q���g��%a����&.����>}z�kV���Le��vh��%���k�h��Y�z�|���J�)�0q��bN��Rt�����U/���3�<�^O��� s�$d��#����: @� @� @� @� @� *��pa�}46F� �
L�0!�5m��u���S�������5x`e��U��.�o�G�2�o���
h���/s��u'P0teXp����w-z}����b��27����=��5~~����y�R���=;n����{�8p`�c���E��3�<�n�4��R����)28�q_+R]2����s�M:��~��b�-��t��������n�k&��t�R�� @� @� @� @� @� @�� V�'b? �S`������r��.]��]�lY�q�����
���X,��u��M//^���!��c�9s����#����K:{�#�T;vl^;k��m�q���t�g1z�Kq��7G�^����o^l~.=����U�������Rc��j����T���I��\?S>���o��e4n�����^l��DYVKB�E������e
����f�@� @� @� @� @� @� �
\���� j��F��\��}�'�|2�,Y��d�z�����W�����9v*TZ���a�V�X�v����S\x�E��C��O>��G�G��b#���9w��|���c
�������&�����=V7���#F�:n��y���M��:f��M�4]�R����%A����Y��6iZ��e�P' @� @� @� @� @� 6�����w[X}�$��������v��q�9gg>�������71��-R������i�����}Ip�w�>9�����>��>�E��7�u�^��2o��� �u����|���c�����nm��I�W�9���/C~C�h��p�����@��������$0ZV+���g���,�~� @� @� @� @� @� ����W�jw�K� T�@�Jl�`5]d���1|���t��o��V^�}��I1y�������5��A�\��H��S�L�=zT��;6�O��{������i�����o�Z�j��q�����^��R������t�*�_/,���*{�2o�� @� @� @� @� @� H����� @� k!P�N�tvR��6o�������0bD�R����zaE���v��5]j��i��]c�Z�L�:5��_b��[4O�}��"������>+�N�m�Q4n�8�4}��@d������f�y� @� @� @� @� @� @`C
nH}�&@� k(�p����������_����o� ��l����
���O�]��XYZ������m��7���:������&@��A��+�V����
^�}�9��.����t�M������i�7�|}�a�4I%�>���1I����\����.w��I��c @� @� @� @� @� *��pae{"�C� �h��f�����3r�����c�+���/��E��p��O>����y��h�"�����=o]L��
:4�E�n��u�C]9����o$�*�
��j��"��`k����x����n�������k����+(RbN�����U<�}��c�^x��" !��:w��y�����������-�A�G<�|�����{�m�}�) @� @� @� @� @� �S@��r>�"@� e
����Q�V�t��2�M����1��2�����N��i��i��O<�k�j]��C�t��}o���j�_�_}���:uj�t�C��%���2\�Tv||������������#G��q������o�S+���U�������Y4����o��T�l��E|�� �����2������{l:n��i����;���9%����N<��8�����������' @� @� @� @� @� 6��p��w[ �6�5�N�:�K�p����/�,��+���W_}U���^H�����3��O�x������s\��c�vH��=��R��s�u�1�� ]u��6�80���i�s�������A���C2U����#.����8qb��&U������c6lG���9����m����V�������.��v�w�y��7�x#�������w�O>�_8�k���~�~y�E��Cg��g�~zYG�G� @� @� @� @� @� �J+P����� @`���{C���o��.[�,�v�����;n/6&�D����%���nN>������|]��g�}v���n�`�W��#���3[���a�����Z�
���K�������:�����}�p`�L����,S����{�$�x�UW����6��T~�e�6��^{E��7N��o����K/�{��X� �}��'� g�-����{����gc�������E�ER�0y���n���G�6m�E��d���(S�0�;~��b������j����J���g^z��t_�����X�f��n��e�]��w��MB�^xQ�iG������a��f����7��W��I'��l�M|���x�_������o��f���k���=��A�t���>}zt��w�{�y��7����������3�����Y�=� @� @� @� @� @� @`}
�Om�"@�@%X�tY$��Z4,6L�&����N�TK^5��~�q�}O�h�|�I\y�����p�-1��ik.L�]������O��?�/_^"