Re: Virtual generated columns

On Mon, Feb 10, 2025 at 1:16 PM Zhang Mingli <zmlpostgres(at)gmail(dot)com> wrote:
> I believe virtual columns should behave like stored columns, except they don't actually use storage.
> Virtual columns are computed when the table is read, and they should adhere to the same rules of join semantics.
> I agree with Richard, the result seems incorrect. The right outcome should be:
> gpadmin=# SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
> a | b
> ------+------
> NULL | NULL
> NULL | NULL
> (2 rows)

Yeah, I also feel that the virtual generated columns should adhere to
outer join semantics, rather than being unconditionally replaced by
the generation expressions. But maybe I'm wrong.

If that's the case, this incorrect-result issue isn't limited to
constant expressions; it could also occur with non-strict ones.

CREATE TABLE t (a int, b int GENERATED ALWAYS AS (COALESCE(a, 100)));
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);

# SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
a | b
---+-----
| 100
| 100
(2 rows)

It seems to me that virtual generated columns should be expanded in
the planner rather than in the rewriter. Additionally, we may need to
wrap the replacement expressions in PHVs if the virtual generated
columns come from the nullable side of an outer join, similar to what
we do when pulling up subqueries.

Thanks
Richard