[DEPARSE] Operators on ROW() constructors read back as row comparisons
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
Attachments:
t254187_1v1-0001-Add-tests-for-deparsing-record-comparisons.patchapplication/x-patch; name=v1-0001-Add-tests-for-deparsing-record-comparisons.patchDownload+309-1
v1-0002-Fix-deparsing-of-operators-on-ROW.patchapplication/x-patch; name=v1-0002-Fix-deparsing-of-operators-on-ROW.patchDownload+77-58
Henson Choi <assam258@gmail.com> writes:
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.
Indeed. I think this is a parser bug and you're trying to band-aid
around it in the wrong place. get_rule_expr() would already do the
right thing if the RowExpr had row_format == COERCE_EXPLICIT_CAST,
but it does not, which seems like it could fool more things than
only deparsing. I'd blame that on coerce_type(), which silently
does nothing if the expression already has the right type; but
it should also worry about whether the expression is correctly
labeled as to coercion format. So roughly speaking, I think
we want something that's like hide_coercion_node() but can install
a cformat other than COERCE_IMPLICIT_CAST, and then invoke that
in the coerce_type() paths that don't build a new node.
It might be easier to move the existing coerce_type() logic into
a new subroutine, and then make coerce_type() be a wrapper that
calls that subroutine and then fixes up the coercion format.
Not sure about that part.
regards, tom lane
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested
Hi Henson,
I read your report and the patch, and I agree with the analysis. A cast of a ROW() to record (or to a named composite type) is a no-op that leaves no trace in the analyzed tree, so the deparser prints the operands bare, and the parser reads "ROW(..) op ROW(..)" back as a row comparison and "ROW(..) op (SELECT ..)" as a row comparison sublink. The results then differ silently (true becomes NULL, because a row comparison treats NULLs differently) or the definition does not read back at all. I also confirmed the two ways a bare ROW() arises without the user writing one: a whole-row reference to a JOIN USING alias, and a whole-row reference coerced to a named composite type.
Two code-level checks I made:
The condition in get_oper_expr() — left operand a bare ROW(), right operand a bare ROW() or an EXPR_SUBLINK sublink — mirrors the parser's special cases in transformAExprOp() exactly, so the fix fires in precisely the cases that would be misread, and nowhere else. One-sided ROW() comparisons and explicit casts keep their current output, as the new v3 and v8 tests show.
For scope, I searched the tree for the other places that print or consume RowExprs. Genuine row comparisons are stored as RowCompareExpr with already-expanded operands, so they cannot be affected; get_oper_expr() is the only place an operator's operands are printed in this form.
Tom — on the layering question, I read the code you pointed at and you are right about the mechanism: coerce_type() returns the node unchanged when the target type already matches, without fixing up the RowExpr's coercion format, and get_rule_expr() would print the cast itself if row_format were COERCE_EXPLICIT_CAST. I do not have a strong view on which layer should own the fix. My testing below covers v1 as submitted; it shows the deparse output is correct, but it does not settle where the fix belongs. For what it is worth, I looked for other consumers of row_format that the mislabeling could fool: the other readers I found (for example in parse_clause.c) work on raw parse trees, where an explicit cast is still a TypeCast node, so I could not find a second observable symptom — but I may have missed one.
For the hands-on part I used AI assistance. The bug was reproduced on current master (e9d0600): the record-equality view deparses as SELECT (ROW(1, NULL::integer) = ROW(1, NULL::integer)) and reads back with r = NULL instead of true; the *=, subquery, JOIN USING and =#= cases fail to recreate with "operator does not exist: integer *= integer", "subquery has too few columns", "operator does not exist: integer = record" and "operator does not exist: integer =#= integer" respectively. A full pg_dump/restore of a database containing these objects showed 8 errors and, worse, the generated column and the BEGIN ATOMIC function body you mention in the report silently changed from true to NULL. With v1 applied, the same stored objects deparse with the casts (ROW(1, NULL::integer)::record, ROW(s1.a, s1.b)::rowtypes_cmp_t), every view recreates from its pg_get_viewdef() output with identical results, and the dump/restore is clean with no value changes. This was checked in both pretty mode (pg_get_viewdef) and the non-pretty form pg_dump emits. The one execution error I saw — selecting the different-width column of the *= view, "cannot compare record types with different numbers of columns" — occurs identically on the original view, so it is pre-existing executor behaviour, not a deparse effect.
make installcheck-world was run against an installed build of master plus v1 (with plperl, uuid-ossp and injection points enabled): core regression 239/239, isolation 134/134, contrib 51/51 suites, test modules 40/40 suites, and the injection_points isolation specs 18/18.
One coverage suggestion: the report names generated columns and BEGIN ATOMIC function bodies as silently corrupted stored expressions, but the tests exercise views only. I verified those two cases by hand, as described above, and they break and fix exactly as the report says; a round-trip test for at least the generated column would strengthen the suite, since it reaches the same code through pg_get_expr rather than pg_get_viewdef.
On backpatching to 15, I have no objection: as with the IS DOCUMENT fix, the current output in stable branches is unrestorable or silently wrong, which seems to justify the deparse change there, and I note the updatable_views expected-output change is dropped on 15–18 as you describe. No documentation changes are needed for this fix.
Thanks for the clear report and the round-trip tests.
Regards,
Prashant
Hi Tom,
Indeed. I think this is a parser bug and you're trying to band-aid
around it in the wrong place. get_rule_expr() would already do the
right thing if the RowExpr had row_format == COERCE_EXPLICIT_CAST,
but it does not, which seems like it could fool more things than
only deparsing.
Recording the cast in the parser fixes the case you describe, and
v2-0002 does that. It cannot fix trees that are already stored, though.
I started a server with only that patch on a data directory made before
it. A view created there with
row(1, null::int)::record = row(1, null::int)::record
still printed without the casts, and after pg_dump and restore it
returned NULL instead of true. Its original text is gone, so it cannot
be fixed by creating it again. So the ruleutils.c change from v1 is
still needed, and it is the only part that helps the back branches.
With A, B and C as described below, I would propose:
Back branches (15 and later): C only, that is the tests and 0004, with
the expected output adjusted for each branch. The parser changes
would only change how new objects are printed there.
Master: A + C, that is 0001, 0002 and 0004. The parser keeps the cast
the user wrote, and C covers the rest. B adds nothing that C does not
already cover, and has to guess in make_op() what C sees directly, so
I would leave 0003 out.
I attached A + B + C so that each part can be seen on its own; dropping
0003 gives the master proposal.
There is a trade-off depending on where the fix goes. I tried these
ways:
A Parser, casts the user wrote: coerce_type() marks a copy of the
RowExpr as COERCE_EXPLICIT_CAST when asked for an explicit cast of
a RowExpr to its own type. (v2-0002)
B Parser, ROW()s the parser makes itself: mark the ROW() for a
whole-row reference to a JOIN USING alias as an explicit cast, and
have make_op() pass COERCE_EXPLICIT_CAST when it coerces a
whole-row reference of type record to an operator's composite
input type and the operands would take the form that
transformAExprOp reads as a row comparison. (v2-0003)
B' A shorter form of B: keep the JOIN USING part, but instead of
the make_op() change, have coerce_record_to_complex() in
src/backend/parser/parse_coerce.c always mark the ROW() it
builds as an explicit cast, ignoring the cformat it is given.
(not attached)
@@ -1133,2 +1133,2 @@ coerce_record_to_complex(ParseState *pstate,
rowexpr->row_typeid = baseTypeId;
- rowexpr->row_format = cformat;
+ rowexpr->row_format = COERCE_EXPLICIT_CAST;
C ruleutils.c: when the left operand of an operator is a ROW()
not marked as a cast and the right one is a ROW() or a
scalar subquery, print a cast after each such ROW().
(v1-0002, now v2-0004)
against these cases:
1 a ROW() cast to record by the user, as in the view above
2 a whole-row reference to a JOIN USING alias
3 a whole-row reference coerced to an operator's composite type
4 objects stored before the fix, as above
5 ROW()s made by the rewriter, as for whole-row references to an
updatable view in EXPLAIN; nothing stored changes
1 2 3 4 5 other output changed
A o - - - - none
B - o o - - j.* prints ::record wherever used
B' - o o - - every record-to-composite coercion,
e.g. function arguments and defaults
C o o o o o none
Whether a ROW() needs a cast depends on its parent and its sibling: it
does only as the left operand of an operator whose right operand is a
ROW() or a scalar subquery. For 1 the cast is the user's own, so A can
record it on the node itself. For 2 and 3 the parser builds the ROW()
without seeing either; it sees them only in make_op(), before the
operands are coerced, so B has to tell from their types what they will
become, and B' marks every such ROW() instead. ruleutils.c sees the
operator and both operands complete, so C needs no such guess.
In short, A keeps a cast that must not be dropped when the conversion
is simplified, while B and C add one the user did not write. B adds
it when parsing, to the ROW()s the transformation makes: in make_op()
only where it is needed, and always for a JOIN USING alias. C adds it
when printing, whatever made the tree, so it also covers trees parsed
before the code for A and B came in (4) and ROW()s the rewriter makes
after parsing (5).
Since only C reaches 4 and 5, the ways combine like this:
C alone (v1)
+ smallest change; the only one that helps the back branches
- the tree still loses the cast the user wrote
A alone (what you suggested)
+ fixes the parser bug
- 2 to 5 stay broken
A + C
+ parser records what the user wrote; C covers the rest
- C decides from the shape of the output
A + B + C (attached v2)
+ the tree also records 2 and 3 when they are parsed
- make_op() has to guess the operands before coercing them;
C is still needed
A + B' + C
+ B' is a single line
- ignores the cformat it is given, and changes the output of
every record-to-composite coercion
Once the back branches are counted, none of these is clean. What
matters there is the objects that already exist, so only C helps them,
while in master the parser fix still leaves C needed for 4 and 5. C
ends up in every choice, and master fixes the same bug in more places
than the back branches, which is why I split the proposal above.
So roughly speaking, I think
we want something that's like hide_coercion_node() but can install
a cformat other than COERCE_IMPLICIT_CAST, and then invoke that
in the coerce_type() paths that don't build a new node.
A function like that sees only the node it is given, but whether a ROW()
needs a cast depends on its parent and its sibling, as above, and
coerce_type() is told neither. Putting a function that looks at them
into the existing logic would mean passing them down through the
callers, which makes the change much larger. So A only records the cast
the user wrote, in the same-type path, and leaves the rest to C.
0001 now has fewer tests than in v1: one view per case, recreated from
its pg_get_viewdef() output and compared with the original.
Best regards,
Henson
Attachments:
t254187_2v2-0001-Add-tests-for-deparsing-record-comparisons.patchapplication/x-patch; name=v2-0001-Add-tests-for-deparsing-record-comparisons.patchDownload+171-1
v2-0002-Record-explicit-casts-of-ROW-constructors.patchapplication/x-patch; name=v2-0002-Record-explicit-casts-of-ROW-constructors.patchDownload+30-15
v2-0003-Show-casts-of-ROW-constructors-made-by-the-parser.patchapplication/x-patch; name=v2-0003-Show-casts-of-ROW-constructors-made-by-the-parser.patchDownload+80-29
v2-0004-Fix-deparsing-of-operators-on-ROW-constructors.patchapplication/x-patch; name=v2-0004-Fix-deparsing-of-operators-on-ROW-constructors.patchDownload+27-3
Import Notes
Reply to msg id not found: 1391711.1792527730@sss.pgh.pa.us
Hi Prashant,
Thank you for the review and the testing. I only saw it after I had
sent v2, which is why v2 does not mention it.
One coverage suggestion: the report names generated columns and
BEGIN ATOMIC function bodies as silently corrupted stored
expressions, but the tests exercise views only.
Good point. If Tom's review calls for a new version, I will add a
round-trip test for a generated column along with it.
v2 also records the cast in the parser, as Tom suggested, but the
ruleutils.c change you reviewed is still there as 0004 with the same
logic, so your checks of it still apply.
Best regards,
Henson
Henson Choi <assam258@gmail.com> writes:
Indeed. I think this is a parser bug and you're trying to band-aid
around it in the wrong place. get_rule_expr() would already do the
right thing if the RowExpr had row_format == COERCE_EXPLICIT_CAST,
but it does not, which seems like it could fool more things than
only deparsing.
Recording the cast in the parser fixes the case you describe, and
v2-0002 does that. It cannot fix trees that are already stored, though.
I'm not too concerned about that, given that the sum total of field
complaints about this is zero. I doubt very many people are writing
explicit casts to record, rather than explicit casts to some named
composite type, and probably even fewer are doing it in this context
for this purpose.
regards, tom lane
Hi Tom,
I'm not too concerned about that, given that the sum total of field
complaints about this is zero. I doubt very many people are writing
explicit casts to record, rather than explicit casts to some named
composite type, and probably even fewer are doing it in this context
for this purpose.
Understood. Then I'll drop the ruleutils.c change (v2-0004). v3 is
attached:
0001 tests
0002 record the casts the user writes in the parse tree (same as
v2-0002)
0003 show casts of the ROW()s the parser makes itself (same as
v2-0003)
0003 covers a whole-row reference to a JOIN USING alias and a
whole-row reference coerced to an operator's composite input type.
These come up without the user writing any cast, and they do not read
back even for views created now. The path in 0002 does not reach
them, so I kept it separate. If you think these cases need not be
handled, 0003 can be dropped.
The tests are smaller now. Each case creates a view and shows only
its pg_get_viewdef() output, and the objects are left in place so that
the pg_upgrade test dumps and restores them. As Prashant suggested,
there is also a generated column, to show the output of pg_get_expr().
Best regards,
Henson
Attachments:
t254187_7v3-0001-Add-tests-for-deparsing-record-comparisons-of-ROW.patchapplication/octet-stream; name=v3-0001-Add-tests-for-deparsing-record-comparisons-of-ROW.patchDownload+114-1
v3-0002-Record-explicit-casts-of-ROW-constructors-in-the-.patchapplication/octet-stream; name=v3-0002-Record-explicit-casts-of-ROW-constructors-in-the-.patchDownload+27-12
v3-0003-Show-casts-of-ROW-constructors-made-by-the-parser.patchapplication/octet-stream; name=v3-0003-Show-casts-of-ROW-constructors-made-by-the-parser.patchDownload+69-13
Import Notes
Reply to msg id not found: 2222105.1792607283@sss.pgh.pa.us
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: not tested
Hi Henson, Tom,
Thanks for v3. I have read through the new series and tested it with AI assistance. I agree with the direction settled in the thread: record the casts in the parse tree and leave ruleutils.c alone.
On the code: in 0002, marking a copy of the RowExpr in coerce_type() only for a same-type explicit cast that is not already marked looks right to me - the copy avoids modifying a node that may be shared, and restricting it to COERCE_EXPLICIT_CAST leaves implicit coercions alone. In 0003, marking the JOIN USING whole-row RowExpr as explicit does mean j.* prints with ::record wherever it is deparsed, not just under an operator, but I could not find a case where the new output fails to read back, and the regression suite is clean. The make_op() change also makes sense to me: it only fires for a two-argument operator whose operands take the row-comparison form, and coercing there keeps make_fn_arguments() from redoing it as an implicit cast.
What I verified, on current master with the patches applied in order:
The bug still reproduces on unpatched master in all the forms from my earlier review: silent true-to-NULL changes when views are recreated from pg_get_viewdef() output, and restore errors for the subquery, JOIN USING, and coerced whole-row cases.
With only 0001+0002 applied, the user-written cast cases are fixed - the ::record equality and <= views, the scalar-subquery case, and the generated column print their casts, round-trip with the same values, and dump/restore cleanly. The JOIN USING and =#= cases still fail at that point, as expected.
With the full v3 series, the JOIN USING view prints ROW(...)::record and round-trips, and the =#= view prints ::rowtypes_cmp_t casts on both operands and round-trips. A dump/restore of objects created after the fix completes with no errors (the same dump/restore on unpatched master reports 8 errors; with 0002 only, 4).
Thank you for adding the generated column case I confirmed it now prints the cast through pg_get_expr() and round-trips.
The whole-row comparison in the ON CONFLICT updatable-view case (Henson's case 5, rewriter-built RowExprs) prints exactly as on master; I diffed the EXPLAIN output before and after and it is identical.
Objects created before the fix still deparse the old way when the database is run under the patched server, and a dump taken before the fix restores with the same errors as before. I understand this to be the accepted trade-off behind dropping the ruleutils.c change, not a problem with v3 - noting it only because I checked it explicitly.
installcheck-world is fully green for me on the patched build: core regression 239/239, isolation 134/134, contrib 205/205 tests plus contrib isolation 20/20, test modules 71/71 plus module isolation 24/24, including the injection_points isolation specs 18/18. I watched specifically for side effects of the coerce_type() change in the form of unexpected cast, EXPLAIN, or deparse diffs elsewhere; the only expected-output changes are the ones in rowtypes.out itself.
One question: with the ruleutils.c part gone, what is intended for the back branches? A parser-side fix changes nothing for objects already stored in a back-branch database, and I could not tell from the v3 email whether backpatching is planned.
- Prashant
The new status of this patch is: Ready for Committer
Hi Prashant,
One question: with the ruleutils.c part gone, what is intended for
the back branches? A parser-side fix changes nothing for objects
already stored in a back-branch database, and I could not tell from
the v3 email whether backpatching is planned.
The ruleutils.c change (v2-0004) was the part meant for the back
branches, for exactly the reason you give: a parser-side fix cannot
help trees that are already stored. Tom was not concerned about
those trees, given that there have been no field complaints, so I
dropped it in v3.
Whether v3 goes into the back branches without it, I leave to the
committer.
Thanks for the careful review and testing.
By the way, neither of your two reviews reached my mailbox, so I read
them in the archive. I suppose that is because they were posted
through the CommitFest app rather than sent as mail to the list. If
you reply by mail next time, I will see it sooner.
Best regards,
Henson
Hi Tom,
Attached is v4, rebased over recent commits that added tests at the
end of rowtypes.sql. The only change is where the new tests sit in
rowtypes.sql and rowtypes.out; the patches are otherwise the same as
v3.
Best regards,
Henson
Attachments:
t254187_10v4-0001-Add-tests-for-deparsing-record-comparisons-of-ROW.patchapplication/octet-stream; name=v4-0001-Add-tests-for-deparsing-record-comparisons-of-ROW.patchDownload+114-1
v4-0002-Record-explicit-casts-of-ROW-constructors-in-the-.patchapplication/octet-stream; name=v4-0002-Record-explicit-casts-of-ROW-constructors-in-the-.patchDownload+27-12
v4-0003-Show-casts-of-ROW-constructors-made-by-the-parser.patchapplication/octet-stream; name=v4-0003-Show-casts-of-ROW-constructors-made-by-the-parser.patchDownload+69-13
Hello
Function-like cast syntax has the same problem, even with v4:
create view v as select record(row(1, null::int)) = record(row(1, null::int)) as r;
select r from v;
select pg_get_viewdef('v');
-- SELECT (ROW(1, NULL::integer) = ROW(1, NULL::integer)) AS r;
Hi Zsolt,
Function-like cast syntax has the same problem, even with v4:
create view v as select record(row(1, null::int)) = record(row(1,
null::int)) as r;
select r from v;
select pg_get_viewdef('v');
-- SELECT (ROW(1, NULL::integer) = ROW(1, NULL::integer)) AS r;
Thanks for testing this. A function-style cast reaches coerce_type()
with COERCE_EXPLICIT_CALL, and v4 only recorded COERCE_EXPLICIT_CAST.
As with ::record, it also matters in WHERE:
create view v2 as select x from t
where record(row(x, null::int)) = record(row(1, null::int));
That view was dumped as
WHERE (ROW(x, NULL::integer) = ROW(1, NULL::integer))
so after restore it returns no rows instead of the row with x = 1.
Attached is v5. The only functional change is the condition in
0002's coerce_type():
- cformat == COERCE_EXPLICIT_CAST &&
+ (cformat == COERCE_EXPLICIT_CAST ||
+ cformat == COERCE_EXPLICIT_CALL) &&
0001 adds a view with the function-style cast in the target list and
in WHERE, and 0002 prints it with ::record. 0003 is unchanged.
Best regards,
Henson