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 <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
