Unexpected results from a query with UNION ALL - Mailing list pgsql-general

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