public inbox for [email protected]  

help / color / mirror / Atom feed

From: Joe Wildish <[email protected]>
To: [email protected]
Subject: MERGE bug report
Date: Tue, 05 Apr 2022 23:17:30 +0100
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: MERGE bug report
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox