Re: BUG #16545: COALESCE evaluates arguments to the right of the first non-null argument

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> after upgrading PostgreSQL from 10 to 11 I have found out different
> behaviour of COALESCE function.

> It seems to me that it became to evaluate arguments to the right of the
> first non-null argument which is in contradiction with documentation:

> "Like a CASE expression, COALESCE only evaluates the arguments that are
> needed to determine the result; that is, arguments to the right of the first
> non-null argument are not evaluated. This SQL-standard function provides
> capabilities similar to NVL and IFNULL, which are used in some other
> database systems."

The manual also explains that constant subexpressions will be evaluated
no matter what. See

https://www.postgresql.org/docs/10/sql-expressions.html#SYNTAX-EXPRESS-EVAL

particularly the examples involving CASE, which works pretty much
like COALESCE. Nothing about that has changed in a very long time.
I believe the specific case you show here has changed behavior
because PG 11 got smarter about constant-folding array subscription
operations.

PG10:
# explain verbose select (xpath('/tag/text()','<tag>[</tag>'))[1];
QUERY PLAN
-------------------------------------------
Result (cost=0.00..0.01 rows=1 width=32)
Output: ('{[}'::xml[])[1]
(2 rows)

later branches:
# explain verbose select (xpath('/tag/text()','<tag>[</tag>'))[1];
QUERY PLAN
-------------------------------------------
Result (cost=0.00..0.01 rows=1 width=32)
Output: '['::xml
(2 rows)

So now it will try to reduce the ~ operator to a constant at plan
time, where before it could not do that.

If you need an optimization fence to prevent this, there are ways
to accomplish that. One of the more reliable ones is to wrap the
~ operator in a volatile plpgsql function. That's usually pretty
disastrous for query performance though, so I recommend trying to
avoid the need for it.

regards, tom lane