Expand virtual generated columns for ALTER COLUMN TYPE

For the subcommand ALTER COLUMN TYPE of the ALTER TABLE command, the
USING expression may reference virtual generated columns.  These
columns must be expanded before the expression is fed through
expression_planner and the expression-execution machinery.  Failing to
do so can result in incorrect rewrite decisions, and can also lead to
"ERROR:  unexpected virtual generated column reference".

Reported-by: Alexander Lakhin <exclusion@gmail.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://postgr.es/m/b5f96b24-ccac-47fd-9e20-14681b894f36@gmail.com

3 files changed, 30 insertions, 19 deletions

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 074ddb6b9cd..1c3ad74e7b9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14484,6 +14484,9 @@ ATPrepAlterColumnType(List **wqueue,
 		/* Fix collations after all else */
 		assign_expr_collations(pstate, transform);
 
+		/* Expand virtual generated columns in the expr. */
+		transform = expand_generated_columns_in_expr(transform, rel, 1);
+
 		/* Plan the expr now so we can accurately assess the need to rewrite. */
 		transform = (Node *) expression_planner((Expr *) transform);
 
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 47cbd3a82fe..46713f06797 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1479,7 +1479,8 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100))
+  d int generated always as (coalesce(a, 100)),
+  e int
 );
 insert into gtest32 values (1), (2);
 analyze gtest32;
@@ -1563,41 +1564,44 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
                       QUERY PLAN                      
 ------------------------------------------------------
  Nested Loop Left Join
-   Output: a, (a * 2), (20), (COALESCE(a, 100))
+   Output: a, (a * 2), (20), (COALESCE(a, 100)), e
    Join Filter: false
    ->  Seq Scan on generated_virtual_tests.gtest32 t1
-         Output: t1.a, t1.b, t1.c, t1.d
+         Output: t1.a, t1.b, t1.c, t1.d, t1.e
    ->  Result
-         Output: a, 20, COALESCE(a, 100)
+         Output: a, e, 20, COALESCE(a, 100)
          One-Time Filter: false
 (8 rows)
 
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
- a | b | c | d 
----+---+---+---
-   |   |   |  
-   |   |   |  
+ a | b | c | d | e 
+---+---+---+---+---
+   |   |   |   |  
+   |   |   |   |  
 (2 rows)
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
                      QUERY PLAN                      
 -----------------------------------------------------
  HashAggregate
-   Output: a, ((a * 2)), (20), (COALESCE(a, 100))
+   Output: a, ((a * 2)), (20), (COALESCE(a, 100)), e
    Hash Key: t.a
    Hash Key: (t.a * 2)
    Hash Key: 20
    Hash Key: COALESCE(t.a, 100)
+   Hash Key: t.e
    Filter: ((20) = 20)
    ->  Seq Scan on generated_virtual_tests.gtest32 t
-         Output: a, (a * 2), 20, COALESCE(a, 100)
-(9 rows)
+         Output: a, (a * 2), 20, COALESCE(a, 100), e
+(10 rows)
 
-select * from gtest32 t group by grouping sets (a, b, c, d) having c = 20;
- a | b | c  | d 
----+---+----+---
-   |   | 20 |  
+select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+ a | b | c  | d | e 
+---+---+----+---+---
+   |   | 20 |   |  
 (1 row)
 
+-- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
+alter table gtest32 alter column e type bigint using b;
 drop table gtest32;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index c731d123763..6fa986515b9 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -797,7 +797,8 @@ create table gtest32 (
   a int primary key,
   b int generated always as (a * 2),
   c int generated always as (10 + 10),
-  d int generated always as (coalesce(a, 100))
+  d int generated always as (coalesce(a, 100)),
+  e int
 );
 
 insert into gtest32 values (1), (2);
@@ -838,7 +839,10 @@ select t2.* from gtest32 t1 left join gtest32 t2 on false;
 select t2.* from gtest32 t1 left join gtest32 t2 on false;
 
 explain (verbose, costs off)
-select * from gtest32 t group by grouping sets (a, b, c, d) having c = 20;
-select * from gtest32 t group by grouping sets (a, b, c, d) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+select * from gtest32 t group by grouping sets (a, b, c, d, e) having c = 20;
+
+-- Ensure that the virtual generated columns in ALTER COLUMN TYPE USING expression are expanded
+alter table gtest32 alter column e type bigint using b;
 
 drop table gtest32;