On Tue, Feb 11, 2025 at 10:34 AM Richard Guo <guofengli...@gmail.com> wrote: > 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.
It seems that outer-join removal does not work well with virtual generated columns. create table t (a int, b int); create table vt (a int primary key, b int generated always as (a * 2)); explain (costs off) select t.a from t left join vt on t.a = vt.a where coalesce(vt.b, 1) = 1; QUERY PLAN --------------- Seq Scan on t (1 row) This plan does not seem correct to me. The inner-rel attribute 'vt.b' is used above the join, which means the join should not be removed. explain (costs off) select t.a from t left join vt on t.a = vt.a where coalesce(vt.b, 1) = 1 or t.a is null; server closed the connection unexpectedly For this query, an Assert in remove_rel_from_query() is hit. I haven't looked into the details yet, but I suspect that both of these issues are caused by our failure to mark the correct nullingrel bits for the virtual generated columns. Thanks Richard
