Обсуждение: BUG #15166: PL/PGSQL default rowtype variable value is null but alsonot null
BUG #15166: PL/PGSQL default rowtype variable value is null but alsonot null
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15166 Logged by: Alex Email address: cdalxndr@yahoo.com PostgreSQL version: 10.3 Operating system: Windows 10 version 1709 Description: Running the following script will report that 'var' is null but also not null: create table test_table(); create function test() returns void as $$ declare var test_table%rowtype; begin raise notice 'is null: %', (var is null); raise notice 'is not null: %', (var is not null); end; $$ LANGUAGE plpgsql; select test() produces output: NOTICE: is null: t NOTICE: is not null: t By documentation, it should be null: "If the DEFAULT clause is not given then the variable is initialized to the SQL null value." (https://www.postgresql.org/docs/current/static/plpgsql-declarations.html)
Re: BUG #15166: PL/PGSQL default rowtype variable value is null but also not null
От
Andrew Gierth
Дата:
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes: 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)
Thank you for clarification.
This issue can be closed.
This issue can be closed.
On Saturday, April 21, 2018, 7:45:45 AM GMT+3, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:
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)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes: > 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. Right, if they're declared as a named composite type rather than RECORD. > Also, it looks like this behavior changes in pg11, probably not > intentionally. For the record, it was entirely intentional, cf https://postgr.es/m/8962.1514399547@sss.pgh.pa.us As of commit 4b93f5799, both named-composite and RECORD variables are initialized to simple NULLs, not ROW(NULL,NULL,...). regards, tom lane