From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: lxndrkrlv@gmail.com
Subject: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
Date: Sat, 16 Oct 2021 19:41:52 +0000
Message-ID: <17233-afb9d806aaa64b17@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17233
Logged by: Alexander Korolev
Email address: lxndrkrlv@gmail.com
PostgreSQL version: 14.0
Operating system: Windows
Description:
This SELECT command fails as expected:
SELECT CTID FROM tmp1 INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE;
-- ERROR: column "ctid" does not exist.
But if I use same SELECT in WHERE clause of DELETE command
DELETE FROM tmp1 WHERE CTID in (
SELECT CTID FROM tmp1 INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR
UPDATE);
this command is executed without errors.
Repro
--------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_temp.tmp1;
DROP TABLE IF EXISTS pg_temp.tmp2;
CREATE TEMPORARY TABLE tmp1 (id int NOT NULL, name text);
CREATE TEMPORARY TABLE tmp2 (id int NOT NULL, name text);
INSERT INTO tmp1 (id, name) VALUES (1, 'aaa'), (2, 'bbb');
INSERT INTO tmp2 (id, name) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'eee');
SELECT ctid, * from tmp1;
SELECT ctid, * from tmp2;
/* Works as expected: ERROR: column "ctid" does not exist
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE;
*/
// Executed without errors !!!
DELETE FROM tmp1
WHERE CTID in (
SELECT CTID
FROM tmp1
INNER JOIN tmp2 ON tmp1.id = tmp2.id FOR UPDATE
);
SELECT * FROM tmp1;
SELECT * FROM tmp2;
--------------------------------------------------------------------------------------------
* in some cases, if tables is big, server process is hang. I can't make
small reproducible example.
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: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, lxndrkrlv@gmail.com
Subject: Re: BUG #17233: Incorrect behavior of DELETE command with bad subquery in WHERE clause
In-Reply-To: <17233-afb9d806aaa64b17@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox