BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19767
Logged by: Alperen Keles
Email address: alpkeles99@gmail.com
PostgreSQL version: 18.6
Operating system: Ubuntu 24.04 x86_64 (also macOS 26 arm64)
Description:
Hello,
In ORDER BY and GROUP BY, a bare integer constant is a column position
(ORDER BY 2), while "ORDER BY (- '3'::int4)" is an expression, the negation
of a typed literal, so it sorts by a constant -3.
pg_get_viewdef() prints that term as "(- 3)". When that text is parsed
again, the grammar folds "- 3" into the integer constant -3 (doNegate),
which ORDER BY then reads as a column position. So the printed
definition of a valid view is not a valid view, and pg_dump, which
prints views the same way, writes a dump that does not restore.
```
CREATE TABLE t (a int);
INSERT INTO t VALUES (1), (2);
CREATE VIEW o AS SELECT a FROM t ORDER BY (- '3'::int4);
CREATE VIEW g AS SELECT count(*) AS n FROM t GROUP BY (- '3'::int4);
SELECT * FROM o; -- 1, 2
SELECT * FROM g; -- 2
SELECT pg_get_viewdef('o'::regclass);
SELECT a
FROM t
ORDER BY (- 3);
SELECT pg_get_viewdef('g'::regclass);
SELECT count(*) AS n
FROM t
GROUP BY (- 3);
```
Creating each view again from its own printed definition fails:
```
CREATE VIEW o2 AS SELECT a FROM t ORDER BY (- 3);
ERROR: ORDER BY position -3 is not in select list
CREATE VIEW g2 AS SELECT count(*) AS n FROM t GROUP BY (- 3);
ERROR: GROUP BY position -3 is not in select list
```
That is what happens on restore: pg_dump of that database writes
```
CREATE VIEW public.o AS
SELECT a
FROM public.t
ORDER BY (- 3);
```
and restoring it with psql into an empty database gives
```
psql:dump.sql:44: ERROR: ORDER BY position -3 is not in select list
psql:dump.sql:67: ERROR: GROUP BY position -3 is not in select list
```
followed by "relation does not exist" for the views' ALTER OWNER and for
a view built on top of o, so those views are lost. pg_restore of a -Fc
dump fails the same way.
When the folded constant is a position that exists, the text silently
means something else. "- (- '1'::int4)" prints as "(- (- 1))", which
folds to 1:
```
CREATE TABLE t2 (a int, b int);
INSERT INTO t2 VALUES (1, 10), (2, 20), (1, 30);
CREATE VIEW v AS SELECT DISTINCT ON (- (- '1'::int4)) a, b FROM t2
ORDER BY - (- '1'::int4), b DESC;
SELECT * FROM v; -- one row: (1,30)
```
Its definition, run again (or restored from a dump), becomes
DISTINCT ON (a) ... ORDER BY a, b DESC and returns (1,30) and (2,20).
Reproduced on 18.6 (REL_18_STABLE dfbc1507cdb), 19rc1 (REL_19_STABLE
1212d5d33b1), 14.24 (REL_14_STABLE 14904052e65) and master a261fc2b722,
Ubuntu 24.04 x86_64, --enable-cassert, with pg_dump of the same version;
also on 18.6 and master on macOS arm64.
Found with a query fuzzer.
Regards,
Alperen Keles
Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
Hi, On Fri, Oct 16, 2026 at 9:46 PM PG Bug reporting form wrote: > > pg_get_viewdef() prints that term as "(- 3)". When that text is parsed > again, the grammar folds "- 3" into the integer constant -3 (doNegate), > which ORDER BY then reads as a column position. So the printed > definition of a valid view is not a valid view, and pg_dump, which > prints views the same way, writes a dump that does not restore. > > ``` > CREATE TABLE t (a int); > INSERT INTO t VALUES (1), (2); > CREATE VIEW o AS SELECT a FROM t ORDER BY (- '3'::int4); > CREATE VIEW g AS SELECT count(*) AS n FROM t GROUP BY (- '3'::int4); > SELECT * FROM o; -- 1, 2 > SELECT * FROM g; -- 2 > I'm not sure if this kind of use case is common enough to warrant concern. But I looked into a possible fix. The fix is small where during deparsing, get_oper_expr checks if the argument to a prefix operator is a constant and forces adding '::type' to it (so it would create 3::int4 in this case). It might lead to some extra '::type' prints in other cases unaffected by the bug but that seems fine to me. I'm attaching the patch with the fix and a couple of tests. Please take a look. Thanks & Regards, Nitin Motiani Google
Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
От:
Henson Choi <assam258@gmail.com>
Дата:
Hi Nitin,
> I'm not sure if this kind of use case is common enough to warrant
> concern. But I looked into a possible fix. The fix is small where
> during deparsing, get_oper_expr checks if the argument to a prefix
> operator is a constant and forces adding '::type' to it (so it would
> create 3::int4 in this case). It might lead to some extra '::type'
> prints in other cases unaffected by the bug but that seems fine to me.
I had been tracking an issue with the same cause, and this fix
resolves it too. When a user-defined prefix "-" operator is visible in
the search_path, pg_get_viewdef() prints it as a bare "-", and running
that text again folds "- 1" into the constant -1, so the operator
silently disappears:
create schema s;
create function s.neg42(int) returns int language sql immutable
return 42;
create operator s.- (rightarg = int4, function = s.neg42);
set search_path = s, pg_catalog, public;
create view public.vneg as select -(1::int) as x;
select x from public.vneg; -- 42
select pg_get_viewdef('public.vneg', true) as def \gset
create view public.vneg_re as :def
select x from public.vneg_re;
Before the patch:
select pg_get_viewdef('public.vneg', true);
pg_get_viewdef
-------------------
SELECT - 1 AS x;
(1 row)
select x from public.vneg_re;
x
----
-1
(1 row)
With the patch:
select pg_get_viewdef('public.vneg', true);
pg_get_viewdef
----------------------------
SELECT - 1::integer AS x;
(1 row)
select x from public.vneg_re;
x
----
42
(1 row)
That case seems even less common than the reported one, and pg_dump
keeps the meaning since it prints OPERATOR(s.-), so I do not think
it changes the case for the fix, and I am not sure it is worth a test
of its own either.
On master, the code change alone leaves all existing regression
output unchanged, so the extra '::type' you mention does not show up
there; it only affects prefix operators applied to int4 and numeric
constants, the only ones printed without a label. I also built REL_15
through REL_19 with the patch and ran the regression tests: the code
applies cleanly to all of them, and the patch applies as it is from
REL_16 on; on REL_15 only the expected output needs regenerating, for
the column qualification pretty mode still prints there.
Best regards,
Henson
> I'm not sure if this kind of use case is common enough to warrant
> concern. But I looked into a possible fix. The fix is small where
> during deparsing, get_oper_expr checks if the argument to a prefix
> operator is a constant and forces adding '::type' to it (so it would
> create 3::int4 in this case). It might lead to some extra '::type'
> prints in other cases unaffected by the bug but that seems fine to me.
I had been tracking an issue with the same cause, and this fix
resolves it too. When a user-defined prefix "-" operator is visible in
the search_path, pg_get_viewdef() prints it as a bare "-", and running
that text again folds "- 1" into the constant -1, so the operator
silently disappears:
create schema s;
create function s.neg42(int) returns int language sql immutable
return 42;
create operator s.- (rightarg = int4, function = s.neg42);
set search_path = s, pg_catalog, public;
create view public.vneg as select -(1::int) as x;
select x from public.vneg; -- 42
select pg_get_viewdef('public.vneg', true) as def \gset
create view public.vneg_re as :def
select x from public.vneg_re;
Before the patch:
select pg_get_viewdef('public.vneg', true);
pg_get_viewdef
-------------------
SELECT - 1 AS x;
(1 row)
select x from public.vneg_re;
x
----
-1
(1 row)
With the patch:
select pg_get_viewdef('public.vneg', true);
pg_get_viewdef
----------------------------
SELECT - 1::integer AS x;
(1 row)
select x from public.vneg_re;
x
----
42
(1 row)
That case seems even less common than the reported one, and pg_dump
keeps the meaning since it prints OPERATOR(s.-), so I do not think
it changes the case for the fix, and I am not sure it is worth a test
of its own either.
On master, the code change alone leaves all existing regression
output unchanged, so the extra '::type' you mention does not show up
there; it only affects prefix operators applied to int4 and numeric
constants, the only ones printed without a label. I also built REL_15
through REL_19 with the patch and ran the regression tests: the code
applies cleanly to all of them, and the patch applies as it is from
REL_16 on; on REL_15 only the expected output needs regenerating, for
the column qualification pretty mode still prints there.
Best regards,
Henson
Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
On Mon, Oct 19, 2026 at 8:05 PM Henson Choi wrote: > > On master, the code change alone leaves all existing regression > output unchanged, so the extra '::type' you mention does not show up > there; it only affects prefix operators applied to int4 and numeric > constants, the only ones printed without a label. I also built REL_15 > through REL_19 with the patch and ran the regression tests: the code > applies cleanly to all of them, and the patch applies as it is from > REL_16 on; on REL_15 only the expected output needs regenerating, for > the column qualification pretty mode still prints there. > Thanks for the feedback and the analysis, Henson. I've created a commitfest entry for this at https://commitfest.postgresql.org/patch/7500/. Regards, Nitin Motiani Google
Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
Attaching the rebased patch. Thanks, Nitin Motiani Google
Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
От:
Henson Choi <assam258@gmail.com>
Дата:
Hi Nitin,
> Attaching the rebased patch.
Thanks. I checked v2: the code and the test are the same as in v1,
and only the context at the end of create_view.out has changed.
The whole test suite passes with it, and a pg_dump/restore of objects
using prefix operators on constants round-trips to the same
definitions.
I also tried it on the back branches again: the code applies
cleanly to REL_14 through REL_19, and the test applies as it is from
REL_16 on; on REL_14 and REL_15 only the expected output needs
regenerating.
I will mark it Ready for Committer.
Best regards,
Henson
> Attaching the rebased patch.
Thanks. I checked v2: the code and the test are the same as in v1,
and only the context at the end of create_view.out has changed.
The whole test suite passes with it, and a pg_dump/restore of objects
using prefix operators on constants round-trips to the same
definitions.
I also tried it on the back branches again: the code applies
cleanly to REL_14 through REL_19, and the test applies as it is from
REL_16 on; on REL_14 and REL_15 only the expected output needs
regenerating.
I will mark it Ready for Committer.
Best regards,
Henson
Re: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3
От:
solai v <solai.cdac@gmail.com>
Дата:
Hi, I tested the v2 patch and I would like to share my observations. Before applying the patch, pg_get_viewdef() generated output such as ORDER BY (- 3), which could be misinterpreted as a column position when the view definition was parsed again. After applying the patch, the output included the explicit type cast, for example, ORDER BY (- 3::integer). I also verified that the recreated views worked correctly. The regression tests covering ORDER BY, GROUP BY, and reloading the generated view definitions passed, and all regression tests passed in my testing environment. Based on these results, the patch appears to address the reported issue. Thank you for your work on this fix. Regards, Solai