On Saturday, April 21, 2018, 7:45:45 AM GMT+3, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
PG> Running the following script will report that 'var' is null but
PG> also not null:
PG> create table test_table();
This creates a table with no columns. Such an object isn't valid in the
SQL spec, but if you extend the spec's definition of how IS NULL and IS
NOT NULL are required to work to the zero-column case, it turns out that
a value of such a table's rowtype can be simultaneously NULL and NOT
NULL:
- a composite value IS NULL either if it is the null value, or every
one of its column values is the null value. By long-standing
convention in logic, "every" is vacuously true when applied to the
empty set, so a composite value of degree 0 always passes the IS
NULL test.
- a composite value IS NOT NULL if it is not the null value and none
of its column values is the null value. "None of" is once more
vacuously true when there are no columns, so a composite value of
degree 0 passes IS NOT NULL if and only if it is not the null value.
The spec is quite explicit that IS NULL and IS NOT NULL are inverses of
each other only if the operand is of degree exactly 1 (i.e. is a scalar
or a row value with exactly 1 column).
PG> By documentation, it should be null: "If the DEFAULT clause is not
PG> given then the variable is initialized to the SQL null value."
That could be considered a slight bug in the documentation, because what
actually happens for rowtype variables is that they are initialized to a
row value with all null columns, NOT to the null value. In most cases
the difference is somewhat hard to spot.
Also, it looks like this behavior changes in pg11, probably not
intentionally. Relying on it either way seems dangerous (in fact relying
on anything at all to do with zero-column tables seems dangerous).
--
Andrew (irc:RhodiumToad)
