The result of a WITH-query in pg-mem depends on the sequence of queries

[Igggr]

Each sub-query in "WITH" runs on the same "snapshot" of the table in postgresql, but not in pg-mem

I have a table that stores user balances. When user balance is subtracted it should first subtract from balance.bonus. Only when balance.bonus became 0 balance.main should be decreased. And I also must know how many was subtracted. And I must do it in one query to avoid data races.

I solve this using "WITH", but this query give a different result in postgres and pg-mem. UPDATE part work well in both cases, but result of SELECT differ. In postgres it perform calculation using old balances (before UPDATE operation is run), in pg-mem however it perform calculation using new balances (after UPDATE operation is run) .

To Reproduce

result in postgress { main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}

result in pg-mem { main: 140 bonus: 0, mainDiff: 100 , bonusDiff: 0}

CREATE TABLE "balance" (
    "id" SERIAL NOT NULL, 
    "main" integer NOT NULL DEFAULT 0,
    "bonus" integer NOT NULL DEFAULT 0,
    "userId" integer,
    CONSTRAINT "REL_9297a70b26dc787156fa49de26" UNIQUE ("userId"),
    CONSTRAINT "PK_079dddd31a81672e8143a649ca0" PRIMARY KEY ("id")
);

INSERT INTO "balance" ("main", "bonus", "userId") VALUES (200, 60, 1);

WITH 
    sel AS (
        SELECT
            (CASE
                WHEN 100 <= balance."bonus" THEN 0
                ELSE 100 - balance."bonus"
            END) AS "mainDiff", 
            (CASE
                WHEN 100 <= balance."bonus" THEN 100
                ELSE balance."bonus"
            END) AS "bonusDiff"
        FROM "balance" WHERE "userId" = 1
    ),
    upd AS (
        UPDATE 
            "balance" SET "main" =
                (CASE
                    WHEN 100 <= balance."bonus" THEN balance."main"
                    ELSE balance."main" - 100 + balance."bonus"
                END),
            "bonus" =
                (CASE
                    WHEN 100 <= balance."bonus" THEN balance."bonus" - 100
                    ELSE 0
                END)  
        WHERE "userId" = 1
        RETURNING "main", "bonus"
    )
        SELECT *
        FROM sel, upd LIMIT 1;

But if i change last line to FROM upd, sel LIMIT 1, then pg-mem will give me correct result (the same as postgresql)

result in postgress { main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}

result in pg-mem { main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}

CREATE TABLE "balance" (
    "id" SERIAL NOT NULL, 
    "main" integer NOT NULL DEFAULT 0,
    "bonus" integer NOT NULL DEFAULT 0,
    "userId" integer,
    CONSTRAINT "REL_9297a70b26dc787156fa49de26" UNIQUE ("userId"),
    CONSTRAINT "PK_079dddd31a81672e8143a649ca0" PRIMARY KEY ("id")
);

INSERT INTO "balance" ("main", "bonus", "userId") VALUES (200, 60, 1);

WITH 
    sel AS (
        SELECT
            (CASE
                WHEN 100 <= balance."bonus" THEN 0
                ELSE 100 - balance."bonus"
            END) AS "mainDiff", 
            (CASE
                WHEN 100 <= balance."bonus" THEN 100
                ELSE balance."bonus"
            END) AS "bonusDiff"
        FROM "balance" WHERE "userId" = 1
    ),
    upd AS (
        UPDATE 
            "balance" SET "main" =
                (CASE
                    WHEN 100 <= balance."bonus" THEN balance."main"
                    ELSE balance."main" - 100 + balance."bonus"
                END),
            "bonus" =
                (CASE
                    WHEN 100 <= balance."bonus" THEN balance."bonus" - 100
                    ELSE 0
                END)  
        WHERE "userId" = 1
        RETURNING "main", "bonus"
    )
        SELECT *
        FROM upd, sel LIMIT 1;

pg-mem version

2.7.4