MIME-Version: 1.0
From: Philip Hazelden <philip.hazelden@gmail.com>
Date: Mon, 9 Sep 2024 14:02:50 +0100
Message-ID: <CALB5dS9y7W395PNn_b90ex7jyYkirKnS2OdcDcjq=eT=J32NSA@mail.gmail.com>
Subject: Clarify this MERGE warning? "Only columns from the target table that
 attempt to match data_source rows should appear in join_condition."
To: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CALB5dS9y7W395PNn_b90ex7jyYkirKnS2OdcDcjq%3DeT%3DJ32NSA%40mail.gmail.com>
Precedence: bulk

The MERGE docs[1] give this warning:

> Only columns from the target table that attempt to match
> `data_source` rows should appear in `join_condition`.
> `join_condition` subexpressions that only reference the target
> table's columns can affect which action is taken, often in
> surprising ways.

(The docs for upcoming v17 have the same line.)

But when I tested this, it seems to work fine. For example, consider a
two-level primary key, where the source table implicitly has a fixed
value for one level:

    create table t1 (k1 int, k2 int, v text);
    insert into t1 values
      (1, 1, '1.1'), (1, 2, '1.2'),
      (2, 1, '2.1'), (2, 2, '2.2'), (2, 3, '2.3');

    create table t2 (k2 int, v text);
    insert into t2 values (1, '1.1 v2'), (3, '1.3 v2');

    merge into t1 using t2
      on t1.k2 = t2.k2 and t1.k1 = 1
      when matched then update set v = t2.v
      when not matched then insert values (1, t2.k2, t2.v);

`t1` now contains

     k1 | k2 |   v
    ----+----+--------
      1 |  1 | 1.1 v2
      1 |  2 | 1.2
      1 |  3 | 1.3 v2
      2 |  1 | 2.1
      2 |  2 | 2.2
      2 |  3 | 2.3
    (6 rows)

Which is what I'd expect.

So why should I avoid doing this? It's not clear to me whether the
warning is saying "this likely won't work like you expect because it's
difficult to reason about" or "because the behavior is unspecified" or
"because there's a bug" or what.

I found a thread[2] on the psql-hackers list which has this snippet of
conversation:

>>> * It might make sense to point out in the docs that join_condition
>>> should not filter the target table too much. Like SQL server docs say,
>>> don't put things in the join that filter the target that actually
>>> belong in the WHEN .. AND quals. In a way, this should be obvious,
>>> because it's an outer join. But I don't think it is, and ISTM that the
>>> sensible thing to do is to warn against it.
>>>
>>
>> Hmm, ok. Not sure how exactly to put that in words without confusing users.
>> Do you want to suggest something?
>
> Perhaps a Warning box should say:
>
> Only columns from "target_table_name" that attempt to match
> "data_source" rows should appear in "join_condition".
> "join_condition" subexpressions that only reference
> "target_table_name" columns can only affect which action is taken,
> often in surprising ways.

Notably, the "only affect" became simply "affect" in the docs, which I
think is less clear.

This makes me think the warning is trying to say something like: "if
you can move a subexpression from `join_condition` to `WHEN ... AND`,
you should probably do so". Is that right?

(I still don't know *why* I should do that. It sounds like maybe it's
more efficient that way because this is an outer join? But I don't
know why that matters. If I'd had to guess which would be more
efficient, I'd have weakly guessed "prefer to do it in a join, indexes
will get used better that way".)

And I think it's not always possible to move a subexpression without
changing `data_source`. In the example I posted above, I don't think
it would work, since we'd no longer have a `NOT MATCHED` on `t2`'s
`(3, '1.3 v2')` row.

[1]: https://www.postgresql.org/docs/current/sql-merge.html
[2]: https://www.postgresql.org/message-id/flat/CANP8%2BjKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc%2BXrz8QB0nXA%40mail.gmail.com