pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL
2+ messages / 2 participants
[nested] [flat]

* BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL
@ 2026-10-13 17:24 PG Bug reporting form <noreply@postgresql.org>
  2026-10-14 14:45 ` Re: BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL Sebastian Webber <sebastian@swebber.me>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-10-13 17:24 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: zj4204@gmail.com

The following bug has been logged on the website:

Bug reference:      19756
Logged by:          kalakala
Email address:      zj4204@gmail.com
PostgreSQL version: 18.6
Operating system:   Debian 13 (official Docker image postgres:18.6)
Description:        

ALTER TABLE ... ALTER COLUMN ... TYPE ... USING stores wrong values in
partitions and inheritance children whose column numbers differ from the
parent's (here the parent has a dropped column). This happens when the USING
expression contains IS [NOT] NULL, IS [NOT] TRUE/FALSE/UNKNOWN, or a named
function argument.

CREATE TABLE pu (x int, k int, a int, b int, c int) PARTITION BY LIST (k);
ALTER TABLE pu DROP COLUMN x;
CREATE TABLE pu1 PARTITION OF pu FOR VALUES IN (1);
INSERT INTO pu VALUES (1, 5, NULL, 9);
ALTER TABLE pu ALTER COLUMN a TYPE bigint USING CASE WHEN b IS NULL THEN -1
ELSE a END;
SELECT k, a, b, c FROM pu;

 k | a | b | c
---+---+---+---
 1 | 5 |   | 9

Expected a = -1, because b is NULL. The same statements on a table without
partitions give a = -1, and so does 12.22. With num_nulls(b) = 1 in place of
b IS NULL, the result is also -1. b IS UNKNOWN and b IS NOT TRUE (with
boolean b and c) give the same wrong result.

With a named argument on the same layout, USING extract(day from
make_interval(days => b))::int on a row with b = 2, c = 30 sets a = 30. The
positional form make_interval(0, 0, 0, b) sets a = 2.

Two variants fail with an internal error instead:
CREATE TABLE r (a int);
CREATE TABLE r1 () INHERITS (r);
ALTER TABLE r ALTER COLUMN a TYPE int USING (1 IS NULL)::int;
ERROR:  XX000: unrecognized node type: 7
LOCATION:  transformExprRecurse, parse_expr.c:376

CREATE TABLE p1 (x int, a int, b int);
ALTER TABLE p1 DROP COLUMN x;
CREATE TABLE c1 () INHERITS (p1);
INSERT INTO c1 VALUES (30, NULL);
ALTER TABLE p1 ALTER COLUMN a TYPE int USING (b IS NULL)::int;
ERROR:  XX000: attribute number 3 exceeds number of columns 2
LOCATION:  CheckVarSlotCompatibility, execExprInterp.c:2411

The first of these fails although r1 has the same column layout as r.

Reproduced on 18.6, 19beta4 and 13.23 (PGDG Debian packages in the official
Docker images). 12.22 gives the expected result in every case above.








^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL
  2026-10-13 17:24 BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-14 14:45 ` Sebastian Webber <sebastian@swebber.me>
  0 siblings, 0 replies; 2+ messages in thread

From: Sebastian Webber @ 2026-10-14 14:45 UTC (permalink / raw)
  To: zj4204@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Tue, Oct 13, 2026 at 17:24:13 +0000, PG Bug reporting form wrote:
> ALTER TABLE ... ALTER COLUMN ... TYPE ... USING stores wrong values in
> partitions and inheritance children whose column numbers differ from
> the parent's

I can reproduce all three cases on 13.23, 14.24, 15.19, 16.15, 17.11
and 18.6 (official Docker images), and on master at d733fcaf5ca. 12.22
gets them right.

This is a regression from 1281a5c907b. That commit kept parse analysis
of ALTER COLUMN TYPE in phase 1, so that USING is parsed against the
table's original state. But ATPrepCmd also runs ATParseTransformCmd
for each child, so each child parses the raw USING expression again.
The result replaces the expression that ATPrepAlterColumnType had
already mapped to the child's column numbers.

The second parse goes wrong because transformExpr rewrites NullTest,
BooleanTest and NamedArgExpr in place. After the parent's pass, their
arguments are already Vars with the parent's attnos, and the child
keeps them as they are. When the argument is a Const, the child's pass
fails with "unrecognized node type".

The same second parse breaks a column qualified with the parent's
name. This works in 12:

  CREATE TABLE q (a int, b int);
  CREATE TABLE q1 () INHERITS (q);
  ALTER TABLE q ALTER COLUMN a TYPE bigint USING q.b;
  ERROR:  missing FROM-clause entry for table "q"

The attached patch parses USING only when there is no cooked
expression yet, so children keep the mapped expression, as in 12. It
adds regression tests for the wrong values, the Const and the
qualified name.

I tested it on master with a meson debug build with assertions on
Debian trixie. The regression suite passes with the patch, and without
the fix alter_table shows all three problems. The patch also applies
cleanly to REL_14_STABLE through REL_18_STABLE, with the same results
(autoconf builds with assertions).

I used Claude Opus 5.5 (reasoning medium) for the analysis, the patch
and the tests, and Codex gpt-6.1-sol (reasoning high) to review the
patch.

-- 
Sebastian Webber

Attachments:

v1-0001-Fix-ALTER-COLUMN-TYPE-USING-for-inheritance-child.patch (text/x-patch, 5.8K) download From 114bb649d1b79c1f7b8519e689296dfb6bc0d67c Mon Sep 17 00:00:00 2001 From: Sebastian Webber <sebastian@swebber.me> Date: Wed, 14 Oct 2026 11:18:18 -0300 Subject: [PATCH v1] Fix ALTER COLUMN TYPE USING for inheritance children ALTER TABLE ... ALTER COLUMN TYPE ... USING maps the parent's transformed USING expression to each child's attribute numbers, but since 1281a5c90 the child's own pass through transformAlterTableStmt then transforms the raw expression again and throws the mapped one away. That second pass goes wrong in two ways. transformExpr modifies some raw nodes in place (NullTest, BooleanTest, NamedArgExpr), so after the parent's pass their arguments are Vars with the parent's attribute numbers, which the child's pass keeps as they are: the child reads the wrong column, or fails with "unrecognized node type" when the argument is a Const. And a column qualified with the parent's name fails with "missing FROM-clause entry", since only the child is in the namespace. Transform the USING expression only when it has not been transformed yet, so children keep the mapped expression, as before 1281a5c90. Reported-by: kalakala <zj4204@gmail.com> Discussion: https://postgr.es/m/19756-046a8a872ba33d27@postgresql.org Backpatch-through: 14 --- src/backend/parser/parse_utilcmd.c | 9 ++++++-- src/test/regress/expected/alter_table.out | 25 +++++++++++++++++++++++ src/test/regress/sql/alter_table.sql | 13 ++++++++++++ 3 files changed, 45 insertions(+), 2 deletions(-) diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index f0c7755b8fb..10e09014daa 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -3687,9 +3687,14 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, /* * For ALTER COLUMN TYPE, transform the USING clause if - * one was specified. + * one was specified. When recursing to a child table, + * cooked_default already holds the parent's expression + * with its attribute numbers mapped to the child (see + * ATPrepAlterColumnType); keep that, since the raw + * expression need not mean the same thing against the + * child, and transformExpr may have scribbled on it. */ - if (def->raw_default) + if (def->raw_default && def->cooked_default == NULL) { def->cooked_default = transformExpr(pstate, def->raw_default, diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 20707c186b3..6a7b948e4dc 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2591,6 +2591,31 @@ ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8 ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2)); ERROR: cannot convert whole-row table reference DETAIL: USING expression contains a whole-row table reference. +-- USING expressions on children with other column numbers (bug #19756) +CREATE TABLE test_type_diff3 (a int, b int, c bool, d bool); +CREATE TABLE test_type_diff3_c (b int, a int, d bool, c bool); +ALTER TABLE test_type_diff3_c INHERIT test_type_diff3; +INSERT INTO test_type_diff3 VALUES (1, NULL, true, NULL); +INSERT INTO test_type_diff3_c VALUES (2, 3, true, NULL); +ALTER TABLE test_type_diff3 ALTER COLUMN a TYPE int8 + USING a * 1000 + (b IS NULL)::int * 100 + (c IS UNKNOWN)::int * 10 + + coalesce(extract(day from make_interval(days => b))::int, 0); +SELECT tableoid::regclass, a, b, c, d FROM test_type_diff3 ORDER BY a; + tableoid | a | b | c | d +-------------------+------+---+---+--- + test_type_diff3 | 1100 | | t | + test_type_diff3_c | 3012 | 2 | | t +(2 rows) + +ALTER TABLE test_type_diff3 ALTER COLUMN a TYPE int4 USING a + (1 IS NULL)::int; +ALTER TABLE test_type_diff3 ALTER COLUMN a TYPE int8 USING test_type_diff3.a + 1; +SELECT tableoid::regclass, a, b, c, d FROM test_type_diff3 ORDER BY a; + tableoid | a | b | c | d +-------------------+------+---+---+--- + test_type_diff3 | 1101 | | t | + test_type_diff3_c | 3013 | 2 | | t +(2 rows) + -- check for rollback of ANALYZE corrupting table property flags (bug #11638) CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text); CREATE TABLE check_fk_presence_2 (id int REFERENCES check_fk_presence_1, t text); diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 18f3fdac57a..99e35226942 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1646,6 +1646,19 @@ INSERT INTO test_type_diff2_c3 VALUES (7, 8, 9); ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int8 USING int_four::int8; -- whole-row references are disallowed ALTER TABLE test_type_diff2 ALTER COLUMN int_four TYPE int4 USING (pg_column_size(test_type_diff2)); +-- USING expressions on children with other column numbers (bug #19756) +CREATE TABLE test_type_diff3 (a int, b int, c bool, d bool); +CREATE TABLE test_type_diff3_c (b int, a int, d bool, c bool); +ALTER TABLE test_type_diff3_c INHERIT test_type_diff3; +INSERT INTO test_type_diff3 VALUES (1, NULL, true, NULL); +INSERT INTO test_type_diff3_c VALUES (2, 3, true, NULL); +ALTER TABLE test_type_diff3 ALTER COLUMN a TYPE int8 + USING a * 1000 + (b IS NULL)::int * 100 + (c IS UNKNOWN)::int * 10 + + coalesce(extract(day from make_interval(days => b))::int, 0); +SELECT tableoid::regclass, a, b, c, d FROM test_type_diff3 ORDER BY a; +ALTER TABLE test_type_diff3 ALTER COLUMN a TYPE int4 USING a + (1 IS NULL)::int; +ALTER TABLE test_type_diff3 ALTER COLUMN a TYPE int8 USING test_type_diff3.a + 1; +SELECT tableoid::regclass, a, b, c, d FROM test_type_diff3 ORDER BY a; -- check for rollback of ANALYZE corrupting table property flags (bug #11638) CREATE TABLE check_fk_presence_1 (id int PRIMARY KEY, t text); -- 2.55.0
^ permalink raw reply [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-10-14 14:45 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-13 17:24 BUG #19756: ALTER COLUMN TYPE USING gives wrong values on partitions with IS NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-14 14:45 ` Sebastian Webber <sebastian@swebber.me>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox