MIME-Version: 1.0
From: Andrey <adnyre@gmail.com>
Date: Fri, 3 Jan 2025 20:19:21 +0200
Message-ID: <CAOS4yi14bhxXUCZ_CQFeojAvBzAf9FebtjXsvOTm8KdXb8L14Q@mail.gmail.com>
Subject: Unexpected results from a query with UNION ALL
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ffc650062ad151c3"
Archived-At: <https://www.postgresql.org/message-id/CAOS4yi14bhxXUCZ_CQFeojAvBzAf9FebtjXsvOTm8KdXb8L14Q%40mail.gmail.com>
Precedence: bulk

--000000000000ffc650062ad151c3
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ffc650062ad151c3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div style=3D"color:rgb(8,8,8)"><pre style=3D"font-fa=
mily:&quot;JetBrains Mono&quot;,monospace">Hi everyone,<br><br>Recently I g=
ot unexpected results from a query that seems to be legit.<br>The setup is =
like this:<br><br>-- setup<br>CREATE TABLE parents<br>(<br>=C2=A0 =C2=A0 id=
 =C2=A0 =C2=A0 =C2=A0 uuid =C2=A0 =C2=A0not null primary key,<br>=C2=A0 =C2=
=A0 revision integer not null<br>);<br>CREATE TABLE children<br>(<br>=C2=A0=
 =C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0uuid =C2=A0 =C2=A0not null primary ke=
y,<br>=C2=A0 =C2=A0 parent_id uuid =C2=A0 =C2=A0not null references parents=
<br>);<br>INSERT INTO parents (id, revision)<br>VALUES (&#39;ec422e09-55bb-=
4465-a990-31f59859959d&#39;, 1);<br>INSERT INTO children (id, parent_id)<br=
>VALUES (&#39;5cb82ceb-c5ef-4c59-a02e-f7b610470f8c&#39;, &#39;ec422e09-55bb=
-4465-a990-31f59859959d&#39;);<br>INSERT INTO children (id, parent_id)<br>V=
ALUES (&#39;ce5b22b0-c6c4-4c09-826c-7086c53ee9ec&#39;, &#39;ec422e09-55bb-4=
465-a990-31f59859959d&#39;);<br><br>The query is:<br><br>-- query<br>SELECT=
 <a href=3D"http://children_union.id">children_union.id</a> AS child_id<br>=
FROM parents p<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0JOIN (SELECT id, parent=
_id<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 FROM children<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 UNION ALL<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT null::uuid, null::uuid<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE false) childr=
en_union ON children_union.parent_id =3D <a href=3D"http://p.id">p.id</a><b=
r>WHERE <a href=3D"http://p.id">p.id</a> =3D &#39;ec422e09-55bb-4465-a990-3=
1f59859959d&#39;<br>=C2=A0 =C2=A0 FOR UPDATE OF p;<br><br>It looks weird, b=
ut it&#39;s just a simplification of a much bigger query. The &#39;SELECT n=
ull::uuid, null::uuid WHERE false&#39; part was actually more meaningful bu=
t I substituted it with a query that returns 0 rows after finding out that =
it&#39;s irrelevant.<br><br>If I just run this query I get something that I=
 would expect to get:<br><br>-- result 1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0child_id<br>--------------------------------------<=
br>=C2=A05cb82ceb-c5ef-4c59-a02e-f7b610470f8c<br>=C2=A0ce5b22b0-c6c4-4c09-8=
26c-7086c53ee9ec<br>(2 rows)<br><br>But if I lock the single row in the par=
ents table:<br><br>-- concurrent query<br>BEGIN;<br>UPDATE parents<br>SET r=
evision =3D revision + 1<br>WHERE id =3D &#39;ec422e09-55bb-4465-a990-31f59=
859959d&#39;;<br><br>and then run my query again in a separate session, the=
n it&#39;s waiting for the lock to be released. Once I commit the concurren=
t query and release the lock, I get this:<br><br>-- result 2<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0child_id<br>------------------=
--------------------<br>=C2=A05cb82ceb-c5ef-4c59-a02e-f7b610470f8c<br>=C2=
=A05cb82ceb-c5ef-4c59-a02e-f7b610470f8c<br>(2 rows)<br><br>but I would expe=
ct to get the same result as previously. Is it a bug or am I doing somethin=
g wrong here?<br><br>Thank you,<br>Andrii</pre></div></div></div>

--000000000000ffc650062ad151c3--