Re: MERGE bug report

On Tue, Apr 5, 2022 at 3:35 PM Zhihong Yu <zyu(at)yugabyte(dot)com> wrote:

>
>
> On Tue, Apr 5, 2022 at 3:18 PM 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.*
>>
>> 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 .
>

Pardon - I typed too fast:

The call to fix_join_expr_mutator() is on this line (3348):

resultlist = lappend(resultlist,
mutator((Node *) lfirst(temp),
context));