public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey <[email protected]>
To: [email protected]
Subject: Unexpected results from a query with UNION ALL
Date: Fri, 3 Jan 2025 20:19:21 +0200
Message-ID: <CAOS4yi14bhxXUCZ_CQFeojAvBzAf9FebtjXsvOTm8KdXb8L14Q@mail.gmail.com> (raw)
Hi everyone,
Recently I got unexpected results from a query that seems to be legit.
The setup is like this:
-- setup
CREATE TABLE parents
(
id uuid not null primary key,
revision integer not null
);
CREATE TABLE children
(
id uuid not null primary key,
parent_id uuid not null references parents
);
INSERT INTO parents (id, revision)
VALUES ('ec422e09-55bb-4465-a990-31f59859959d', 1);
INSERT INTO children (id, parent_id)
VALUES ('5cb82ceb-c5ef-4c59-a02e-f7b610470f8c',
'ec422e09-55bb-4465-a990-31f59859959d');
INSERT INTO children (id, parent_id)
VALUES ('ce5b22b0-c6c4-4c09-826c-7086c53ee9ec',
'ec422e09-55bb-4465-a990-31f59859959d');
The query is:
-- query
SELECT children_union.id AS child_id
FROM parents p
JOIN (SELECT id, parent_id
FROM children
UNION ALL
SELECT null::uuid, null::uuid
WHERE false) children_union ON
children_union.parent_id = p.id
WHERE p.id = 'ec422e09-55bb-4465-a990-31f59859959d'
FOR UPDATE OF p;
It looks weird, but it's just a simplification of a much bigger query.
The 'SELECT null::uuid, null::uuid WHERE false' part was actually more
meaningful but I substituted it with a query that returns 0 rows after
finding out that it's irrelevant.
If I just run this query I get something that I would expect to get:
-- result 1
child_id
--------------------------------------
5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
ce5b22b0-c6c4-4c09-826c-7086c53ee9ec
(2 rows)
But if I lock the single row in the parents table:
-- concurrent query
BEGIN;
UPDATE parents
SET revision = revision + 1
WHERE id = 'ec422e09-55bb-4465-a990-31f59859959d';
and then run my query again in a separate session, then it's waiting
for the lock to be released. Once I commit the concurrent query and
release the lock, I get this:
-- result 2
child_id
--------------------------------------
5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
5cb82ceb-c5ef-4c59-a02e-f7b610470f8c
(2 rows)
but I would expect to get the same result as previously. Is it a bug
or am I doing something wrong here?
Thank you,
Andrii
view thread (4+ 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]
Subject: Re: Unexpected results from a query with UNION ALL
In-Reply-To: <CAOS4yi14bhxXUCZ_CQFeojAvBzAf9FebtjXsvOTm8KdXb8L14Q@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