Re: Virtual generated columns

On Feb 10, 2025 at 12:53 +0800, jian he <jian(dot)universality(at)gmail(dot)com>, wrote:
>
> please check attached.
>
> > BTW, I was curious about what happens if the replacement expression is
> > constant, so I tried running the query below.
> >
> > CREATE TABLE t (a int, b int GENERATED ALWAYS AS (1 + 1));
> > 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
> > ---+---
> > | 2
> > | 2
> > (2 rows)
> >
> > Is this the expected behavior? I was expecting that t2.b should be
> > all NULLs.
> >
> SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
> should be same as
> SELECT t2.a, 2 as b FROM t t1 LEFT JOIN t t2 ON FALSE;
> so i think this is expected.
Hi,

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)

--
Zhang Mingli
HashData