On Mon, Feb 10, 2025 at 1:16 PM Zhang Mingli <zmlpostg...@gmail.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
