MERGE when not matched should not require aliasing the column names in the VALUES clause

[mdesmet]

Currently Trino requires the user to alias the to be inserted values in a MERGE when not matched clause.

merge into target as t
        using source as s
        on t.id = s.id
    when matched then update set
...
    when not matched then insert
        (a, b, c)
    values
        (s.a, s.b, s.c)

This seems counter-intuitive as in the not matched clause there are no matched records, therefore there is no ambiguity.

The following query should work, now failing because of ambiguous column names.

merge into target as t
        using source as s
        on t.id = s.id
    when matched then update set
...
    when not matched then insert
        (a, b, c)
    values
        (a, b, c)

Note that above query works in other DBMS. So it is beneficial to solve this issue in order to reduce migration efforts towards Trino.

[djsstarburst]

@martint and I spent a long time yesterday looking into this.

We concluded that the predicate in a WHEN NOT MATCHED AND predicate THEN INSERT VALUES (a, b, c) must be evaluated in the join scope as the current SQL MERGE implementation does.

We also found, as @mdesmet reported, that the spec says that the inserted values should be scoped to the source, and table name aliases for source table columns should be optional.

@martint spent some time looking at changing the scoping to satisfy the scoping rules for insert values, and concluded that it would take some re-engineering of the scoping mechanisms, and he would have to dedicate some time to do it.