On Wed, Apr 6, 2022 at 6:18 AM Joe Wildish <j...@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
0001-Include-entries-in-WHEN-conditions-into-processed_tl.patch
Description: Binary data
