Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHkf7-00000003HOw-072y for pgsql-bugs@arkaria.postgresql.org; Fri, 16 Oct 2026 16:20:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHkf4-0000000FLJN-4AJT for pgsql-bugs@arkaria.postgresql.org; Fri, 16 Oct 2026 16:20:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHkLq-0000000FE8c-0JCZ for pgsql-bugs@lists.postgresql.org; Fri, 16 Oct 2026 16:00:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHkLo-00000002Vsl-02E0 for pgsql-bugs@lists.postgresql.org; Fri, 16 Oct 2026 16:00:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=PgpY5wCBWBJ1zboL28wCYRArGqkokpUwD+gotMBEitA=; b=yGd9NHgvVWUBhL44u2mHBBtWu1 m5IDXsaw4PN0dGYuAXLsKpu5wEh+8zps9oISPBkRw7U0hhuF3qB6yuSIy0C4eus/QNPimFqAfRZU1 svLWwry8lBYP6Va1TICpmR4X0EStasFb4wvs58m98p6hV49E4CUbRGKCx1VpdGPlbhPhmaZ4NFXE8 3AnFczEJqc79z6WNPgJrZxRScnbMnod9OTBy+wg5CcpDWhgKESQJu6eZrUjv/Z6sLu1WHtr2rghxo BYB4+xBk9GJr2HxUhjCJ+vDVa/A6sfuSJhcvv1xdWSREmWSe9Z2V0UcJw2Ox/nY7gnO+mNNcfdrPA yKadn7yg==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xHkLm-00000006oKc-3AKx for pgsql-bugs@lists.postgresql.org; Fri, 16 Oct 2026 16:00:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xHkLl-00000003Lvs-1ewz for pgsql-bugs@lists.postgresql.org; Fri, 16 Oct 2026 16:00:05 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19767: pg_get_viewdef prints ORDER BY (- '3'::int4) as (- 3), which reparses as column position -3 To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Fri, 16 Oct 2026 15:59:56 +0000 Message-ID: <19767-f1189fd6a83254b7@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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