On Tue, Apr 5, 2022 at 3:18 PM 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.* > > Sorry I can't be more specific. Error manifests when planning occurs and > that is well outside of my code base knowledge. > > Hope this helps. > > Cheers, > -Joe > Hi, It seems all the calls to fix_join_expr_mutator() are within setrefs.c I haven't found where in nodeFuncs.c fix_join_expr_mutator is called. I am on commit 75edb919613ee835e7680e40137e494c7856bcf9 .
