Re: strange IS NULL behaviour

On Fri, Jul 5, 2013 at 11:03:56AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > On Thu, Jul 4, 2013 at 04:29:20PM -0400, Tom Lane wrote:
> >> No, it isn't, or at least it's far from the only place. If we're going
> >> to change this, we would also want to change the behavior of tests on
> >> RECORD values, which is something that would have to happen at runtime.
>
> > I checked RECORD and that behaves with recursion:
>
> Apparently you don't even understand the problem. All of these examples
> you're showing are constants. Try something like
>
> declare r record;
> ...
> select ... into r ...
> if (r is null) ...

OK, I created the following test on git head (without my patch), and the
results look correct:

DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN

DROP TABLE IF EXISTS test;
CREATE TABLE test (x INT, y INT);

INSERT INTO test VALUES (1, NULL), (NULL, 1), (NULL, NULL);
FOR r IN SELECT * FROM test
LOOP
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END LOOP;
END;
$$;

NOTICE: false
NOTICE: false
NOTICE: true

Am I missing something?

Is this an example of NOT NULL contraints not testing NULLs?

CREATE TABLE test3(x INT, y INT);
CREATE TABLE test5(z test3 NOT NULL);

INSERT INTO test5 VALUES (ROW(NULL, NULL));

SELECT * FROM test5;
z
-----
(,)

Looks like I have to modify ExecEvalNullTest(). If I fix this, is it
going to cause problems with pg_upgraded databases now having values
that are no longer validated by the NOT NULL constraint?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +