Re: NULL as a (pseudo-)value not described?

On Thu, Sep 11, 2014 at 9:24 PM, johnlumby <johnlumby(at)hotmail(dot)com> wrote:

> On 09/11/14 10:03, David Johnston wrote:
>
On Thu, Sep 11, 2014 at 9:24 AM, John Lumby <johnlumby(at)hotmail(dot)com> wrote:
>
> ​As I mentioned before the fact that null is a literal/constant is
> assumed. My point here is that given that assumption the referenced section
> explains that "SET col = null" is valid. SET col = <expression>,
> <constants> are <expressions>, null is a valid <constant>, ergo SET col =
> null is valid.
>
>
> Ah, ok. Well, I would say that NULL is not a constant,
> since a constant has a well-defined value and can be used
> to represent that value in any expression, neither of which
> are true of NULL. However see my last para.
>
> ​OK - pseudo-constant then; it can be used in any place a normal constant
can be used for those situations where you don't have a well-defined value
to put there.​

>
> The documentation does not describe all possible valid constants -
> though I admit given the special nature of NULL it probably should do so in
> this instance. The trick is avoiding adding comments pertaining to NULL
> all over the documentation (see below) and confusing the underlying normal
> non-null usage. Thus, right now, only when NULL behavior is important does
> it get addressed. SET col = NULL is no different then SET col = 'a
> string' so why make it seem like a special case by pointing out the
> "obvious"?
>
> Even if people are not positive simply trying what you wrote is easy and
> in the absence of any error it would become obvious that NULL is valid in
> UPDATE/SET.
>
> The larger problem is people thinking "NULL = NULL" returns true or
> "NULL = 'some other literal'" returns FALSE: i.e., that NULL is never
> special but is just another literal.
>
>
> Actually I have seen mailings where someone is asking how to set a column
> value to NULL
> so I'd say it is not obvious. A reference manual should preferably
> avoid
> assuming anything about what it is documenting.
>

​Everything has to make assumptions regarding prior knowledge - but again I
do agree in this specific case.​

> Well, I would assume that one or other of the SQL ANSI standards
> documents NULL
> and (provided postgresql confirms to that standard) we can simply copy or
> rephrase
> that text. However, I personally am not a standards expert, and after a
> short
> and futile search on on the web I can't find any accessible html/pdf
> format of any SQL ANSI standard.
> I assume someone more knowledgeable on standards could find it.
> But if I had to take a shot at it, I would add a description of NULL at
> the bottom of section 4.2
> following the text :
> "In addition to this list, there are a number of constructs that can be
> classified
> as an expression but do not follow any general syntax rules.
> These generally have the semantics of a function or operator and are
> explained
> in the appropriate location in Chapter 9. An example is the IS NULL
> clause."
>
> something along these lines :
>
> <start addition>
> Another example is the NULL token. This is not a value but can be
> included in certain commands
> where a value can be assigned to a column, such as INSERT (the
> VALUES clause)
> and UPDATE (the SET clause), in the same position as a value
> would be placed,
> provided the column is not defined as NOT NULL. Its meaning is "no
> value".
> It is not equal to any other value, and not unequal to any other
> value,
> and not equal to another NULL, and not unequal to another NULL.
> NULL may not be stated as an explicit default value for a column,
> but if no default value is defined for a column which is not defined
> as NOT NULL,
> then its default value is NULL. In this case, NULL has the sense
> of "not set".
> <is the next true? I am not somewhere to verify ...?>
> It cannot be used in an expression other than when it occurs alone
> without any associated operator or function.
> </is the above true? I am not somewhere to verify ...?>
> </end addition>
>

​User documentation does not want to copy from standards, or in many cases,
source code. The person writing said documentation should be informed by
those sources and then write something targeted for the typical user.

You entire paragraph boils down to: NULL can be used like a constant but
when compared with itself, or other constants, results in the third logical
state - unknown.

​It can be declared as a default: CREATE TABLE test ( col varchar NULL
DEFAULT NULL );

And its use in an expression is not limited any more than any other "true
constant"

More of a semantic but you can attempt to insert NULL into a column having
a NOT NULL constraint - it will just cause an error. Much like you can
attempt to insert -20 into a column with a constraint CHECK(col >= 0)

My rough idea at the moment is to introduce a section in "Tutorial: The SQL
Language" chapter, subsequent to "Joins Between Tables", and summarize and
cross-reference to other sections the concept and use of NULL. The three
prior sections (Rows, Queries, Joins) all have NULL implications that are
not covered in those chapters explicitly and adding it to all three doesn't
feel right. A separate encapsulating chapter seems better and lets the
reader focus on those topics without the immediate distraction of NULL
complicating things. This would be the "reference section" for the topic.
If you think of the topic as being a pre-requisite then the tutorial does
make some sense - and when I devised the above I didn't really process that
this was "tutorial" material...consideration for another time.

In "SQL Syntax" I would cover the topic again under "constants" - probably
calling them pseudo-constant (inspired from the above discussion).

I'm uncertain on whether or how to cover them within the "Data Types"
chapter but feel like it should be mentioned there as well.

David J.