agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: 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)
Date: Wed, 8 Jan 2025 16:10:42 -0500
Message-ID: <Z37p0paENWWUarj-@momjian.us> (raw)
In-Reply-To: <173591158454.714.7664064332419606037@wrigleys.postgresql.org>
References: <173591158454.714.7664064332419606037@wrigleys.postgresql.org>
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.
view thread (6+ messages) latest in thread
Message-ID: <Z37p0paENWWUarj-@momjian.us>
Permalink: ../Z37p0paENWWUarj-@momjian.us/
Also on: postgresql.org/message-id/Z37p0paENWWUarj-@momjian.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, pgsql-hackers@lists.postgresql.org
Subject: Re: Parameter NOT NULL to CREATE DOMAIN not the same as CHECK (VALUE IS NOT NULL)
In-Reply-To: <Z37p0paENWWUarj-@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox