CREATE TABLE PARENT_TABLE (
    ID INTEGER NOT NULL PRIMARY KEY,
    LINK_ID INTEGER
);

CREATE TABLE CHILD_TABLE (
    ID INTEGER NOT NULL PRIMARY KEY,
    PARENT_ID INTEGER,
    AMOUNT DOUBLE PRECISION,
    REF_ID INTEGER
);

INSERT INTO PARENT_TABLE (ID, LINK_ID) VALUES (1, NULL);

INSERT INTO CHILD_TABLE (ID, PARENT_ID, AMOUNT, REF_ID) VALUES (101, 1, 100, NULL);
INSERT INTO CHILD_TABLE (ID, PARENT_ID, AMOUNT, REF_ID) VALUES (102, 1, 100, NULL);
INSERT INTO CHILD_TABLE (ID, PARENT_ID, AMOUNT, REF_ID) VALUES (103, 1, 100, NULL);

-- EXPECTED: 1 row (OK with SubQueryConversion = false)
-- ACTUAL: 3 rows (same row repeated) with SubQueryConversion = true
SELECT
    p.id
FROM parent_table p
WHERE p.id = 1 AND
    EXISTS(
        SELECT 1
        FROM child_table c
        WHERE c.parent_id = p.id AND
            c.amount - COALESCE(
                (SELECT SUM(c2.amount)
                FROM child_table c2
                    JOIN parent_table p2 ON p2.link_id = p.id -- Correlation to outer p
                WHERE c2.ref_id = c.id), 0) > 0);

-- Same query WITHOUT the JOIN works correctly
SELECT
    p.id
FROM parent_table p
WHERE p.id = 1 AND
    EXISTS(
        SELECT 1
        FROM child_table c
        WHERE c.parent_id = p.id AND
            c.amount - COALESCE(
                (SELECT SUM(c2.amount)
                FROM child_table c2
                WHERE c2.ref_id = c.id), 0) > 0);

--Even DISTINCT does NOT help
SELECT DISTINCT p.id
FROM parent_table p
WHERE p.id = 1 AND
      EXISTS(SELECT 1
             FROM child_table c
             WHERE c.parent_id = p.id AND
                   c.amount - COALESCE((SELECT SUM(c2.amount)
                                        FROM child_table c2
                                        JOIN parent_table p2 ON p2.link_id = p.id
                                        WHERE c2.ref_id = c.id), 0) > 0);