--- sql/generated_stored.sql	2025-09-21 19:52:14.554930323 -0700
+++ sql/generated_virtual.sql	2025-09-21 19:52:21.447016340 -0700
@@ -1,19 +1,19 @@
--- keep these tests aligned with generated_virtual.sql
+-- keep these tests aligned with generated_stored.sql
 -- We can use psql variables to keep the diff to a minimum.
-\set Generated 'STORED'
-\set Other 'VIRTUAL'
+\set Generated 'VIRTUAL'
+\set Other 'STORED'
 
 
-CREATE SCHEMA generated_stored_tests;
-GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
-SET search_path = generated_stored_tests;
+CREATE SCHEMA generated_virtual_tests;
+GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC;
+SET search_path = generated_virtual_tests;
 
 CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) :Generated);
 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
 
-SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
+SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;
 
-SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
+SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2, 3;
 
 \d gtest1
 
@@ -74,7 +74,7 @@
 SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
 SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
 
--- test that overflow error happens on write
+-- test that overflow error happens on read
 INSERT INTO gtest1 VALUES (2000000000);
 SELECT * FROM gtest1;
 DELETE FROM gtest1 WHERE a = 2000000000;
@@ -192,7 +192,7 @@
 TABLE gtestc;
 DROP TABLE gtestp CASCADE;
 
--- test stored update
+-- test update
 CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) :Generated);
 INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
 SELECT * FROM gtest3 ORDER BY a;
@@ -255,11 +255,11 @@
 CREATE TABLE gtest4 (
     a int,
     b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) :Generated
-);
-INSERT INTO gtest4 VALUES (1), (6);
-SELECT * FROM gtest4;
+);  -- fails, user-defined type
+--INSERT INTO gtest4 VALUES (1), (6);
+--SELECT * FROM gtest4;
 
-DROP TABLE gtest4;
+--DROP TABLE gtest4;
 DROP TYPE double_int;
 
 -- using tableoid is allowed
@@ -293,20 +293,21 @@
 CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
 REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
 
-CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated);
-INSERT INTO gtest12 VALUES (1, 10), (2, 20);
-GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) :Generated);  -- fails, user-defined function
+--INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+--GRANT SELECT (a, c), INSERT ON gtest12 TO regress_user11;
 
 SET ROLE regress_user11;
 SELECT a, b FROM gtest11;  -- not allowed
 SELECT a, c FROM gtest11;  -- allowed
 SELECT gf1(10);  -- not allowed
-INSERT INTO gtest12 VALUES (3, 30), (4, 40);  -- currently not allowed because of function permissions, should arguably be allowed
-SELECT a, c FROM gtest12;  -- allowed (does not actually invoke the function)
+--INSERT INTO gtest12 VALUES (3, 30), (4, 40);  -- allowed (does not actually invoke the function)
+--SELECT a, c FROM gtest12;  -- currently not allowed because of function permissions, should arguably be allowed
 RESET ROLE;
 
-DROP FUNCTION gf1(int);  -- fail
-DROP TABLE gtest11, gtest12;
+--DROP FUNCTION gf1(int);  -- fail
+DROP TABLE gtest11;
+--DROP TABLE gtest12;
 DROP FUNCTION gf1(int);
 DROP USER regress_user11;
 
@@ -315,8 +316,8 @@
 INSERT INTO gtest20 (a) VALUES (10);  -- ok
 INSERT INTO gtest20 (a) VALUES (30);  -- violates constraint
 
-ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100);  -- violates constraint
-ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3);  -- ok
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100);  -- violates constraint (currently not supported)
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3);  -- ok (currently not supported)
 
 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
 INSERT INTO gtest20a (a) VALUES (10);
@@ -386,85 +387,85 @@
 
 -- index constraints
 CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) :Generated UNIQUE);
-INSERT INTO gtest22a VALUES (2);
-INSERT INTO gtest22a VALUES (3);
-INSERT INTO gtest22a VALUES (4);
+--INSERT INTO gtest22a VALUES (2);
+--INSERT INTO gtest22a VALUES (3);
+--INSERT INTO gtest22a VALUES (4);
 CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) :Generated, PRIMARY KEY (a, b));
-INSERT INTO gtest22b VALUES (2);
-INSERT INTO gtest22b VALUES (2);
+--INSERT INTO gtest22b VALUES (2);
+--INSERT INTO gtest22b VALUES (2);
 
 -- indexes
 CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) :Generated);
-CREATE INDEX gtest22c_b_idx ON gtest22c (b);
-CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
-CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
-\d gtest22c
-
-INSERT INTO gtest22c VALUES (1), (2), (3);
-SET enable_seqscan TO off;
-SET enable_bitmapscan TO off;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
-SELECT * FROM gtest22c WHERE b = 4;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
-SELECT * FROM gtest22c WHERE b * 3 = 6;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-
-ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
-ANALYZE gtest22c;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
-SELECT * FROM gtest22c WHERE b = 8;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
-SELECT * FROM gtest22c WHERE b * 3 = 12;
-EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-RESET enable_seqscan;
-RESET enable_bitmapscan;
+--CREATE INDEX gtest22c_b_idx ON gtest22c (b);
+--CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
+--CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
+--\d gtest22c
+
+--INSERT INTO gtest22c VALUES (1), (2), (3);
+--SET enable_seqscan TO off;
+--SET enable_bitmapscan TO off;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
+--SELECT * FROM gtest22c WHERE b = 4;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
+--SELECT * FROM gtest22c WHERE b * 3 = 6;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+--SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+
+--ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+--ANALYZE gtest22c;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+--SELECT * FROM gtest22c WHERE b = 8;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+--SELECT * FROM gtest22c WHERE b * 3 = 12;
+--EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+--SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+--RESET enable_seqscan;
+--RESET enable_bitmapscan;
 
 -- foreign keys
 CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
-INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
+--INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
 
 CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON UPDATE CASCADE);  -- error
 CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x) ON DELETE SET NULL);  -- error
 
 CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated REFERENCES gtest23a (x));
-\d gtest23b
+--\d gtest23b
 
-INSERT INTO gtest23b VALUES (1);  -- ok
-INSERT INTO gtest23b VALUES (5);  -- error
-ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
-ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
+--INSERT INTO gtest23b VALUES (1);  -- ok
+--INSERT INTO gtest23b VALUES (5);  -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
+--ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
 
-DROP TABLE gtest23b;
-DROP TABLE gtest23a;
+--DROP TABLE gtest23b;
+--DROP TABLE gtest23a;
 
 CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) :Generated, PRIMARY KEY (y));
-INSERT INTO gtest23p VALUES (1), (2), (3);
+--INSERT INTO gtest23p VALUES (1), (2), (3);
 
 CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
-INSERT INTO gtest23q VALUES (1, 2);  -- ok
-INSERT INTO gtest23q VALUES (2, 5);  -- error
+--INSERT INTO gtest23q VALUES (1, 2);  -- ok
+--INSERT INTO gtest23q VALUES (2, 5);  -- error
 
 -- domains
 CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
 CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated);
-INSERT INTO gtest24 (a) VALUES (4);  -- ok
-INSERT INTO gtest24 (a) VALUES (6);  -- error
+--INSERT INTO gtest24 (a) VALUES (4);  -- ok
+--INSERT INTO gtest24 (a) VALUES (6);  -- error
 CREATE TYPE gtestdomain1range AS range (subtype = gtestdomain1);
 CREATE TABLE gtest24r (a int PRIMARY KEY, b gtestdomain1range GENERATED ALWAYS AS (gtestdomain1range(a, a + 5)) :Generated);
-INSERT INTO gtest24r (a) VALUES (4);  -- ok
-INSERT INTO gtest24r (a) VALUES (6);  -- error
+--INSERT INTO gtest24r (a) VALUES (4);  -- ok
+--INSERT INTO gtest24r (a) VALUES (6);  -- error
 
 CREATE TABLE gtest24at (a int PRIMARY KEY);
-ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated;  -- ok
+ALTER TABLE gtest24at ADD COLUMN b gtestdomain1 GENERATED ALWAYS AS (a * 2) :Generated;  -- error
 CREATE TABLE gtest24ata (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) :Generated);
-ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1;  -- ok
+ALTER TABLE gtest24ata ALTER COLUMN b TYPE gtestdomain1;  -- error
 
 CREATE DOMAIN gtestdomainnn AS int CHECK (VALUE IS NOT NULL);
 CREATE TABLE gtest24nn (a int, b gtestdomainnn GENERATED ALWAYS AS (a * 2) :Generated);
-INSERT INTO gtest24nn (a) VALUES (4);  -- ok
-INSERT INTO gtest24nn (a) VALUES (NULL);  -- error
+--INSERT INTO gtest24nn (a) VALUES (4);  -- ok
+--INSERT INTO gtest24nn (a) VALUES (NULL);  -- error
 
 -- typed tables (currently not supported)
 CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
@@ -517,7 +518,7 @@
 INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
 SELECT tableoid::regclass, * FROM gtest_child ORDER BY 1, 2, 3;
-SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3;
+SELECT tableoid::regclass, * FROM gtest_child2 ORDER BY 1, 2, 3;  -- uses child's generation expression, not parent's
 SELECT tableoid::regclass, * FROM gtest_child3 ORDER BY 1, 2, 3;
 UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
@@ -611,15 +612,15 @@
 SELECT * FROM gtest29;
 \d gtest29
 
-ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
+ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;  -- not supported
 INSERT INTO gtest29 (a) VALUES (5);
 INSERT INTO gtest29 (a, b) VALUES (6, 66);
 SELECT * FROM gtest29;
 \d gtest29
 
 -- check that dependencies between columns have also been removed
-ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
-\d gtest29
+--ALTER TABLE gtest29 DROP COLUMN a;  -- should not drop b
+--\d gtest29
 
 -- with inheritance
 CREATE TABLE gtest30 (
@@ -646,7 +647,7 @@
 CREATE TABLE gtest31_2 (x int, y gtest31_1);
 ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar;  -- fails
 
--- bug #18970: these cases are unsupported, but make sure they fail cleanly
+-- bug #18970
 ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL);
 ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1');
 ALTER TABLE gtest31_2 DROP CONSTRAINT cc;
@@ -797,3 +798,91 @@
 
 -- sanity check of system catalog
 SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's', 'v');
+
+
+--
+-- test the expansion of virtual generated columns
+--
+-- these tests are specific to generated_virtual.sql
+--
+
+-- using user-defined type not yet supported
+CREATE TABLE gtest24xxx (a gtestdomain1, b gtestdomain1, c int GENERATED ALWAYS AS (greatest(a, b)) :Generated);  -- error
+
+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)),
+  e int
+);
+
+insert into gtest32 values (1), (2);
+analyze gtest32;
+
+-- Ensure that nullingrel bits are propagated into the generation expressions
+explain (costs off)
+select sum(t2.b) over (partition by t2.a),
+       sum(t2.c) over (partition by t2.a),
+       sum(t2.d) over (partition by t2.a)
+from gtest32 as t1 left join gtest32 as t2 on (t1.a = t2.a)
+order by t1.a;
+
+select sum(t2.b) over (partition by t2.a),
+       sum(t2.c) over (partition by t2.a),
+       sum(t2.d) over (partition by t2.a)
+from gtest32 as t1 left join gtest32 as t2 on (t1.a = t2.a)
+order by t1.a;
+
+-- Ensure that outer-join removal functions correctly after the propagation of nullingrel bits
+explain (costs off)
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2;
+
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2;
+
+explain (costs off)
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2 or t1.a is null;
+
+select t1.a from gtest32 t1 left join gtest32 t2 on t1.a = t2.a
+where coalesce(t2.b, 1) = 2 or t1.a is null;
+
+-- Ensure that the generation expressions are wrapped into PHVs if needed
+explain (verbose, costs off)
+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, 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;
+
+-- Ensure that virtual generated column references within SubLinks that should
+-- be transformed into joins can get expanded
+explain (costs off)
+select 1 from gtest32 t1 where exists
+  (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
+
+select 1 from gtest32 t1 where exists
+  (select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
+
+drop table gtest32;
+
+-- Ensure that virtual generated columns in constraint expressions are expanded
+create table gtest33 (a int, b int generated always as (a * 2) virtual not null, check (b > 10));
+set constraint_exclusion to on;
+
+-- should get a dummy Result, not a seq scan
+explain (costs off)
+select * from gtest33 where b < 10;
+
+-- should get a dummy Result, not a seq scan
+explain (costs off)
+select * from gtest33 where b is null;
+
+reset constraint_exclusion;
+drop table gtest33;