sql-queries: ERROR: could not decorrelate subquery since 20.2

[fabiog1901]

I'm running into a ERROR: could not decorrelate subquery when running the CTE which demonstrate an example on how you can re-write some stored procedures that use variables. These statements used to run up until 20.1, and return said error from 20.2. Where can I start please to rewrite the CTE, please?

create table transaction(
    transaction_uuid UUID PRIMARY KEY not null DEFAULT gen_random_uuid(),
    request_uuid UUID  not null DEFAULT gen_random_uuid() ,
    account_id STRING not null,
    debitCard_id STRING default null,
    transaction_am INT8 not null,
    transactionType_cd STRING not null,
    runningBalance_am INT8 not null default 0,
    reservation_uuid UUID default null,
    transactionMetaData_json JSONB default null,
    insert_tsz TIMESTAMPTZ not null default now()
);
CREATE INDEX ON transaction (request_uuid);
CREATE INDEX ON transaction (account_id);
create table account_balance(
    account_id STRING PRIMARY KEY not null,
    runningBalance_am INT8 not null default 0
);

-- LOAD some initial data
--
insert into account_balance select concat('A_', i::STRING), 100 from generate_series(1, 10) as i;
select * from account_balance order by 1;

-- the following withdraws $9... and updates account_balance and transaction marking it a valid txn…

WITH input_cte as (
   select column1 as aid, column2 as did, column3 as amt, column4 as ttype
   from (values('A_7', 'D_8', 9, 'DEBIT'))
),
update_acct as (
    UPDATE account_balance 
    SET runningBalance_am = 
       (select (CASE WHEN (runningBalance_am - (select amt from input_cte)) > 0 
                     THEN (select runningBalance_am - amt from input_cte) 
                     ELSE runningBalance_am 
                END) 
          from account_balance 
        where account_id= (select aid from input_cte))
        where account_id=(select aid from input_cte)
    returning (runningBalance_am)
)
    select
    aid,
    did,
    amt,
    ttype,
    runningBalance_am,
    (CASE WHEN (runningBalance_am - amt) >= 0 
            THEN '[{"VALID TXN}]' 
            ELSE '[{"BOGUS"}]' 
    END)
    from update_acct
    full outer join input_cte on (1=1)
;

Jira issue: CRDB-11651

[ajwerner]

Is this a duplicate of https://github.com/cockroachdb/cockroach/issues/67951? Consider converting the CASE to an IF

[fabiog1901]

Thanks Andrew, I tried as follows but it still errors out with the same message:

WITH input_cte AS (
   SELECT column1 AS aid, column2 AS did, column3 AS amt, column4 AS ttype
   FROM (values('A_7', 'A_8', 9, 'DEBIT'))
),
update_acct AS (
    UPDATE account_balance 
    SET runningBalance_am = (
            SELECT IF ((runningBalance_am - (SELECT amt FROM input_cte)) > 0, (SELECT runningBalance_am - amt FROM input_cte) , runningBalance_am) 
            FROM account_balance 
            WHERE account_id = (SELECT aid FROM input_cte)
        )
    WHERE account_id = (SELECT aid FROM input_cte)
    RETURNING (runningBalance_am)
)
    SELECT aid, did, amt, ttype, runningBalance_am, IF ((runningBalance_am - amt) >= 0, '[{"VALID TXN}]', '[{"BOGUS"}]' )
    FROM update_acct
    FULL OUTER JOIN input_cte ON (1=1)
;

[ajwerner]

Alright, well I'm out of my depth. Somebody from @cockroachdb/sql-queries will need to have a look.