Re: Schema-qualify the equality operator when deparsing NULLIF/IS DISTINCT FROM

michal(dot)dtz(at)gmail(dot)com writes:
> A view that applies NULLIF or IS [NOT] DISTINCT FROM to a value whose
> "=" operator is not on the search_path (for example an hstore column)
> cannot be dumped and restored.

Yeah, this has been a known issue for a long time. I cataloged a
bunch of related cases at

https://www.postgresql.org/message-id/10492.1531515255%40sss.pgh.pa.us

but I missed JOIN USING, which also fails to mention exactly which
operator it resolved the semantics with. It doesn't seem hugely
helpful to fix one case without fixing them all, and fixing them all
is a lot of work :-(

> Proposed fix (in the deparser)
> ------------------------------
> When the equality operator would not be found by its bare name under the
> current search_path -- detected via generate_operator_name(), which
> already decides when the OPERATOR(...) decoration is required -- emit an
> equivalent expression that can carry the qualified operator, instead of
> the normal syntax:

>    NULLIF(a, b)
>        -> CASE WHEN a IS NOT NULL AND b IS NOT NULL AND (a OPERATOR(s.=) b)
>                THEN NULL ELSE a END

Don't like this approach one bit. While the output it produces might
be semantically equivalent (for non-volatile expressions anyway),
it's not equivalent performance-wise, especially not if the change
blocks any optimizations. Also, other cases such as JOIN USING really
can't be fixed without new syntax.

I'm kind of surprised that we haven't gotten more complaints since
2018, but there really haven't been all that many, so we never got
to the point of putting in the work to fix this topic properly.
If you feel motivated, though, have at it.

regards, tom lane