Re: MERGE bug report

On Wed, Apr 6, 2022 at 6:18 AM Joe Wildish <joe@lateraljoin.com> wrote:

> Hello Hackers,
>
> Reporting a bug with the new MERGE statement. Tested against
> 75edb919613ee835e7680e40137e494c7856bcf9.
>
> psql output as follows:
>
> ...
> psql:merge.sql:33: ERROR:  variable not found in subplan target lists
> ROLLBACK
> [local] joe@joe=# \errverbose
> ERROR:  XX000: variable not found in subplan target lists
> LOCATION:  fix_join_expr_mutator, setrefs.c:2800
>
> Stack trace:
>
> fix_join_expr_mutator setrefs.c:2800
> expression_tree_mutator nodeFuncs.c:3348
> fix_join_expr_mutator setrefs.c:2853
> expression_tree_mutator nodeFuncs.c:2992
> fix_join_expr_mutator setrefs.c:2853
> expression_tree_mutator nodeFuncs.c:3348
> fix_join_expr_mutator setrefs.c:2853
> fix_join_expr setrefs.c:2753
> set_plan_refs setrefs.c:1085
> set_plan_references setrefs.c:315
> standard_planner planner.c:498
> planner planner.c:277
> pg_plan_query postgres.c:883
> pg_plan_queries postgres.c:975
> exec_simple_query postgres.c:1169
> PostgresMain postgres.c:4520
> BackendRun postmaster.c:4593
> BackendStartup postmaster.c:4321
> ServerLoop postmaster.c:1801
> PostmasterMain postmaster.c:1473
> main main.c:202
> __libc_start_main 0x00007fc4ccc0b1e2
> _start 0x000000000048804e
>
> Reproducer script:
>
> BEGIN;
> DROP TABLE IF EXISTS item, incoming, source CASCADE;
>
> CREATE TABLE item
>   (order_id    INTEGER NOT NULL,
>    item_id     INTEGER NOT NULL,
>    quantity    INTEGER NOT NULL,
>    price       NUMERIC NOT NULL,
>    CONSTRAINT pk_item PRIMARY KEY (order_id, item_id));
>
> INSERT INTO item VALUES (100, 1, 4, 100.00), (100, 2, 9, 199.00);
>
> CREATE TABLE incoming (order_id, item_id, quantity, price)
>   AS (VALUES (100, 1, 4, 100.00), (100, 3, 1, 200.00));
>
> CREATE TABLE source (order_id, item_id, quantity, price) AS
>   (SELECT order_id, item_id, incoming.quantity, incoming.price
>      FROM item LEFT JOIN incoming USING (order_id, item_id));
>
> MERGE INTO item a
> USING source b
>    ON (a.order_id, a.item_id) =
>       (b.order_id, b.item_id)
>  WHEN NOT MATCHED
>  THEN INSERT (order_id, item_id, quantity, price)
>       VALUES (order_id, item_id, quantity, price)
>  WHEN MATCHED
>   AND a.* IS DISTINCT FROM b.*
>  THEN UPDATE SET (quantity, price) = (b.quantity, b.price)
>  WHEN MATCHED
>   AND (b.quantity IS NULL AND b.price IS NULL)
>  THEN DELETE;
> COMMIT;
>
> It seems related to the use of a.* and b.*
>

That's right. The varattno is set to zero for whole-row Var. And in this
case these whole-row Vars are not included in the targetlist.

Attached is an attempt for the fix.

Thanks
Richard