On Monday, April 29, 2024, PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18451
Logged by: Darryl Dixon
Email address: darryl.dixon@gmail.com
PostgreSQL version: 14.11
Operating system: Ubuntu 22.04 64Bit
Description:
postdb=# select where 'ab' like '%a%';
--
(1 row)
postdb=# select where 'ab'||NULL like '%a%';
--
(0 rows)
postdb=# select where 'ab'||NULL::text like '%a%';
--
(0 rows)
You are mistaken in believing that concatenation involving null produces a non-null result. It is rather an inherently “strict” operation.
postdb=# select where 'ab'||format('%s', NULL::text) like '%a%';
--
(1 row)
Whereas format() is not “strict” and you choice of %s results in the documented empty string substitution.
David J.