Re: strange IS NULL behaviour

On Fri, Jul  5, 2013 at 11:03:56AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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