public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: Yugo Nagata <[email protected]>
To: Surya Poondla <[email protected]>
To: Mutaamba Maasha <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Align tests for stored and virtual generated columns
Date: Tue, 30 Sep 2025 11:01:55 -0700
Message-ID: <CA+renyWSegxAW4f+OBdhEeVLL6kLNiN_zxy2ddE=YGhXkyM+=g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Thu, Aug 7, 2025 at 7:52 PM Yugo Nagata <[email protected]> wrote:
> 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.
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 protected]
Attachments:
[text/x-patch] diff_generated_vs_stored.diff (15.0K, 2-diff_generated_vs_stored.diff)
download | inline diff:
--- 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;
[application/octet-stream] 0001-Try-to-limit-STORED-vs-VIRTUAL-diff-to-just-the-firs.patch.nocfbot (136.0K, 3-0001-Try-to-limit-STORED-vs-VIRTUAL-diff-to-just-the-firs.patch.nocfbot)
download
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Align tests for stored and virtual generated columns
In-Reply-To: <CA+renyWSegxAW4f+OBdhEeVLL6kLNiN_zxy2ddE=YGhXkyM+=g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox