Обсуждение: NULL as a (pseudo-)value not described?

As far as I can tell,  
the un-value NULL is not described anywhere in the doc
as being something that can be assigned to a column.

E.g. :
   .  not listed as a constant  (well that's reasonable as it isn't)
    .  not listed as an expression  (is NULL an expression?)
    .  not explicitly listed under the INSERT and UPDATE commands
        as a valid syntactic unit as an alternative to an expression
        (If it is not an expression)

I do see it listed as a valid keyword but there is no description there.

To put it another way,   the following syntax is apparently not permitted :
      UPDATE my_nullable_table SET nullable_col = NULL;

(I am not referring to other null-related constructs such as IS NULL,  IFNULL etc)

I have to assume I'm missing something?
Or is it that NULL is so,  well,  null,   that it cannot be described?

John

johnlumby wrote
> As far as I can tell,  
> the un-value NULL is not described anywhere in the doc
> as being something that can be assigned to a column.
>
> E.g. :
>    .  not listed as a constant  (well that's reasonable as it isn't)
>     .  not listed as an expression  (is NULL an expression?)
>     .  not explicitly listed under the INSERT and UPDATE commands
>         as a valid syntactic unit as an alternative to an expression
>         (If it is not an expression)
>
> I do see it listed as a valid keyword but there is no description there.
>
> To put it another way,   the following syntax is apparently not permitted
> :
>       UPDATE my_nullable_table SET nullable_col = NULL;
>
> (I am not referring to other null-related constructs such as IS NULL, 
> IFNULL etc)
>
> I have to assume I'm missing something?
> Or is it that NULL is so,  well,  null,   that it cannot be described?

http://www.postgresql.org/docs/9.4/static/ddl-default.html

To your point: null is a constant/literal as defined in

http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

and so can appear in the defined expression area and so your example is
explicitly allowed.

http://www.postgresql.org/docs/9.4/static/bookindex.html#AEN167062 (under
"N")

Pseudo-type has a specific meaning is PostgreSQL which null does not conform
to.

Having brought this to attention why did you go looking for it and where, in
order, did you look?

I'll agree that it seems that a previous recognition that null can always
used as a valid data value is assumed much like it is assumed that 1000 is a
valid value for an integer.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/NULL-as-a-pseudo-value-not-described-tp5818521p5818543.html
Sent from the PostgreSQL - docs mailing list archive at Nabble.com.

John, please respond to the list next time.

Sorry,  I was not sure if I understood what you were saying.
Now I do ...

On Thu, Sep 11, 2014 at 9:24 AM, John Lumby <johnlumby@hotmail.com> wrote:

Thanks David  ...  but  ... (below)

----------------------------------------
> Date: Wed, 10 Sep 2014 18:20:00 -0700
> From: david.g.johnston@gmail.com
> To: pgsql-docs@postgresql.org
> Subject: Re: [DOCS] NULL as a (pseudo-)value not described?
>
> johnlumby wrote
>> As far as I can tell,
>> the un-value NULL is not described anywhere in the doc
>> as being something that can be assigned to a column.
>>
>> E.g. :
>>    .  not listed as a constant  (well that's reasonable as it isn't)
>>     .  not listed as an expression  (is NULL an expression?)
>>     .  not explicitly listed under the INSERT and UPDATE commands
>>         as a valid syntactic unit as an alternative to an expression
>>         (If it is not an expression)
>>
>> I do see it listed as a valid keyword but there is no description there.
>>
>> To put it another way,   the following syntax is apparently not permitted
>> :
>>       UPDATE my_nullable_table SET nullable_col = NULL;
>>
>> (I am not referring to other null-related constructs such as IS NULL,
>> IFNULL etc)
>>
>> I have to assume I'm missing something?
>> Or is it that NULL is so,  well,  null,   that it cannot be described? 

>
> http://www.postgresql.org/docs/9.4/static/ddl-default.html
>
> To your point: null is a constant/literal as defined in
>
> http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

Well I have searched that page from top to bottom
and nowhere do I see such a statement.    There are many mentions
of the word "NULL" and "null" but as far as I can tell none of these
refer to to the use of the word as a substitute for a value
such as occurs in a column assignment in an INSERT or UPDATE command.

Can  you please cut'n'paste the relevant text that you see that states this?

​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.

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.

 

>
> and so can appear in the defined expression area and so your example is
> explicitly allowed.
>
> http://www.postgresql.org/docs/9.4/static/bookindex.html#AEN167062 (under
> "N")

Likewise --    8 instances of the nullness concept,
none of which refer to to the use of the word as a substitute for a value

such as occurs in a column assignment in an INSERT or UPDATE command.

​Fair enough...my response was somewhat unorganized.​  Again, this supports the theory that it is assumed people know what null is and simply need to see how PostgreSQL specifically treats it.

 

>
> Pseudo-type has a specific meaning is PostgreSQL which null does not conform
> to.
>
> Having brought this to attention why did you go looking for it and where, in
> order, did you look?

​ I've already agreed the documentation could be improved - but since you are the one who brought this up it would be helpful to get your thoughts on how best to do that.  Most everyone on this list intimately understands how NULL works so while we can write accurate documentation it is more difficult to write/organize the documentation for the purposes of learning.  IOW, It would be nice to get to know the target audience.

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>

David J.

​

 

________________________________
> Date: Thu, 11 Sep 2014 22:08:05 -0400
> Subject: Re: [DOCS] NULL as a (pseudo-)value not described?
> From: david.g.johnston@gmail.com
> To: johnlumby@hotmail.com
> CC: pgsql-docs@postgresql.org
>
>
>
> On Thu, Sep 11, 2014 at 9:24 PM, johnlumby
> <johnlumby@hotmail.com<mailto:johnlumby@hotmail.com>> wrote:
> On 09/11/14 10:03, David Johnston wrote:
> On Thu, Sep 11, 2014 at 9:24 AM, John Lumby
> <johnlumby@hotmail.com<mailto:johnlumby@hotmail.com>> wrote:
> ​As I mentioned before the fact that null is a literal/constant is
> ...
> ​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.

Not to harp on this,  but although "trying" it in the sense of
 typing it in and seeing if some error occurs is easy,
verifying that it produces the expected result in the database
is far from easy IF the person doing it cannot find
any semantic definition of a null value,  e.g. the various
tricky "unknown" rules etc.

>
> 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.
>
> ...
>
> ​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.
>

Yes,   I agree,   that all sounds good,
     and also thanks for correcting factual errors in my version.

I have two comments,  one of which I think you may have already addressed:

1 .   would be good if the documentation distinguishes
    between two slightly different points:
     .    the semantic meaning of a null value
     .   valid syntactic uses of the token NULL

2.    I suggest including somewhere explicit examples of
       setting a column value to NULL
      a)  in a VALUES clause of INSERT
      b)  in a SET clause of UPDATE