Re: Virtual generated columns

On Tue, Feb 11, 2025 at 10:34 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> 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)
>

Now I agree with you.
I think the following two should return the same result.

SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
SELECT t2.a, t2.b FROM t t1 LEFT JOIN (select * from t) t2 ON FALSE;

------------------------
atatch refined patch solves the failure to copy the nullingrel bits
for the virtual generated columns.
in ReplaceVarsFromTargetList_callback.
I tried to just use add_nulling_relids, but failed, so I did the
similar thing as SetVarReturningType.

I didn't solve the out join semantic issue.
i am wondering, can we do the virtual generated column expansion in
the rewrite stage as is,
and wrap the expressions in PHVs if the virtual generated
columns come from the nullable side of an outer join.
I am looking at pullup_replace_vars_callback, but it seems not very
helpful to us.