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

The following bug has been logged on the website:

Bug reference:      16545
Logged by:          Petr Jac
Email address:      jac@uol.cz
PostgreSQL version: 12.3
Operating system:   docker@ubuntu20.04
Description:

Hello,

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."

I can demonstrate this with example running in pure docker postgres
instances:

### PostgreSQL 10.13
postgres=# SELECT version();
                                                              version
                                                       

------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.13 (Debian 10.13-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

postgres=# SELECT coalesce((SELECT 'ONE'),
                (SELECT 'TWO'
                  WHERE '123' ~
((xpath('/tag/text()','<tag>[</tag>'))[1]::TEXT)
                )
);
 coalesce 
----------
 ONE
(1 row)

### PostgreSQL 11.8
postgres=# SELECT version();
                                                             version
                                                     

----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.8 (Debian 11.8-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

postgres=# SELECT coalesce((SELECT 'ONE'),
                (SELECT 'TWO'
                  WHERE '123' ~
((xpath('/tag/text()','<tag>[</tag>'))[1]::TEXT)
                )
);
ERROR:  invalid regular expression: brackets [] not balanced

### PostgreSQL 12.3
postgres=# SELECT version();
                                                     version
                                     
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

postgres=# SELECT coalesce((SELECT 'ONE'),
                (SELECT 'TWO'
                  WHERE '123' ~
((xpath('/tag/text()','<tag>[</tag>'))[1]::TEXT)
                )
);
ERROR:  invalid regular expression: brackets [] not balanced

---
Thank you

PG Bug reporting form <noreply@postgresql.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