MIME-Version: 1.0
From: Swirl Smog Dowry <swirl-smog-dowry@duck.com>
Date: Thu, 26 Feb 2026 11:18:43 +0100
Message-ID: <CA+-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8+pe2yJrdh4o4uoQ@mail.gmail.com>
Subject: pg_get_viewdef() produces non-round-trippable SQL for views with
 USING join on mismatched integer types
To: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a754fe064bb7723b"
Archived-At: <https://www.postgresql.org/message-id/CA%2B-gibjCg_vjcq3hWTM0sLs3_TUZ6Q9rkv8%2Bpe2yJrdh4o4uoQ%40mail.gmail.com>
Precedence: bulk

--000000000000a754fe064bb7723b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

PostgreSQL version: 18.1 (also verified on 18.2)
OS: Linux

Description:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
When a view uses a USING join on columns with different integer types
(integer vs
bigint) and the SELECT clause contains an explicit narrowing cast,
pg_get_viewdef()
produces SQL that PostgreSQL itself rejects. This makes pg_dump produce
dumps that
fail on restore for any such view.

The SELECT clause gets col::integer, while the GROUP BY gets (col::bigint)
=E2=80=94 they
refer to the same underlying column but with different casts, so the GROUP
BY check
fails to recognise the SELECT expression as covered.

Minimal reproducer:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

  CREATE TABLE t1 (year integer, val numeric);
  CREATE TABLE t2 (year bigint, label text);
  INSERT INTO t1 VALUES (2025, 100), (2025, 200), (2026, 300);
  INSERT INTO t2 VALUES (2025, 'A'), (2026, 'B');

  -- View creation succeeds and queries work fine:
  CREATE VIEW v AS
    SELECT year::integer AS year, t2.label, sum(val) AS total
    FROM t1
    LEFT JOIN t2 USING (year)
    GROUP BY year, t2.label;

  SELECT * FROM v;  -- returns correct results

  -- pg_get_viewdef output:
  SELECT pg_get_viewdef('v'::regclass, true);

Output of pg_get_viewdef:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

   SELECT t1.year::integer AS year,
      t2.label,
      sum(t1.val) AS total
     FROM t1
       LEFT JOIN t2 USING (year)
    GROUP BY (t1.year::bigint), t2.label;

Note: SELECT has t1.year::integer, GROUP BY has (t1.year::bigint).

Attempting to re-execute the pg_get_viewdef output fails:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D

  CREATE VIEW v2 AS
   SELECT t1.year::integer AS year,
      t2.label,
      sum(t1.val) AS total
     FROM t1
       LEFT JOIN t2 USING (year)
    GROUP BY (t1.year::bigint), t2.label;

  ERROR:  column "t1.year" must appear in the GROUP BY clause or be used
          in an aggregate function
  LINE 2:  SELECT t1.year::integer AS year,
                  ^

Impact:
=3D=3D=3D=3D=3D=3D=3D
pg_dump uses pg_get_viewdef() to serialise view definitions. Any database
containing such a view (USING join between integer and bigint columns, with
an explicit cast in SELECT) will produce a dump that fails during restore
with
the above error.

Without the explicit cast in SELECT (i.e. just SELECT year, ...)
pg_get_viewdef
emits t1.year::bigint in both SELECT and GROUP BY, and the round-trip works=
.
The bug is triggered specifically by the narrowing cast (bigint -> integer)
in
the SELECT list combined with a USING join.


Kind regards,

Swirl

--000000000000a754fe064bb7723b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">PostgreSQL version: 18.1 (also verified on 18.2)<br>OS: Li=
nux<br><br>Description:<br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>When a v=
iew uses a USING join on columns with different integer types (integer vs<b=
r>bigint) and the SELECT clause contains an explicit narrowing cast, pg_get=
_viewdef()<br>produces SQL that PostgreSQL itself rejects. This makes pg_du=
mp produce dumps that<br>fail on restore for any such view.<br><br>The SELE=
CT clause gets col::integer, while the GROUP BY gets (col::bigint) =E2=80=
=94 they<br>refer to the same underlying column but with different casts, s=
o the GROUP BY check<br>fails to recognise the SELECT expression as covered=
.<br><br>Minimal reproducer:<br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D<br><br>=C2=A0 CREATE TABLE t1 (year integer, val numeric);<=
br>=C2=A0 CREATE TABLE t2 (year bigint, label text);<br>=C2=A0 INSERT INTO =
t1 VALUES (2025, 100), (2025, 200), (2026, 300);<br>=C2=A0 INSERT INTO t2 V=
ALUES (2025, &#39;A&#39;), (2026, &#39;B&#39;);<br><br>=C2=A0 -- View creat=
ion succeeds and queries work fine:<br>=C2=A0 CREATE VIEW v AS<br>=C2=A0 =
=C2=A0 SELECT year::integer AS year, t2.label, sum(val) AS total<br>=C2=A0 =
=C2=A0 FROM t1<br>=C2=A0 =C2=A0 LEFT JOIN t2 USING (year)<br>=C2=A0 =C2=A0 =
GROUP BY year, t2.label;<br><br>=C2=A0 SELECT * FROM v; =C2=A0-- returns co=
rrect results<br><br>=C2=A0 -- pg_get_viewdef output:<br>=C2=A0 SELECT pg_g=
et_viewdef(&#39;v&#39;::regclass, true);<br><br>Output of pg_get_viewdef:<b=
r>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D<br><br>=C2=A0 =C2=A0SELECT t1.year::integer AS year,<br>=C2=A0 =C2=A0 =
=C2=A0 t2.label,<br>=C2=A0 =C2=A0 =C2=A0 sum(t1.val) AS total<br>=C2=A0 =C2=
=A0 =C2=A0FROM t1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0LEFT JOIN t2 USING (year)<b=
r>=C2=A0 =C2=A0 GROUP BY (t1.year::bigint), t2.label;<br><br>Note: SELECT h=
as t1.year::integer, GROUP BY has (t1.year::bigint).<br><br>Attempting to r=
e-execute the pg_get_viewdef output fails:<br>=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br><b=
r>=C2=A0 CREATE VIEW v2 AS<br>=C2=A0 =C2=A0SELECT t1.year::integer AS year,=
<br>=C2=A0 =C2=A0 =C2=A0 t2.label,<br>=C2=A0 =C2=A0 =C2=A0 sum(t1.val) AS t=
otal<br>=C2=A0 =C2=A0 =C2=A0FROM t1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0LEFT JOIN=
 t2 USING (year)<br>=C2=A0 =C2=A0 GROUP BY (t1.year::bigint), t2.label;<br>=
<br>=C2=A0 ERROR: =C2=A0column &quot;t1.year&quot; must appear in the GROUP=
 BY clause or be used<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 in an aggregate=
 function<br>=C2=A0 LINE 2: =C2=A0SELECT t1.year::integer AS year,<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^<br><br>Impact=
:<br>=3D=3D=3D=3D=3D=3D=3D<br>pg_dump uses pg_get_viewdef() to serialise vi=
ew definitions. Any database<br>containing such a view (USING join between =
integer and bigint columns, with<br>an explicit cast in SELECT) will produc=
e a dump that fails during restore with<br>the above error.<br><br>Without =
the explicit cast in SELECT (i.e. just SELECT year, ...) pg_get_viewdef<br>=
emits t1.year::bigint in both SELECT and GROUP BY, and the round-trip works=
.<br>The bug is triggered specifically by the narrowing cast (bigint -&gt; =
integer) in<br><div>the SELECT list combined with a USING join.</div><div><=
br></div><div><br></div><div>Kind regards,</div><div><br></div><div>Swirl</=
div><div><br></div></div>

--000000000000a754fe064bb7723b--