public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: Zhang Mingli <[email protected]>
Cc: jian he <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Dean Rasheed <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Virtual generated columns
Date: Tue, 11 Feb 2025 17:15:24 +0800
Message-ID: <CAMbWs48AbYx+qLZ0cJnGuYvtic0uTZSZKYniC_Sxx6HWxNgmfQ@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4_xiGiB5Oufdn62cJ=MoiS9cJWLzq=jR_7xnTO8x_oPqg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<CAEZATCUHnmOu7RtDuP+PCtS_dqvU8hFsAYYFPdiB8gyFszj5WQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAEZATCXQcjZPq+OsczFc13M6v+OP_NiqmUAcRAzi1K60KFuWmQ@mail.gmail.com>
<[email protected]>
<CAEZATCXnp5XTkUCsmfjLXVWZ7TaibWatnXDdu52J6zN3XsyZhw@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAEZATCX6SHevKrVfAOq+88b4Ysm4Rqq2yeO6He3N0pttEbjYag@mail.gmail.com>
<[email protected]>
<CAEZATCVsfdp5MQpENpbrFAAfpC2tYU7y-Q=uOG57UubGy48Gyw@mail.gmail.com>
<[email protected]>
<[email protected]>
<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>
On Tue, Feb 11, 2025 at 10:34 AM Richard Guo <[email protected]> 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
view thread (29+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Virtual generated columns
In-Reply-To: <CAMbWs48AbYx+qLZ0cJnGuYvtic0uTZSZKYniC_Sxx6HWxNgmfQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox