Hi hackers,
When ruleutils.c prints an operator between two ROW() constructors, or
between a ROW() and a scalar subquery on its right, with a bare ROW(),
the parser reads it back as a row comparison, not as an operator on
record values. A view, rule or stored expression using it then
silently gives a different result after dump and restore, or does not
restore at all. The two attached patches add tests and fix it.
The problem
-----------
The parser reads "ROW(..) op ROW(..)" as a column-by-column row
comparison, and "ROW(..) op (SELECT ..)" as a row comparison sublink
(transformAExprOp). Casting a ROW() to record makes the record
operator apply, but the cast does nothing and leaves no trace in the
tree. The output comes without it and reads back as a row comparison,
which treats NULLs differently:
create view v as
select row(1, null::int)::record = row(1, null::int)::record
as r;
select r from v; -- t
before: SELECT (ROW(1, NULL::integer) =
ROW(1, NULL::integer)) AS r;
read back, r is NULL
after: SELECT (ROW(1, NULL::integer)::record =
ROW(1, NULL::integer)::record) AS r;
Other cases do not read back at all:
row(1, 2)::record *= row(1, 2)::record
ERROR: operator does not exist: integer *= integer
row(1, null::int)::record = (select row(1, null::int)::record)
ERROR: subquery has too few columns
The ROW() need not be written by the user. A whole-row reference to a
JOIN USING alias is expanded into a ROW(), and so is a whole-row
reference to a subquery passed to an operator on a named composite
type, which is coerced to that type:
select j.* = (select row(1))
from a join b using (x) as j;
before: ROW(a.x) = ( SELECT ROW(1) AS "row")
ERROR: operator does not exist: integer = record
after: ROW(a.x)::record = ( SELECT ROW(1) AS "row")
create operator =#= (leftarg = t, rightarg = t, function = t_eq);
select s1 =#= s2 from (select 1 as a, null::int as b) s1,
(select 1, null::int) s2;
before: ROW(s1.a, s1.b) =#= ROW(s2."?column?", s2.int4)
ERROR: operator does not exist: integer =#= integer
after: ROW(s1.a, s1.b)::t =#= ROW(s2."?column?", s2.int4)::t
pg_dump prints with the same code. With the first example in a view,
a generated column and a BEGIN ATOMIC function body, a dump and
restore turned all three from true to NULL without an error. The
other examples do not restore.
The fix
-------
0002 makes get_oper_expr() print a cast after each ROW() that has no
explicit cast, when the left operand is such a ROW() and the right one
is a ROW() or a scalar subquery. The raw parse tree then has a
TypeCast there, which the parser does not treat specially. The cast
is to the type of the ROW(), so it is ::t in the =#= example above.
An explicit cast to a named type is already printed, so nothing is
added then. Other comparisons with a ROW() on one side only, such as
ROW(1, 2) = '(1,2)'::t, are unchanged. IS DISTINCT FROM and IN also
compare two ROW()s column by column, but the parser does so after
transforming the operands, so the stored expression is already
column-by-column and reads back the same. = ANY and NULLIF are not
special for ROW()s. None of these is changed.
The change also shows in EXPLAIN. In the ON CONFLICT filter in
updatable_views, a comparison between whole-row references to an
updatable view is now shown as ROW(...)::uv_iocu_view =
ROW(...)::uv_iocu_view.
Tests
-----
0001 adds the tests with the output before the fix, and 0002 updates
it, so that the diff shows what changes. For each case above, a view
is created and created again from its pg_get_viewdef() output, and
both are queried where the query can run, so that the expected output
shows whether the results agree. A plain row comparison and a ROW()
with an explicit cast to a named type are checked to keep their
output. The views are left in place, so that the dump and restore
comparison in the pg_upgrade test covers the fix. So that the tests
pass at each step, 0001 drops the views that do not survive dump and
restore yet, and 0002 removes that DROP.
Back branches
-------------
I think this should be back-patched to 15. I applied both patches to
REL_15_STABLE through REL_19_STABLE. The updatable_views.out change
applies only to 19 and master, which have that test (ON CONFLICT DO
SELECT), and has to be dropped on 15-18. Otherwise the patches apply
as they are, and make check passes on all branches.
This came out of an AI-assisted audit of ruleutils.c. I will post the
other problems it found as separate, independent reports as I work
through them.
Best regards,
Henson
From 19a89154b8557983ade9a36d78a855ce881770ab Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 2 Oct 2026 22:35:37 +0900
Subject: [PATCH v1 1/2] Add tests for deparsing record comparisons of ROW()s
A comparison of two ROW() constructors through a record operator,
such as record_eq, is read back by the parser as a row comparison,
which compares column by column and gives NULL where the record
operator gives true or false. A record operator without a
column-by-column counterpart, such as *=, rows of different lengths,
and a ROW() compared with a scalar subquery cannot be read back at
all, and neither can a whole-row reference to a subquery that is
coerced to a named composite type for a user-defined operator.
Add views with each of these, and recreate them from their
pg_get_viewdef() output. Also test that a plain row comparison and a
ROW() with an explicit cast to a named type keep their output. The
views that already survive dump and restore are left in place for the
pg_upgrade test.
The expected output shows the behavior before the fix. The next
commit fixes it and updates the output.
---
src/test/regress/expected/rowtypes.out | 215 +++++++++++++++++++++++++
src/test/regress/sql/rowtypes.sql | 94 +++++++++++
2 files changed, 309 insertions(+)
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 956bc2d02fc..09133083af1 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1408,3 +1408,218 @@ ERROR: column "oid" not found in data type compositetable
LINE 1: SELECT (NULL::compositetable).oid;
^
DROP TABLE compositetable;
+-- A comparison of two ROW() constructors that is a record comparison, not a
+-- row comparison, needs its ::record casts to survive deparsing
+CREATE VIEW rowtypes_cmp_v AS
+SELECT (ROW(1, NULL::int)::record = ROW(1, NULL::int)::record) AS r;
+SELECT * FROM rowtypes_cmp_v;
+ r
+---
+ t
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v'::regclass);
+ pg_get_viewdef
+---------------------------------------------------------------
+ SELECT (ROW(1, NULL::integer) = ROW(1, NULL::integer)) AS r;
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v_re AS :def
+SELECT * FROM rowtypes_cmp_v_re;
+ r
+---
+
+(1 row)
+
+-- The other operators of a record comparison need the casts as well
+CREATE VIEW rowtypes_cmp_v2 AS
+SELECT (ROW(1, NULL::int)::record <= ROW(1, NULL::int)::record) AS le,
+ (ROW(1, NULL::int)::record <> ROW(1, NULL::int)::record) AS ne;
+SELECT * FROM rowtypes_cmp_v2;
+ le | ne
+----+----
+ t | f
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v2'::regclass);
+ pg_get_viewdef
+-----------------------------------------------------------------
+ SELECT (ROW(1, NULL::integer) <= ROW(1, NULL::integer)) AS le,+
+ (ROW(1, NULL::integer) <> ROW(1, NULL::integer)) AS ne;
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v2'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v2_re AS :def
+SELECT * FROM rowtypes_cmp_v2_re;
+ le | ne
+----+----
+ |
+(1 row)
+
+-- A row comparison is split by the parser into column-by-column operators,
+-- so there is no record operator to cast
+CREATE VIEW rowtypes_cmp_v3 AS
+SELECT (ROW(1, NULL::int) = ROW(1, NULL::int)) AS eq,
+ (ROW(1, NULL::int) <> ROW(1, NULL::int)) AS ne,
+ (ROW(1, 2) < ROW(1, 3)) AS lt;
+SELECT * FROM rowtypes_cmp_v3;
+ eq | ne | lt
+----+----+----
+ | | t
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v3'::regclass);
+ pg_get_viewdef
+--------------------------------------------------------------
+ SELECT ((1 = 1) AND (NULL::integer = NULL::integer)) AS eq,+
+ ((1 <> 1) OR (NULL::integer <> NULL::integer)) AS ne, +
+ (ROW(1, 2) < ROW(1, 3)) AS lt;
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v3'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v3_re AS :def
+SELECT * FROM rowtypes_cmp_v3_re;
+ eq | ne | lt
+----+----+----
+ | | t
+(1 row)
+
+-- A record operator with no column-by-column form, and rows of different
+-- lengths, could not be read back at all without the casts
+CREATE VIEW rowtypes_cmp_v4 AS
+SELECT (ROW(1, 2)::record *= ROW(1, 2)::record) AS img,
+ (ROW(1)::record = ROW(1, 2)::record) AS len;
+SELECT pg_get_viewdef('rowtypes_cmp_v4'::regclass);
+ pg_get_viewdef
+------------------------------------------
+ SELECT (ROW(1, 2) *= ROW(1, 2)) AS img,+
+ (ROW(1) = ROW(1, 2)) AS len;
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v4'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v4_re AS :def
+ERROR: operator does not exist: integer *= integer
+LINE 1: ...ATE VIEW rowtypes_cmp_v4_re AS SELECT (ROW(1, 2) *= ROW(1, ...
+ ^
+DETAIL: No operator of that name accepts the given argument types.
+HINT: You might need to add explicit type casts.
+SELECT img FROM rowtypes_cmp_v4_re;
+ERROR: relation "rowtypes_cmp_v4_re" does not exist
+LINE 1: SELECT img FROM rowtypes_cmp_v4_re;
+ ^
+-- A ROW() compared with a scalar subquery would be read back as a row
+-- comparison with the subquery's columns
+CREATE VIEW rowtypes_cmp_v5 AS
+SELECT (ROW(1, NULL::int)::record = (SELECT ROW(1, NULL::int)::record)) AS r;
+SELECT * FROM rowtypes_cmp_v5;
+ r
+---
+ t
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v5'::regclass);
+ pg_get_viewdef
+----------------------------------------------------------------------------------
+ SELECT (ROW(1, NULL::integer) = ( SELECT ROW(1, NULL::integer) AS "row")) AS r;
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v5'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v5_re AS :def
+ERROR: subquery has too few columns
+LINE 1: ...types_cmp_v5_re AS SELECT (ROW(1, NULL::integer) = ( SELECT...
+ ^
+SELECT * FROM rowtypes_cmp_v5_re;
+ERROR: relation "rowtypes_cmp_v5_re" does not exist
+LINE 1: SELECT * FROM rowtypes_cmp_v5_re;
+ ^
+-- So would a whole-row reference to a join alias, which is expanded into a
+-- ROW()
+CREATE TABLE rowtypes_cmp_a (x int);
+CREATE TABLE rowtypes_cmp_b (x int);
+INSERT INTO rowtypes_cmp_a VALUES (1), (2);
+INSERT INTO rowtypes_cmp_b VALUES (1), (2);
+CREATE VIEW rowtypes_cmp_v6 AS
+SELECT x, (j.* = (SELECT ROW(1))) AS r
+ FROM rowtypes_cmp_a JOIN rowtypes_cmp_b USING (x) AS j;
+SELECT * FROM rowtypes_cmp_v6 ORDER BY x;
+ x | r
+---+---
+ 1 | t
+ 2 | f
+(2 rows)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v6'::regclass);
+ pg_get_viewdef
+--------------------------------------------------------------
+ SELECT rowtypes_cmp_a.x, +
+ (ROW(rowtypes_cmp_a.x) = ( SELECT ROW(1) AS "row")) AS r+
+ FROM (rowtypes_cmp_a +
+ JOIN rowtypes_cmp_b USING (x) AS j);
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v6'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v6_re AS :def
+ERROR: operator does not exist: integer = record
+LINE 2: (ROW(rowtypes_cmp_a.x) = ( SELECT ROW(1) AS "row")) AS r
+ ^
+DETAIL: No operator of that name accepts the given argument types.
+HINT: You might need to add explicit type casts.
+SELECT * FROM rowtypes_cmp_v6_re ORDER BY x;
+ERROR: relation "rowtypes_cmp_v6_re" does not exist
+LINE 1: SELECT * FROM rowtypes_cmp_v6_re ORDER BY x;
+ ^
+-- A whole-row reference to a subquery is coerced to a named composite type
+-- by expanding it into a ROW(), which needs the type's name to be read back
+CREATE TYPE rowtypes_cmp_t AS (a int, b int);
+CREATE FUNCTION rowtypes_cmp_eq(rowtypes_cmp_t, rowtypes_cmp_t) RETURNS bool
+ LANGUAGE sql IMMUTABLE
+ AS 'SELECT $1.a IS NOT DISTINCT FROM $2.a AND $1.b IS NOT DISTINCT FROM $2.b';
+CREATE OPERATOR =#= (LEFTARG = rowtypes_cmp_t, RIGHTARG = rowtypes_cmp_t,
+ FUNCTION = rowtypes_cmp_eq);
+CREATE VIEW rowtypes_cmp_v7 AS
+SELECT (s1 =#= s2) AS r
+ FROM (SELECT 1 AS a, NULL::int AS b) s1, (SELECT 1, NULL::int) s2;
+SELECT * FROM rowtypes_cmp_v7;
+ r
+---
+ t
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v7'::regclass);
+ pg_get_viewdef
+----------------------------------------------------------------
+ SELECT (ROW(s1.a, s1.b) =#= ROW(s2."?column?", s2.int4)) AS r+
+ FROM ( SELECT 1 AS a, +
+ NULL::integer AS b) s1, +
+ ( SELECT 1 AS "?column?", +
+ NULL::integer AS int4) s2;
+(1 row)
+
+SELECT pg_get_viewdef('rowtypes_cmp_v7'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v7_re AS :def
+ERROR: operator does not exist: integer =#= integer
+LINE 1: ...EW rowtypes_cmp_v7_re AS SELECT (ROW(s1.a, s1.b) =#= ROW(s2...
+ ^
+DETAIL: No operator of that name accepts the given argument types.
+HINT: You might need to add explicit type casts.
+SELECT * FROM rowtypes_cmp_v7_re;
+ERROR: relation "rowtypes_cmp_v7_re" does not exist
+LINE 1: SELECT * FROM rowtypes_cmp_v7_re;
+ ^
+-- An explicit cast to a named type is printed once, and needs no cast on the
+-- other operand; nor does a ROW() whose other operand is not a ROW()
+CREATE VIEW rowtypes_cmp_v8 AS
+SELECT (ROW(1, 2)::rowtypes_cmp_t = ROW(1, 2)) AS named,
+ (ROW(1, 2) = '(1,2)'::rowtypes_cmp_t) AS one;
+SELECT pg_get_viewdef('rowtypes_cmp_v8'::regclass);
+ pg_get_viewdef
+-----------------------------------------------------------
+ SELECT (ROW(1, 2)::rowtypes_cmp_t = ROW(1, 2)) AS named,+
+ (ROW(1, 2) = '(1,2)'::rowtypes_cmp_t) AS one;
+(1 row)
+
+-- leave the views around for the pg_upgrade test, except the ones that do
+-- not survive dump and restore yet
+DROP VIEW rowtypes_cmp_v, rowtypes_cmp_v2, rowtypes_cmp_v4, rowtypes_cmp_v5,
+ rowtypes_cmp_v6, rowtypes_cmp_v7;
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index 174b062144a..40fe495a48f 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -562,3 +562,97 @@ SELECT (NULL::compositetable).a;
SELECT (NULL::compositetable).oid;
DROP TABLE compositetable;
+
+-- A comparison of two ROW() constructors that is a record comparison, not a
+-- row comparison, needs its ::record casts to survive deparsing
+CREATE VIEW rowtypes_cmp_v AS
+SELECT (ROW(1, NULL::int)::record = ROW(1, NULL::int)::record) AS r;
+SELECT * FROM rowtypes_cmp_v;
+SELECT pg_get_viewdef('rowtypes_cmp_v'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v_re AS :def
+SELECT * FROM rowtypes_cmp_v_re;
+-- The other operators of a record comparison need the casts as well
+CREATE VIEW rowtypes_cmp_v2 AS
+SELECT (ROW(1, NULL::int)::record <= ROW(1, NULL::int)::record) AS le,
+ (ROW(1, NULL::int)::record <> ROW(1, NULL::int)::record) AS ne;
+SELECT * FROM rowtypes_cmp_v2;
+SELECT pg_get_viewdef('rowtypes_cmp_v2'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v2'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v2_re AS :def
+SELECT * FROM rowtypes_cmp_v2_re;
+
+-- A row comparison is split by the parser into column-by-column operators,
+-- so there is no record operator to cast
+CREATE VIEW rowtypes_cmp_v3 AS
+SELECT (ROW(1, NULL::int) = ROW(1, NULL::int)) AS eq,
+ (ROW(1, NULL::int) <> ROW(1, NULL::int)) AS ne,
+ (ROW(1, 2) < ROW(1, 3)) AS lt;
+SELECT * FROM rowtypes_cmp_v3;
+SELECT pg_get_viewdef('rowtypes_cmp_v3'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v3'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v3_re AS :def
+SELECT * FROM rowtypes_cmp_v3_re;
+
+-- A record operator with no column-by-column form, and rows of different
+-- lengths, could not be read back at all without the casts
+CREATE VIEW rowtypes_cmp_v4 AS
+SELECT (ROW(1, 2)::record *= ROW(1, 2)::record) AS img,
+ (ROW(1)::record = ROW(1, 2)::record) AS len;
+SELECT pg_get_viewdef('rowtypes_cmp_v4'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v4'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v4_re AS :def
+SELECT img FROM rowtypes_cmp_v4_re;
+
+-- A ROW() compared with a scalar subquery would be read back as a row
+-- comparison with the subquery's columns
+CREATE VIEW rowtypes_cmp_v5 AS
+SELECT (ROW(1, NULL::int)::record = (SELECT ROW(1, NULL::int)::record)) AS r;
+SELECT * FROM rowtypes_cmp_v5;
+SELECT pg_get_viewdef('rowtypes_cmp_v5'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v5'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v5_re AS :def
+SELECT * FROM rowtypes_cmp_v5_re;
+-- So would a whole-row reference to a join alias, which is expanded into a
+-- ROW()
+CREATE TABLE rowtypes_cmp_a (x int);
+CREATE TABLE rowtypes_cmp_b (x int);
+INSERT INTO rowtypes_cmp_a VALUES (1), (2);
+INSERT INTO rowtypes_cmp_b VALUES (1), (2);
+CREATE VIEW rowtypes_cmp_v6 AS
+SELECT x, (j.* = (SELECT ROW(1))) AS r
+ FROM rowtypes_cmp_a JOIN rowtypes_cmp_b USING (x) AS j;
+SELECT * FROM rowtypes_cmp_v6 ORDER BY x;
+SELECT pg_get_viewdef('rowtypes_cmp_v6'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v6'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v6_re AS :def
+SELECT * FROM rowtypes_cmp_v6_re ORDER BY x;
+
+-- A whole-row reference to a subquery is coerced to a named composite type
+-- by expanding it into a ROW(), which needs the type's name to be read back
+CREATE TYPE rowtypes_cmp_t AS (a int, b int);
+CREATE FUNCTION rowtypes_cmp_eq(rowtypes_cmp_t, rowtypes_cmp_t) RETURNS bool
+ LANGUAGE sql IMMUTABLE
+ AS 'SELECT $1.a IS NOT DISTINCT FROM $2.a AND $1.b IS NOT DISTINCT FROM $2.b';
+CREATE OPERATOR =#= (LEFTARG = rowtypes_cmp_t, RIGHTARG = rowtypes_cmp_t,
+ FUNCTION = rowtypes_cmp_eq);
+CREATE VIEW rowtypes_cmp_v7 AS
+SELECT (s1 =#= s2) AS r
+ FROM (SELECT 1 AS a, NULL::int AS b) s1, (SELECT 1, NULL::int) s2;
+SELECT * FROM rowtypes_cmp_v7;
+SELECT pg_get_viewdef('rowtypes_cmp_v7'::regclass);
+SELECT pg_get_viewdef('rowtypes_cmp_v7'::regclass) AS def \gset
+CREATE VIEW rowtypes_cmp_v7_re AS :def
+SELECT * FROM rowtypes_cmp_v7_re;
+
+-- An explicit cast to a named type is printed once, and needs no cast on the
+-- other operand; nor does a ROW() whose other operand is not a ROW()
+CREATE VIEW rowtypes_cmp_v8 AS
+SELECT (ROW(1, 2)::rowtypes_cmp_t = ROW(1, 2)) AS named,
+ (ROW(1, 2) = '(1,2)'::rowtypes_cmp_t) AS one;
+SELECT pg_get_viewdef('rowtypes_cmp_v8'::regclass);
+
+-- leave the views around for the pg_upgrade test, except the ones that do
+-- not survive dump and restore yet
+DROP VIEW rowtypes_cmp_v, rowtypes_cmp_v2, rowtypes_cmp_v4, rowtypes_cmp_v5,
+ rowtypes_cmp_v6, rowtypes_cmp_v7;
--
2.54.0 (Apple Git-157)
From 2b14cc99161363edeadc00598e558278100b714e Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 14 Oct 2026 09:29:42 +0900
Subject: [PATCH v1 2/2] Fix deparsing of operators on ROW() constructors
The parser reads "ROW(..) op ROW(..)" as a column-by-column row
comparison, and "ROW(..) op (SELECT ..)" as a row comparison sublink,
not as an operator on record values. An operator such as record_eq
between two ROW() constructors, or between a ROW() and a scalar
subquery on its right, was printed in that form, so a view, rule or
stored expression using it was read back differently after dump and
restore: the result changed silently (true or false became NULL), or
the restore failed. The ROW() can come from a cast to record, which
leaves no trace in the tree, from a whole-row reference to a JOIN
USING alias, or from coercing a whole-row reference to a named
composite type.
When the left operand is a ROW() without an explicit cast and the
right one is a ROW() or a scalar subquery, print a cast after each
such ROW(), so that the raw parse tree has a TypeCast there instead.
This also shows up in EXPLAIN output, for example for comparisons
between whole-row references to an updatable view.
Leave the rest of the test views in place for the pg_upgrade test too.
Backpatch-through: 15
---
src/backend/utils/adt/ruleutils.c | 24 ++++
src/test/regress/expected/rowtypes.out | 110 ++++++++----------
src/test/regress/expected/updatable_views.out | 6 +-
src/test/regress/sql/rowtypes.sql | 5 +-
4 files changed, 77 insertions(+), 68 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 5e8e1683db0..bc27e71dc5f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10794,13 +10794,37 @@ get_oper_expr(OpExpr *expr, deparse_context *context)
/* binary operator */
Node *arg1 = (Node *) linitial(args);
Node *arg2 = (Node *) lsecond(args);
+ bool bare1 = IsA(arg1, RowExpr) &&
+ ((RowExpr *) arg1)->row_format != COERCE_EXPLICIT_CAST;
+ bool bare2 = IsA(arg2, RowExpr) &&
+ ((RowExpr *) arg2)->row_format != COERCE_EXPLICIT_CAST;
+ bool castrows;
+
+ /*
+ * The parser reads "ROW() op ROW()" as a column-by-column row
+ * comparison, and "ROW() op (SELECT ...)" as a row comparison
+ * sublink, rather than as an operator on record values (see
+ * transformAExprOp). If the operands would print that way, print an
+ * explicit cast after each bare ROW(), so that the raw parse tree has
+ * a TypeCast there instead.
+ */
+ castrows = bare1 &&
+ (bare2 ||
+ (IsA(arg2, SubLink) &&
+ ((SubLink *) arg2)->subLinkType == EXPR_SUBLINK));
get_rule_expr_paren(arg1, context, true, (Node *) expr);
+ if (castrows)
+ appendStringInfo(buf, "::%s",
+ format_type_with_typemod(((RowExpr *) arg1)->row_typeid, -1));
appendStringInfo(buf, " %s ",
generate_operator_name(opno,
exprType(arg1),
exprType(arg2)));
get_rule_expr_paren(arg2, context, true, (Node *) expr);
+ if (castrows && bare2)
+ appendStringInfo(buf, "::%s",
+ format_type_with_typemod(((RowExpr *) arg2)->row_typeid, -1));
}
else
{
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 09133083af1..020613d06cf 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1419,9 +1419,9 @@ SELECT * FROM rowtypes_cmp_v;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v'::regclass);
- pg_get_viewdef
----------------------------------------------------------------
- SELECT (ROW(1, NULL::integer) = ROW(1, NULL::integer)) AS r;
+ pg_get_viewdef
+-------------------------------------------------------------------------------
+ SELECT (ROW(1, NULL::integer)::record = ROW(1, NULL::integer)::record) AS r;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v'::regclass) AS def \gset
@@ -1429,7 +1429,7 @@ CREATE VIEW rowtypes_cmp_v_re AS :def
SELECT * FROM rowtypes_cmp_v_re;
r
---
-
+ t
(1 row)
-- The other operators of a record comparison need the casts as well
@@ -1443,10 +1443,10 @@ SELECT * FROM rowtypes_cmp_v2;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v2'::regclass);
- pg_get_viewdef
------------------------------------------------------------------
- SELECT (ROW(1, NULL::integer) <= ROW(1, NULL::integer)) AS le,+
- (ROW(1, NULL::integer) <> ROW(1, NULL::integer)) AS ne;
+ pg_get_viewdef
+---------------------------------------------------------------------------------
+ SELECT (ROW(1, NULL::integer)::record <= ROW(1, NULL::integer)::record) AS le,+
+ (ROW(1, NULL::integer)::record <> ROW(1, NULL::integer)::record) AS ne;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v2'::regclass) AS def \gset
@@ -1454,7 +1454,7 @@ CREATE VIEW rowtypes_cmp_v2_re AS :def
SELECT * FROM rowtypes_cmp_v2_re;
le | ne
----+----
- |
+ t | f
(1 row)
-- A row comparison is split by the parser into column-by-column operators,
@@ -1491,23 +1491,20 @@ CREATE VIEW rowtypes_cmp_v4 AS
SELECT (ROW(1, 2)::record *= ROW(1, 2)::record) AS img,
(ROW(1)::record = ROW(1, 2)::record) AS len;
SELECT pg_get_viewdef('rowtypes_cmp_v4'::regclass);
- pg_get_viewdef
-------------------------------------------
- SELECT (ROW(1, 2) *= ROW(1, 2)) AS img,+
- (ROW(1) = ROW(1, 2)) AS len;
+ pg_get_viewdef
+----------------------------------------------------------
+ SELECT (ROW(1, 2)::record *= ROW(1, 2)::record) AS img,+
+ (ROW(1)::record = ROW(1, 2)::record) AS len;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v4'::regclass) AS def \gset
CREATE VIEW rowtypes_cmp_v4_re AS :def
-ERROR: operator does not exist: integer *= integer
-LINE 1: ...ATE VIEW rowtypes_cmp_v4_re AS SELECT (ROW(1, 2) *= ROW(1, ...
- ^
-DETAIL: No operator of that name accepts the given argument types.
-HINT: You might need to add explicit type casts.
SELECT img FROM rowtypes_cmp_v4_re;
-ERROR: relation "rowtypes_cmp_v4_re" does not exist
-LINE 1: SELECT img FROM rowtypes_cmp_v4_re;
- ^
+ img
+-----
+ t
+(1 row)
+
-- A ROW() compared with a scalar subquery would be read back as a row
-- comparison with the subquery's columns
CREATE VIEW rowtypes_cmp_v5 AS
@@ -1519,20 +1516,19 @@ SELECT * FROM rowtypes_cmp_v5;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v5'::regclass);
- pg_get_viewdef
-----------------------------------------------------------------------------------
- SELECT (ROW(1, NULL::integer) = ( SELECT ROW(1, NULL::integer) AS "row")) AS r;
+ pg_get_viewdef
+------------------------------------------------------------------------------------------
+ SELECT (ROW(1, NULL::integer)::record = ( SELECT ROW(1, NULL::integer) AS "row")) AS r;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v5'::regclass) AS def \gset
CREATE VIEW rowtypes_cmp_v5_re AS :def
-ERROR: subquery has too few columns
-LINE 1: ...types_cmp_v5_re AS SELECT (ROW(1, NULL::integer) = ( SELECT...
- ^
SELECT * FROM rowtypes_cmp_v5_re;
-ERROR: relation "rowtypes_cmp_v5_re" does not exist
-LINE 1: SELECT * FROM rowtypes_cmp_v5_re;
- ^
+ r
+---
+ t
+(1 row)
+
-- So would a whole-row reference to a join alias, which is expanded into a
-- ROW()
CREATE TABLE rowtypes_cmp_a (x int);
@@ -1550,25 +1546,23 @@ SELECT * FROM rowtypes_cmp_v6 ORDER BY x;
(2 rows)
SELECT pg_get_viewdef('rowtypes_cmp_v6'::regclass);
- pg_get_viewdef
---------------------------------------------------------------
- SELECT rowtypes_cmp_a.x, +
- (ROW(rowtypes_cmp_a.x) = ( SELECT ROW(1) AS "row")) AS r+
- FROM (rowtypes_cmp_a +
+ pg_get_viewdef
+----------------------------------------------------------------------
+ SELECT rowtypes_cmp_a.x, +
+ (ROW(rowtypes_cmp_a.x)::record = ( SELECT ROW(1) AS "row")) AS r+
+ FROM (rowtypes_cmp_a +
JOIN rowtypes_cmp_b USING (x) AS j);
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v6'::regclass) AS def \gset
CREATE VIEW rowtypes_cmp_v6_re AS :def
-ERROR: operator does not exist: integer = record
-LINE 2: (ROW(rowtypes_cmp_a.x) = ( SELECT ROW(1) AS "row")) AS r
- ^
-DETAIL: No operator of that name accepts the given argument types.
-HINT: You might need to add explicit type casts.
SELECT * FROM rowtypes_cmp_v6_re ORDER BY x;
-ERROR: relation "rowtypes_cmp_v6_re" does not exist
-LINE 1: SELECT * FROM rowtypes_cmp_v6_re ORDER BY x;
- ^
+ x | r
+---+---
+ 1 | t
+ 2 | f
+(2 rows)
+
-- A whole-row reference to a subquery is coerced to a named composite type
-- by expanding it into a ROW(), which needs the type's name to be read back
CREATE TYPE rowtypes_cmp_t AS (a int, b int);
@@ -1587,26 +1581,23 @@ SELECT * FROM rowtypes_cmp_v7;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v7'::regclass);
- pg_get_viewdef
-----------------------------------------------------------------
- SELECT (ROW(s1.a, s1.b) =#= ROW(s2."?column?", s2.int4)) AS r+
- FROM ( SELECT 1 AS a, +
- NULL::integer AS b) s1, +
- ( SELECT 1 AS "?column?", +
+ pg_get_viewdef
+------------------------------------------------------------------------------------------------
+ SELECT (ROW(s1.a, s1.b)::rowtypes_cmp_t =#= ROW(s2."?column?", s2.int4)::rowtypes_cmp_t) AS r+
+ FROM ( SELECT 1 AS a, +
+ NULL::integer AS b) s1, +
+ ( SELECT 1 AS "?column?", +
NULL::integer AS int4) s2;
(1 row)
SELECT pg_get_viewdef('rowtypes_cmp_v7'::regclass) AS def \gset
CREATE VIEW rowtypes_cmp_v7_re AS :def
-ERROR: operator does not exist: integer =#= integer
-LINE 1: ...EW rowtypes_cmp_v7_re AS SELECT (ROW(s1.a, s1.b) =#= ROW(s2...
- ^
-DETAIL: No operator of that name accepts the given argument types.
-HINT: You might need to add explicit type casts.
SELECT * FROM rowtypes_cmp_v7_re;
-ERROR: relation "rowtypes_cmp_v7_re" does not exist
-LINE 1: SELECT * FROM rowtypes_cmp_v7_re;
- ^
+ r
+---
+ t
+(1 row)
+
-- An explicit cast to a named type is printed once, and needs no cast on the
-- other operand; nor does a ROW() whose other operand is not a ROW()
CREATE VIEW rowtypes_cmp_v8 AS
@@ -1619,7 +1610,4 @@ SELECT pg_get_viewdef('rowtypes_cmp_v8'::regclass);
(ROW(1, 2) = '(1,2)'::rowtypes_cmp_t) AS one;
(1 row)
--- leave the views around for the pg_upgrade test, except the ones that do
--- not survive dump and restore yet
-DROP VIEW rowtypes_cmp_v, rowtypes_cmp_v2, rowtypes_cmp_v4, rowtypes_cmp_v5,
- rowtypes_cmp_v6, rowtypes_cmp_v7;
+-- leave the views around for the pg_upgrade test
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 9cea538b8e8..8e51ef5464f 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3756,12 +3756,12 @@ select * from uv_iocu_view;
explain (costs off)
insert into uv_iocu_view (aa,bb) values (1,'Rejected: (y,1,"(1,y)")')
on conflict (aa) do select where uv_iocu_view.* = excluded.* returning *;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on uv_iocu_tab
Conflict Resolution: SELECT
Conflict Arbiter Indexes: uv_iocu_tab_a_key
- Conflict Filter: (ROW(uv_iocu_tab.b, uv_iocu_tab.a, (uv_iocu_tab.*)::text) = ROW(excluded.b, excluded.a, (excluded.*)::text))
+ Conflict Filter: (ROW(uv_iocu_tab.b, uv_iocu_tab.a, (uv_iocu_tab.*)::text)::uv_iocu_view = ROW(excluded.b, excluded.a, (excluded.*)::text)::uv_iocu_view)
-> Result
(5 rows)
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index 40fe495a48f..087c0a8cff8 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -652,7 +652,4 @@ SELECT (ROW(1, 2)::rowtypes_cmp_t = ROW(1, 2)) AS named,
(ROW(1, 2) = '(1,2)'::rowtypes_cmp_t) AS one;
SELECT pg_get_viewdef('rowtypes_cmp_v8'::regclass);
--- leave the views around for the pg_upgrade test, except the ones that do
--- not survive dump and restore yet
-DROP VIEW rowtypes_cmp_v, rowtypes_cmp_v2, rowtypes_cmp_v4, rowtypes_cmp_v5,
- rowtypes_cmp_v6, rowtypes_cmp_v7;
+-- leave the views around for the pg_upgrade test
--
2.54.0 (Apple Git-157)
