Re: MERGE bug report

On Wed, Apr 6, 2022 at 6:18 AM Joe Wildish <joe(at)lateraljoin(dot)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(at)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