Re: Assigning ROW variable having NULL value to RECORD type variabledoesn't give any structure to the RECORD variable. - Mailing list pgsql-hackers

On Sat, Jan 4, 2020 at 2:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Wed, Jan 1, 2020 at 10:50 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
> >> I know this is expected to happen considering the changes done in
> >> above commit because from this commit onwards, NULL value assigned to
> >> any row variable represents a true NULL composite value before this
> >> commit it used to be a tuple with each column having null value in it.
> >> But, the point is, even if the row variable is having a NULL value it
> >> still has a structure associated with it. Shouldn't that structure be
> >> transferred to RECORD variable when it is assigned with a ROW type
> >> variable ? Can we consider this behaviour change as a side effect of
> >> the improvement done in the RECORD type of variable?
>
> > I'm not an expert on this topic. However, I *think* that you're trying
> > to distinguish between two things that are actually the same. If it's
> > a "true NULL," it has no structure; it's just NULL. If it has a
> > structure, then it's really a composite value with a NULL in each
> > defined column, i.e. (NULL, NULL, NULL, ...) for some row type rather
> > than just NULL.
>
> Yeah.  In general, we can't do this, because a null value of type
> RECORD simply hasn't got any information about what specific rowtype
> might be involved.  In the case where the null is of a named composite
> type, rather than RECORD, we could choose to act differently ... but
> I'm not really sure that such a change would be an improvement and not
> just a decrease in consistency.
>
> In any case, plpgsql's prior behavior was an implementation artifact
> with very little to recommend it.  As a concrete example, consider
>
> create table t1(a int, b text);
>
> do $$
> declare x t1; r record;
> begin
>   x := null;
>   r := x;
>   raise notice 'r.a = %', r.a;
> end $$;
>
> do $$
> declare r record;
> begin
>   r := null::t1;
>   raise notice 'r.a = %', r.a;
> end $$;
>
> I assert that in any sanely-defined semantics, these two examples
> should give the same result.  In v11 and up, they both give
> 'record "r" is not assigned yet' ... but in prior versions, they
> gave different results.  I do not want to go back to that.
>
> On the other hand, we now have
>
> do $$
> declare x t1; r record;
> begin
>   x := null;
>   r := x;
>   raise notice 'x.a = %', x.a;
>   raise notice 'r.a = %', r.a;
> end $$;
>
> which gives
>
> NOTICE:  x.a = <NULL>
> ERROR:  record "r" is not assigned yet
>
> which is certainly also inconsistent.  The variable declared as
> being type t1 behaves, for this purpose, as if it contained
> "row(null,null)" not just a simple null.  But if you print it,
> or assign it to something else as a whole, you'll find it just
> contains a simple null.  One way to see that these are different
> states is to do
>
> do $$ declare x t1; begin x := null; raise notice 'x = %', x; end$$;
> NOTICE:  x = <NULL>
>
> versus
>
> do $$ declare x t1; begin x := row(null,null); raise notice 'x = %', x; end$$;
> NOTICE:  x = (,)
>
> And, if you assign a row of nulls to a record-type variable, that works:
>
> do $$
> declare x t1; r record;
> begin
>   x := row(null,null);
>   r := x;
>   raise notice 'x.a = %', x.a;
>   raise notice 'r.a = %', r.a;
> end $$;
>
> which gives
>
> NOTICE:  x.a = <NULL>
> NOTICE:  r.a = <NULL>
>
> If we were to change this behavior, I think it would be tantamount
> to sometimes expanding a simple null to a row of nulls, and I'm
> not sure that's a great idea.
>
> The SQL standard is confusing in this respect, because it seems
> that at least the "x IS [NOT] NULL" construct is defined to
> consider both a "simple NULL" and ROW(NULL,NULL,...) as "null".
> But we've concluded that other parts of the spec do allow for
> a distinction (I'm too lazy to search the archives for relevant
> discussions, but there have been some).  The two things are
> definitely different implementation-wise, so it would be hard
> to hide the difference completely.
>
> Another fun fact is that right now, assignment of any null value
> to a composite plpgsql variable works the same: you can assign a simple
> null of some other composite type, or even a scalar null, and behold you
> get a null composite value without any error.  That's because
> exec_assign_value's DTYPE_REC case pays no attention to the declared
> type of the source value once it's found to be null.  Thus
>
> do $$ declare x t1; begin x := 42; raise notice 'x = %', x; end$$;
> ERROR:  cannot assign non-composite value to a record variable
>
> do $$ declare x t1; begin x := null::int; raise notice 'x = %', x; end$$;
> NOTICE:  x = <NULL>
>
> That's pretty bizarre, and I don't think I'd agree with adopting those
> semantics if we were in a green field.  But if we start paying attention
> to the specific type of a null source value, I bet we're going to break
> some code that works today.
>
> Anyway, maybe this area could be improved, but I'm not fully convinced.
> I definitely do not subscribe to the theory that we need to make it
> work like v10 again.

Okay. Thanks for sharing your thoughts on this.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com