Date: Mon, 10 Feb 2025 13:15:58 +0800
From: Zhang Mingli <zmlpostgres@gmail.com>
To: Richard Guo <guofenglinux@gmail.com>, jian he
 <jian.universality@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Dean Rasheed
 <dean.a.rasheed@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, 
 pgsql-hackers <pgsql-hackers@postgresql.org>
Message-ID: <12d5cce9-917f-4a99-86d3-715bb3b363bb@Spark>
In-Reply-To: <CACJufxHVsCoxhqeDV973Hny3EtgCGztDJj=KKvErexnH=2k6yQ@mail.gmail.com>
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>
Subject: Re: Virtual generated columns
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="67a98b93_3823e1f5_1738b"
Archived-At: <https://www.postgresql.org/message-id/12d5cce9-917f-4a99-86d3-715bb3b363bb%40Spark>
Precedence: bulk

--67a98b93_3823e1f5_1738b
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On =46eb 10, 2025 at 12:53 +0800, jian he <jian.universality=40gmail.com>=
, wrote:
>
> please check attached.
>
> > BTW, I was curious about what happens if the replacement expression i=
s
> > 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);
> >
> > =23 SELECT t2.a, t2.b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;
> > a =7C b
> > ---+---
> > =7C 2
> > =7C 2
> > (2 rows)
> >
> > Is this the expected behavior=3F I was expecting that t2.b should be
> > all NULLs.
> >
> SELECT t2.a, t2.b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;
> should be same as
> SELECT t2.a, 2 as b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;
> 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 adhe=
re to the same rules of join semantics.
I agree with Richard, the result seems incorrect. The right outcome shoul=
d be:
gpadmin=3D=23 SELECT t2.a, t2.b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;
=C2=A0a =7C b
------+------
=C2=A0NULL =7C NULL
=C2=A0NULL =7C NULL
(2 rows)


--
Zhang Mingli
HashData


--67a98b93_3823e1f5_1738b
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageReplySection=22>
<div dir=3D=22auto=22>On =46eb 10, 2025 at 12:53 +0800, jian he &lt;jian.=
universality=40gmail.com&gt;, wrote:</div>
<blockquote style=3D=22border-left-color: rgb(26, 188, 156); margin: 5px;=
 padding-left: 10px; border-left-width: thin; border-left-style: solid;=22=
><br />
please check attached.<br />
<br />
<blockquote style=3D=22border-left-color: rgb(230, 126, 34); margin: 5px;=
 padding-left: 10px; border-left-width: thin; border-left-style: solid;=22=
>BTW, I was curious about what happens if the replacement expression is<b=
r />
constant, so I tried running the query below.<br />
<br />
CREATE TABLE t (a int, b int GENERATED ALWAYS AS (1 + 1));<br />
INSERT INTO t VALUES (1);<br />
INSERT INTO t VALUES (2);<br />
<br />
=23 SELECT t2.a, t2.b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;<br />
a =7C b<br />
---+---<br />
=7C 2<br />
=7C 2<br />
(2 rows)<br />
<br />
Is this the expected behavior=3F I was expecting that t2.b should be<br /=
>
all NULLs.<br />
<br /></blockquote>
SELECT t2.a, t2.b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;<br />
should be same as<br />
SELECT t2.a, 2 as b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;<br />
so i think this is expected.</blockquote>
<div dir=3D=22auto=22>Hi,<br />
<br />
I believe virtual columns should behave like stored columns, except they =
don't actually use storage.<br />
Virtual columns are computed when the table is read, and they should adhe=
re to the same rules of join semantics.<br />
I agree with Richard, the result seems incorrect. The right outcome shoul=
d be:&=23160;<br />
gpadmin=3D=23 SELECT t2.a, t2.b =46ROM t t1 LE=46T JOIN t t2 ON =46ALSE;<=
br />
&=23160;a =7C b<br />
------+------<br />
&=23160;NULL =7C NULL<br />
&=23160;NULL =7C NULL<br />
(2 rows)<br />
<br />
<br />
--<br />
Zhang Mingli<br />
HashData<br />
<br />
<br />
<br />
<br /></div>
</div>
</body>
</html>

--67a98b93_3823e1f5_1738b--