From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: "lxndrkrlv@gmail.com" <lxndrkrlv@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17233: Incorrect behavior of DELETE command with bad
 subquery in WHERE clause
In-reply-to: 
 <CAKFQuwbnETbhnBYtMA8HNJwf0ipwAETd58=K0JzPs22Of2ZhcA@mail.gmail.com>
References: <17233-afb9d806aaa64b17@postgresql.org>
 <CAKFQuwbnETbhnBYtMA8HNJwf0ipwAETd58=K0JzPs22Of2ZhcA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Sun, 17 Oct 2021 07:15:48 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1409811.1634481258.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 17 Oct 2021 10:34:18 -0400
Message-ID: <1409812.1634481258@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1409812.1634481258%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The virtual join table doesn=E2=80=99t have a ctid, only physical tables=
 do, and
> the ctid of physical tables apparently aren=E2=80=99t propogated when th=
ey are
> joined.

We leave the system columns out of the join because otherwise they'd
surely conflict between the two sides of the join.  However, you could
still reference either one with "tmp1.ctid" or "tmp2.ctid".

There might be an opportunity here to improve the error message's hint:

regression=3D# SELECT CTID
regression-# FROM tmp1
regression-# INNER JOIN tmp2 ON tmp1.id =3D tmp2.id;
ERROR:  column "ctid" does not exist
LINE 1: SELECT CTID
               ^
HINT:  There is a column named "ctid" in table "tmp1", but it cannot be re=
ferenced from this part of the query.

"cannot be referenced" is probably a shade misleading, given the
availability of the qualified-name alternative.

			regards, tom lane