revision of todo: NULL for ROW variables

Hello

I am checking PLpgSQL ToDo topics, and I am not sure if this topic
isn't done. And if not, then I would to get some detail.

Now there is possible to test row's variable on NULL, now it is
possible to assign NULL to row variable. What we can do more?

a) There is small difference between returned value when we use a
empty row or empty record variable

CREATE OR REPLACE FUNCTION f2(int) RETURNS t2 AS $$
DECLARE rv t2; re record;
BEGIN
CASE $1 WHEN 0 THEN RETURN rv;
WHEN 1 THEN RETURN re;
ELSE RETURN null;
END CASE;
END; $$ LANGUAGE plpgsql;

postgres=# SELECT f2(0);
f2
────
()
(1 row)

Time: 0.759 ms
postgres=# SELECT f2(1);
f2
────────
[null]
(1 row)

Time: 0.570 ms
postgres=# SELECT f2(2);
f2
────────
[null]
(1 row)

() is equal to NULL for test IS NULL, but it isn't same - see:

Time: 0.586 ms
postgres=# SELECT f2(0) is null;
?column?
──────────
t
(1 row)

Time: 0.548 ms
postgres=# SELECT f2(1) is null;
?column?
──────────
t
(1 row)

Time: 0.535 ms
postgres=# SELECT f2(2) is null;
?column?
──────────
t
(1 row)

postgres=# SELECT 'Hello' || f2(0);
?column?
──────────
Hello()
(1 row)

Time: 51.546 ms
postgres=# SELECT 'Hello' || f2(1);
?column?
──────────
[null]
(1 row)

so this is one known issue.

Actually rowvar := NULL <-> reset all fields inside row. I think so
this is perfect from perspective "IS [NOT] NULL" operator. But maybe
it isn't practical. So we can distinct between assign some field to
NULL and between assign row variable to NULL. This flag can be used
just only for returning value. Some like

DECLARE r rowtype;
BEGIN
IF a = 1 THEN
RETURN r; -- result is NULL
ELSIF a = 2 THEN
r.x := NULL;
RETURN r; -- result is ()
ELSIF a = 3 THEN
r.x := NULL;
r := NULL;
RETURN r; -- result is NULL;

comments? Is this change some what we would?

next question? I found one paradox. When some IS NULL, then any
operation with this value should be NULL. But it isn't true for
composite values!

postgres=# CREATE TYPE t AS (a int, b int);
CREATE TYPE
Time: 66.605 ms
postgres=# SELECT 'Hello' || (NULL, NULL)::t;
?column?
──────────
Hello(,)
(1 row)

postgres=# SELECT (NULL, NULL)::t is null;
?column?
──────────
t
(1 row)

does know somebody if this behave is good per ANSI SQL?

Regards

Pavel Stehule

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> I am checking PLpgSQL ToDo topics, and I am not sure if this topic
> isn't done. And if not, then I would to get some detail.

I think that thread petered out because we didn't have consensus on
what the behavior ought to be. It goes back to whether there is
supposed to be a difference between NULL and ROW(NULL,NULL,NULL,...)

regards, tom lane

On Thu, Oct 28, 2010 at 10:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I am checking PLpgSQL ToDo topics, and I am not sure if this topic
>> isn't done. And if not, then I would to get some detail.
>
> I think that thread petered out because we didn't have consensus on
> what the behavior ought to be.  It goes back to whether there is
> supposed to be a difference between NULL and ROW(NULL,NULL,NULL,...)

I think somewhere along the line it was noticed that SQL says you are
supposed to treat (null, null) as null and the behavior of 'is null'
operator was changed to reflect this while other null influenced
behaviors were left intact (for example, coalesce()).

My take on this is that we are stuck with the status quo. If a change
must be done, the 'is null' change should be reverted to un-standard
behavior. The SQL standard position on this issue is, IMNSHO, on
mars.

merlin

On Oct 28, 2010, at 11:41 AM, Merlin Moncure wrote:
> On Thu, Oct 28, 2010 at 10:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> I am checking PLpgSQL ToDo topics, and I am not sure if this topic
>>> isn't done. And if not, then I would to get some detail.
>>
>> I think that thread petered out because we didn't have consensus on
>> what the behavior ought to be. It goes back to whether there is
>> supposed to be a difference between NULL and ROW(NULL,NULL,NULL,...)
>
> I think somewhere along the line it was noticed that SQL says you are
> supposed to treat (null, null) as null and the behavior of 'is null'
> operator was changed to reflect this while other null influenced
> behaviors were left intact (for example, coalesce()).
>
> My take on this is that we are stuck with the status quo. If a change
> must be done, the 'is null' change should be reverted to un-standard
> behavior. The SQL standard position on this issue is, IMNSHO, on
> mars.

As someone who's wanted this... what if we had a dedicated function to tell you if a row variable had been defined? I definitely don't like the though of creating something that effectively duplicates IS NULL, but I'd much rather that than continue not having the ability to tell if a row/record variable has been set or not.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Mon, 2010-11-01 at 09:44 -0500, Jim Nasby wrote:
> > My take on this is that we are stuck with the status quo. If a
> change
> > must be done, the 'is null' change should be reverted to un-standard
> > behavior. The SQL standard position on this issue is, IMNSHO, on
> > mars.
>
> As someone who's wanted this... what if we had a dedicated function to
> tell you if a row variable had been defined? I definitely don't like
> the though of creating something that effectively duplicates IS NULL,
> but I'd much rather that than continue not having the ability to tell
> if a row/record variable has been set or not.

If we just invent a couple more variants of NULL, it will solve all our
problems ;)

Seriously though, I think that we should stick as closely to the letter
of the standard as possible here (or, if there is ambiguity, pick one
reasonable interpretation). NULL semantics are confusing enough without
everyone making their own subtle tweaks.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> Seriously though, I think that we should stick as closely to the
> letter of the standard as possible here (or, if there is
> ambiguity, pick one reasonable interpretation). NULL semantics are
> confusing enough without everyone making their own subtle tweaks.

+1

If the standard behavior doesn't support all the functionality we
need, we should be looking at PostgreSQL extensions which do not
conflict with standard syntax. Supporting standard syntax with
different semantics is evil.

-Kevin

On Mon, Nov 1, 2010 at 2:29 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
>> Seriously though, I think that we should stick as closely to the
>> letter of the standard as possible here (or, if there is
>> ambiguity, pick one reasonable interpretation). NULL semantics are
>> confusing enough without everyone making their own subtle tweaks.
>
> +1
>
> If the standard behavior doesn't support all the functionality we
> need, we should be looking at PostgreSQL extensions which do not
> conflict with standard syntax.  Supporting standard syntax with
> different semantics is evil.

I have basically two gripes with sql standard treatment of null row
values. One is the backward compatibility problem (which extends all
the way up to PQgetisnull, and would affect lots of my code) and the
other is that you will lose the ability to ever usefully enforce table
check constraints over rowtypes like we do for domains (you need to
reserve rowtype := null to skirt the issue in plpgsql declarations).

merlin