Subject: Align tests for stored and virtual generated columns

From: Yugo Nagata <[email-masked]>
Date: Fri, 8 Aug 2025 11:51:42 +0900

Hi,

I noticed that the tests for virtual and stored generated columns
contain the following comment;

 -- keep these tests aligned with generated_stored.sql (or generated_virtual.sql)

However, it seems that some additional tests for virtual generated columns
disrupted that alignment, as they were not added to generated_stored.sql.

I've attached a patch to restore the alignment.

In addition, I moved the test confirming that user-defined types are not allowed
in virtual generated columns to the generated_virtual.sql-specific section,
since this test is not necessary for stored generated columns.

Regards,
Yugo Nagata

-- 
Yugo Nagata <[email-masked]>

---

From: Paul A Jungwirth <[email-masked]>
Date: Tue, 30 Sep 2025 11:01:55 -0700

> I've attached a patch to restore the alignment.

Hello,

Thanks for this patch! Mutaama Maasha, Surya Poondla, and I reviewed
it together. Here are our thoughts:

We agree we should try to keep these tests in sync, so if they are
diverging we should bring them back into line.

The patch still applies. Tests pass.

Going through the tests made me curious about trying to break virtual
columns. I couldn't come up with anything, although one scenario that
doesn't seem be tested is changing the collation of a column used by a
generated column. For instance:

```
-- English vs Turkish upper/lower i:
create table t2 ( x text COLLATE "en-x-icu", y text COLLATE "tr-x-icu" );
insert into t2 values ('i', 'i'), ('I', 'I');
select upper(x), ascii(upper(x)), lower(x), ascii(lower(x)), upper(y),
ascii(upper(y)), lower(y), ascii(lower(y)) from t2;

create table t3 (
  x text collate "en-x-icu",
  lx text collate "en-x-icu" generated always as (lower(x)),
  ux text collate "en-x-icu" generated always as (upper(x)),
  y text collate "tr-x-icu",
  ly text collate "tr-x-icu" generated always as (lower(y)),
  uy text collate "tr-x-icu" generated always as (upper(y))
);
insert into t3 (x, y) values ('i', 'i'), ('I', 'I');
alter table t3 add constraint x check (ascii(lx) < 128 and ascii(ux) < 128);
alter table t3 alter column x type text collate "tr-x-icu";
ERROR:  cannot alter type of a column used by a generated column
DETAIL:  Column "x" is used by generated column "lx".
```

Perhaps we could add a test like that? (We do have a test for changing
the *type* of a column used by a generated column though.)

Is there a way we can make it easier to compare the two test scripts
for differences? Could we write a meta-test that compares them for
differences (in the spirit of `opr_sanity.sql`)? I experimented with
using psql variables to limit `STORED` vs `VIRTUAL` to only the top of
each SQL file. Then I could easily diff the two files and see how
diverged they were. Attached is a patch to do this and the results of
my diff (after applying the author's patch). It seems like there are
still a few trivial discrepancies that we could clean up.

To call out one less-trivial discrepancy:

```
--- sql/generated_stored.sql    2025-09-21 19:52:14.554930323 -0700
+++ sql/generated_virtual.sql   2025-09-21 19:52:21.447016340 -0700
...
-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
```

Why are the VIRTUAL tests commented out? The explanatory comments
suggest they should have opposite results from the STORED tests, which
makes sense, but shouldn't we be running them?

Similarly we noticed that the test for expansion of virtual generated
columns is not applied to stored columns. Is there a reason why not?

We found a couple places where this patch adds new test tables whose
numbering is out of sequence compared to the rest of the file. For
instance:

> @@ -806,6 +803,9 @@ SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT
>  -- 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)) VIRTUAL);  -- error
> +
>  create table gtest32 (
>    a int primary key,
>    b int generated always as (a * 2),
> --
> 2.43.0

Why add gtest24xxx in between gtest28b and gtest32? Maybe it should be
gtest30 or 31?

Also here:

> +INSERT INTO gtest21b (a) VALUES (2), (0);  -- violates constraint
> +INSERT INTO gtest21b (a) VALUES (NULL);  -- error
>  ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
>  INSERT INTO gtest21b (a) VALUES (0);  -- ok now
>
> +-- not-null constraint with partitioned table
> +CREATE TABLE gtestnn_parent (
> +    f1 int,
> +    f2 bigint,
> +    f3 bigint GENERATED ALWAYS AS (nullif(f1, 1) + nullif(f2, 10)) STORED NOT NULL
> +) PARTITION BY RANGE (f1);
> +CREATE TABLE gtestnn_child PARTITION OF gtestnn_parent FOR VALUES FROM (1) TO (5);
> +CREATE TABLE gtestnn_childdef PARTITION OF gtestnn_parent default;

Should gtestnn_parent have a number? It is between gtest21b and
gtest22a. Perhaps gtest21nn_parent? This is a tougher choice since 21
and 22 are taken.

Yours,

-- 
Paul              ~{:-)
[email-masked]
--- 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;

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 27 Mar 2026 16:08:20 +0100

> the *type* of a column used by a generated column though.)

The example you show produces the error

which suggests that it is internally the same thing, so it seems another 
test wouldn't add any new coverage.

> still a few trivial discrepancies that we could clean up.

I thought about something like that initially, too, but then decided 
against it because it would make each test individually harder to 
understand and manage.

> makes sense, but shouldn't we be running them?

My recollection is that some of these tests are commented out because 
running them would produce some behavior that would affect subsequent 
tests (for example, adding or removing rows that they shouldn't), and so 
you would have to do some extra work to undo some of that to make this 
all work smoothly.  (Also, in some cases it would be a waste of time to 
run large blocks of tests for unsupported features, so all but the first 
few test statements are commented out.)

> columns is not applied to stored columns. Is there a reason why not?

I guess this tests something that doesn't really apply to stored 
columns.  But maybe this could be double checked.

> numbering is out of sequence compared to the rest of the file.

Yeah, this whole test numbering turned into a disaster pretty early on. 
   At this point, I consider these test files to be a monument against 
doing that again.  In later feature work, I started to name test tables 
by random numbers. ;-)