Hi,
While working on another thread related with virtual generated columns I
found that a whole-row IS NOT NULL test returns false for a row with no
null fields if the table also has a dropped column:
CREATE TABLE t (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, dropme int);
ALTER TABLE t DROP COLUMN dropme;
INSERT INTO t VALUES (1);
SELECT t, t IS NOT NULL AS is_not_null FROM t;
t | is_not_null
-------+-------------
(1,2) | f
(1 row)
The same happens with a CHECK constraint, which can't be added because
no row can satisfy it, and when it's added as NOT VALID every new row
is rejected:
ALTER TABLE t ADD CONSTRAINT wr CHECK (t IS NOT NULL);
ERROR: check constraint "wr" of relation "t" is violated by some row
With a stored generated column, or without the dropped column, it
works as expected.
EXPLAIN shows that the planner has folded the whole test to a
constant:
EXPLAIN (VERBOSE, COSTS OFF) SELECT t IS NOT NULL FROM t;
QUERY PLAN
--------------------------
Seq Scan on public.t
Output: false
(2 rows)
IIUC what happens is that to expand the virtual column the whole-row
Var is turned into a RowExpr, and since this is a named rowtype
expandRTE() includes a null int4 Const as placeholder for the dropped
column. Then eval_const_expressions() breaks ROW(...) IS [NOT] NULL
into tests on each field, sees the null constant and refutes the
IS NOT NULL. The executor doesn't have this problem because
ExecEvalRowNullInt() skips dropped attributes, so the result depends on
whether the test was simplified at plan time or not.
Virtual generated columns are just the easiest way to reach this. The
same placeholder is added by coerce_record_to_complex() when casting a
row to a named rowtype, so this is also wrong on master without any
generated column involved:
CREATE TABLE ts (a int, dropme int, b int);
ALTER TABLE ts DROP COLUMN dropme;
EXPLAIN (VERBOSE, COSTS OFF) SELECT ROW(a, b)::ts IS NOT NULL FROM ts;
QUERY PLAN
-----------------------
Seq Scan on public.ts
Output: false
(2 rows)
The attached patch fixes this by looking up the rowtype's tuple
descriptor when simplifying a NullTest on a RowExpr of a named
composite type and skipping the fields that correspond to dropped
columns, which matches what the executor does. With the patch the
EXPLAIN above shows ((a IS NOT NULL) AND (b IS NOT NULL)). I've added
regression tests on generated_virtual.sql and rowtypes.sql.
The folding code is old, so the ROW()::type case is not new. I've
checked that the EXPLAIN above also shows "Output: false" on PG 14, so I
think that all supported branches are affected and this should be
backpatched. The virtual generated column case only applies to 18 and
master, so perhaps for 17 and older we need a different fix.
Thoughts?
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From 4564b4d26b8b923bed560a65f7b617ecf7c990a9 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Tue, 20 Oct 2026 11:37:58 -0300
Subject: [PATCH v1] Ignore dropped columns when simplifying ROW(...) IS [NOT]
NULL
eval_const_expressions() breaks a NullTest on a RowExpr into separate
tests on its fields, and a null constant field refutes IS NOT NULL
outright. However, a RowExpr of a named composite type contains a
placeholder, typically a null int4 constant, for each dropped column of
the rowtype. The planner treated that placeholder as a genuinely null
field, so ROW(...)::sometype IS NOT NULL was simplified to constant
false whenever the type had a dropped column, even when all real fields
were non-null. The executor's ExecEvalRowNullInt() already skips dropped
attributes, so the result depended on whether the test was simplified at
plan time.
This could be reached by casting a row to a named rowtype, and more
visibly through virtual generated columns, whose expansion turns a
whole-row reference into a RowExpr including the dropped-column
placeholders. There, "tbl IS NOT NULL" returned false for every row, and
a CHECK constraint like that could neither be added nor, when added as
NOT VALID, be satisfied by any new row.
Fix by consulting the rowtype's tuple descriptor and skipping fields
that correspond to dropped columns, matching the executor's behavior.
---
src/backend/optimizer/util/clauses.c | 33 +++++++++++++++++--
.../regress/expected/generated_virtual.out | 24 ++++++++++++++
src/test/regress/expected/rowtypes.out | 22 +++++++++++++
src/test/regress/sql/generated_virtual.sql | 12 +++++++
src/test/regress/sql/rowtypes.sql | 11 +++++++
5 files changed, 100 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index 95134bfeda3..521a4c0f94d 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4034,11 +4034,28 @@ eval_const_expressions_mutator(Node *node,
*/
RowExpr *rarg = (RowExpr *) arg;
List *newargs = NIL;
+ TupleDesc tupdesc = NULL;
+ bool refuted = false;
ListCell *l;
+ /*
+ * A RowExpr of a named composite type
has a placeholder
+ * for each dropped column. Those must
be ignored, as
+ * ExecEvalRowNullInt() does, so we
need the tuple
+ * descriptor to identify them.
+ */
+ if (rarg->row_typeid != RECORDOID)
+ tupdesc =
lookup_rowtype_tupdesc(rarg->row_typeid, -1);
+
foreach(l, rarg->args)
{
Node *relem = (Node *)
lfirst(l);
+ int i =
foreach_current_index(l);
+
+ /* Ignore dropped columns */
+ if (tupdesc && i <
tupdesc->natts &&
+
TupleDescCompactAttr(tupdesc, i)->attisdropped)
+ continue;
/*
* A constant field refutes the
whole NullTest if it's
@@ -4051,7 +4068,10 @@ eval_const_expressions_mutator(Node *node,
if (carg->constisnull ?
(ntest->nulltesttype == IS_NOT_NULL) :
(ntest->nulltesttype == IS_NULL))
- return
makeBoolConst(false, false);
+ {
+ refuted = true;
+ break;
+ }
continue;
}
@@ -4065,7 +4085,10 @@ eval_const_expressions_mutator(Node *node,
NOTNULL_SOURCE_HASHTABLE))
{
if (ntest->nulltesttype
== IS_NULL)
- return
makeBoolConst(false, false);
+ {
+ refuted = true;
+ break;
+ }
continue;
}
@@ -4082,6 +4105,12 @@ eval_const_expressions_mutator(Node *node,
newntest->location =
ntest->location;
newargs = lappend(newargs,
newntest);
}
+
+ if (tupdesc)
+ ReleaseTupleDesc(tupdesc);
+
+ if (refuted)
+ return makeBoolConst(false,
false);
/* If all the inputs were constants,
result is TRUE */
if (newargs == NIL)
return makeBoolConst(true,
false);
diff --git a/src/test/regress/expected/generated_virtual.out
b/src/test/regress/expected/generated_virtual.out
index b2f8e16e63b..c04cead7d93 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -696,6 +696,30 @@ ERROR: new row for relation "gtest20c" violates check
constraint "whole_row_che
DETAIL: Failing row contains (null, virtual).
ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); --
violates constraint
ERROR: check constraint "whole_row_check" of relation "gtest20c" is violated
by some row
+-- whole-row reference with a dropped column: the NULL placeholder for the
+-- dropped column must not make the row test as null
+CREATE TABLE gtest20e (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c
int);
+ALTER TABLE gtest20e DROP COLUMN c;
+INSERT INTO gtest20e VALUES (1);
+SELECT gtest20e, gtest20e IS NOT NULL AS nn, gtest20e IS NULL AS n FROM
gtest20e;
+ gtest20e | nn | n
+----------+----+---
+ (1,2) | t | f
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT gtest20e IS NOT NULL FROM gtest20e;
+ QUERY PLAN
+-------------------------------------------------------
+ Seq Scan on generated_virtual_tests.gtest20e
+ Output: ((a IS NOT NULL) AND ((a * 2) IS NOT NULL))
+(2 rows)
+
+ALTER TABLE gtest20e ADD CONSTRAINT whole_row_check CHECK (gtest20e IS NOT
NULL); -- ok
+INSERT INTO gtest20e VALUES (2); -- ok
+INSERT INTO gtest20e VALUES (NULL); -- fails
+ERROR: new row for relation "gtest20e" violates check constraint
"whole_row_check"
+DETAIL: Failing row contains (null, virtual).
-- index with whole-row reference needs rebuild
CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO gtest20d VALUES (1), (1);
diff --git a/src/test/regress/expected/rowtypes.out
b/src/test/regress/expected/rowtypes.out
index 956bc2d02fc..c3ef32cf772 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -538,6 +538,28 @@ select ROW() = ROW();
ERROR: cannot compare rows of zero length
LINE 1: select ROW() = ROW();
^
+-- IS [NOT] NULL on a named rowtype with a dropped column must ignore the
+-- dropped column's placeholder, also when simplified by the planner
+create temp table rowdropped (a int, dropme int, b int);
+alter table rowdropped drop column dropme;
+insert into rowdropped values (1, 2), (1, null);
+explain (verbose, costs off)
+select row(a, b)::rowdropped is not null from rowdropped;
+ QUERY PLAN
+-------------------------------------------------
+ Seq Scan on pg_temp.rowdropped
+ Output: ((a IS NOT NULL) AND (b IS NOT NULL))
+(2 rows)
+
+select row(a, b)::rowdropped is not null as nn,
+ row(a, b)::rowdropped is null as n
+from rowdropped;
+ nn | n
+----+---
+ t | f
+ f | f
+(2 rows)
+
-- Check ability to create arrays of anonymous rowtypes
select array[ row(1,2), row(3,4), row(5,6) ];
array
diff --git a/src/test/regress/sql/generated_virtual.sql
b/src/test/regress/sql/generated_virtual.sql
index c4c4f3d5ef1..cbbf154739b 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -353,6 +353,18 @@ INSERT INTO gtest20c VALUES (1); -- ok
INSERT INTO gtest20c VALUES (NULL); -- fails
ALTER TABLE gtest20c ALTER COLUMN b SET EXPRESSION AS (NULL::int); --
violates constraint
+-- whole-row reference with a dropped column: the NULL placeholder for the
+-- dropped column must not make the row test as null
+CREATE TABLE gtest20e (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c
int);
+ALTER TABLE gtest20e DROP COLUMN c;
+INSERT INTO gtest20e VALUES (1);
+SELECT gtest20e, gtest20e IS NOT NULL AS nn, gtest20e IS NULL AS n FROM
gtest20e;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT gtest20e IS NOT NULL FROM gtest20e;
+ALTER TABLE gtest20e ADD CONSTRAINT whole_row_check CHECK (gtest20e IS NOT
NULL); -- ok
+INSERT INTO gtest20e VALUES (2); -- ok
+INSERT INTO gtest20e VALUES (NULL); -- fails
+
-- index with whole-row reference needs rebuild
CREATE TABLE gtest20d (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
INSERT INTO gtest20d VALUES (1), (1);
diff --git a/src/test/regress/sql/rowtypes.sql
b/src/test/regress/sql/rowtypes.sql
index 174b062144a..a8d2255e21b 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -201,6 +201,17 @@ select ROW();
select ROW() IS NULL;
select ROW() = ROW();
+-- IS [NOT] NULL on a named rowtype with a dropped column must ignore the
+-- dropped column's placeholder, also when simplified by the planner
+create temp table rowdropped (a int, dropme int, b int);
+alter table rowdropped drop column dropme;
+insert into rowdropped values (1, 2), (1, null);
+explain (verbose, costs off)
+select row(a, b)::rowdropped is not null from rowdropped;
+select row(a, b)::rowdropped is not null as nn,
+ row(a, b)::rowdropped is null as n
+from rowdropped;
+
-- Check ability to create arrays of anonymous rowtypes
select array[ row(1,2), row(3,4), row(5,6) ];
--
2.50.1 (Apple Git-155)
