Date: Wed, 8 Jan 2025 16:10:42 -0500
From: Bruce Momjian <bruce@momjian.us>
To: jbe-mlist@magnetkern.de,
	PostgreSQL-development <pgsql-hackers@lists.postgresql.org>
Subject: Re: Parameter NOT NULL to CREATE DOMAIN not the same as CHECK (VALUE
 IS NOT NULL)
Message-ID: <Z37p0paENWWUarj-@momjian.us>
References: <173591158454.714.7664064332419606037@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <173591158454.714.7664064332419606037@wrigleys.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/Z37p0paENWWUarj-%40momjian.us>
Precedence: bulk

On Fri, Jan  3, 2025 at 01:39:44PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/17/sql-createdomain.html
> Description:
> 
> The manual claims:
> 
> The syntax NOT NULL in this command is a PostgreSQL extension. (A
> standard-conforming way to write the same would be CHECK (VALUE IS NOT
> NULL). […])
> 
> But both variants differ when composite types are involved:
> 
> CREATE TYPE complex AS (real float8, imag float8);
> 
> CREATE DOMAIN d1 AS complex NOT NULL;
> CREATE DOMAIN d2 AS complex CHECK (VALUE IS NOT NULL);
> 
> SELECT '(,)'::d1; -- allowed
> SELECT '(,)'::d2; -- not allowed

(Theead moved to hackers since there are correctness questions here.)

Wow, I dug into this and found even more problems.  First, your examples
in master return what you reported:

	CREATE TYPE complex AS (real float8, imag float8);

	CREATE DOMAIN d1 AS complex NOT NULL;
	CREATE DOMAIN d2 AS complex CHECK (VALUE IS NOT NULL);

-->	SELECT '(,)'::d1; -- allowed
	 d1
	-----
	 (,)

	SELECT '(,)'::d2; -- not allowed
	ERROR:  value for domain d2 violates check constraint "d2_check"

I added some tests without DOMAINs and found further problems,
specifically the ability to put a value that tests IS NULL as true in a
NOT NULL composite-type column, and not honoring WHERE col IS NULL
tests:

	CREATE TABLE complex_test (col complex NOT NULL);

	-- accepts NULL in a NOT NULL column
	INSERT INTO complex_test VALUES ('(,)');

	-- proof it is NULL
	SELECT col, col IS NULL FROM complex_test;
	 col | ?column?
	-----+----------
	 (,) | t
	
	-- NOT NULL column returns NULL value
	SELECT col, col IS NULL FROM complex_test WHERE col IS NOT NULL;
	 col | ?column?
	-----+----------
	 (,) | t
	
	EXPLAIN SELECT col, col IS NULL FROM complex_test WHERE col IS NOT NULL;
	                           QUERY PLAN
	-----------------------------------------------------------------
	 Seq Scan on complex_test  (cost=0.00..23.60 rows=1360 width=33)
	
	-- IS NULL does not return NULL value
	SELECT col, col IS NULL FROM complex_test WHERE col IS NULL;
	 col | ?column?
	-----+----------
	
	-- optimization in PG 17 prevents any comparison to NULL
	EXPLAIN SELECT col, col IS NULL FROM complex_test WHERE col IS NULL;
	                QUERY PLAN
	-------------------------------------------
	 Result  (cost=0.00..0.00 rows=0 width=33)
	   One-Time Filter: false

With the optimizations in PG 17 for NULL checks:

	https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-OPTIMIZER
-->	Remove IS NOT NULL restrictions from queries on NOT NULL columns and
-->	eliminate scans on NOT NULL columns if IS NULL is specified.

I see different output in pre-PG 17, so I would say this got worse in PG
17+ because I think the IS NULL and IS NOT NULL are being removed during
optimization.  Notice the IS [NOT] NULL checks that appear in the
EXPLAIN output below, but not above:


	SELECT col, col IS NULL FROM complex_test WHERE col IS NOT NULL;
	 col | ?column?
	-----+----------
	
	EXPLAIN SELECT col, col IS NULL FROM complex_test WHERE col IS NOT NULL;
	                           QUERY PLAN
	-----------------------------------------------------------------
	 Seq Scan on complex_test  (cost=0.00..23.60 rows=1353 width=33)
	   Filter: (col IS NOT NULL)
	
	SELECT col, col IS NULL FROM complex_test WHERE col IS NULL;
	 col | ?column?
	-----+----------
	 (,) | t
	
	EXPLAIN SELECT col, col IS NULL FROM complex_test WHERE col IS NULL;
	                          QUERY PLAN
	--------------------------------------------------------------
	 Seq Scan on complex_test  (cost=0.00..23.60 rows=7 width=33)
	   Filter: (col IS NULL)

I think this needs some serious research.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.