MIME-Version: 1.0
References: <a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org>
 <09800cb1-43aa-4239-8918-46b71ede4900@eisentraut.org> <536b281b-2cb3-4477-971f-c64838bc6263@eisentraut.org>
 <CAEZATCUHnmOu7RtDuP+PCtS_dqvU8hFsAYYFPdiB8gyFszj5WQ@mail.gmail.com>
 <51875199-d5f5-41ed-92dc-3e7b1b996991@eisentraut.org> <e5075513-2a0e-4550-bf45-41942af2b676@eisentraut.org>
 <84adfb1b-826c-42b2-a907-b327d3760c7e@eisentraut.org> <CAEZATCXQcjZPq+OsczFc13M6v+OP_NiqmUAcRAzi1K60KFuWmQ@mail.gmail.com>
 <831d7f99-0618-48e5-8557-f3d57137f339@eisentraut.org> <CAEZATCXnp5XTkUCsmfjLXVWZ7TaibWatnXDdu52J6zN3XsyZhw@mail.gmail.com>
 <64c2468c-3b69-44d7-863a-165931a70c1d@eisentraut.org> <b4139280-073a-4226-8bea-3ea9a3ad6e3f@eisentraut.org>
 <CAEZATCX6SHevKrVfAOq+88b4Ysm4Rqq2yeO6He3N0pttEbjYag@mail.gmail.com>
 <afb36e1f-8be9-4900-b29e-b095e30e9416@eisentraut.org> <CAEZATCVsfdp5MQpENpbrFAAfpC2tYU7y-Q=uOG57UubGy48Gyw@mail.gmail.com>
 <8be3512a-a23f-4088-a9ec-bf68650a2997@eisentraut.org> <75eb1a6f-d59f-42e6-8a78-124ee808cda7@gmail.com>
 <8d6a3d4c-f630-4cf9-aced-6cead95776e5@Spark> <CAMbWs4-h49s0ehfSq4CnztRLSwOF79ZQUkSPzGCQLNroAG7joA@mail.gmail.com>
 <CACJufxHVsCoxhqeDV973Hny3EtgCGztDJj=KKvErexnH=2k6yQ@mail.gmail.com>
 <12d5cce9-917f-4a99-86d3-715bb3b363bb@Spark> <CAMbWs4_xiGiB5Oufdn62cJ=MoiS9cJWLzq=jR_7xnTO8x_oPqg@mail.gmail.com>
In-Reply-To: <CAMbWs4_xiGiB5Oufdn62cJ=MoiS9cJWLzq=jR_7xnTO8x_oPqg@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 11 Feb 2025 17:15:24 +0800
Message-ID: <CAMbWs48AbYx+qLZ0cJnGuYvtic0uTZSZKYniC_Sxx6HWxNgmfQ@mail.gmail.com>
Subject: Re: Virtual generated columns
To: Zhang Mingli <zmlpostgres@gmail.com>
Cc: jian he <jian.universality@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, 
	Dean Rasheed <dean.a.rasheed@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs48AbYx%2BqLZ0cJnGuYvtic0uTZSZKYniC_Sxx6HWxNgmfQ%40mail.gmail.com>
Precedence: bulk

On Tue, Feb 11, 2025 at 10:34=E2=80=AFAM Richard Guo <guofenglinux@gmail.co=
m> 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 =3D vt.a where coalesce(vt.b, 1) =3D =
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 =3D vt.a where coalesce(vt.b, 1) =3D
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