public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Schema-qualify the equality operator when deparsing NULLIF/IS DISTINCT FROM
Date: Fri, 03 Jul 2026 10:11:26 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <028117fa-439b-4762-b647-130bc19df89c@Spark>
References: <028117fa-439b-4762-b647-130bc19df89c@Spark>

[email protected] 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

view thread (4+ messages)  latest in thread

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], [email protected]
  Subject: Re: Schema-qualify the equality operator when deparsing NULLIF/IS DISTINCT FROM
  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