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
