Re: BUG #18451: NULL fails to coerce to string when performing string comparison

Hi,

There are a couple of notes regarding this:
1) There is an explicit CAST of NULL->text that silently fails (see
original example)
2) This behaviour, if expected, is not well documented/contradicted by
existing documentation. See the note here:
https://www.postgresql.org/docs/current/functions-string.html
"Note
The string concatenation operator (||) will accept non-string input, so
long as at least one input is of string type, as shown in Table 9.9. For
other cases, inserting an explicit coercion to text can be used to have
non-string input accepted."

Further, the extended details in the referenced table say:

"text || anynonarray → text

anynonarray || text → text

Converts the non-string input to text, then concatenates the two strings.
(The non-string input cannot be of an array type, because that would create
ambiguity with the array || operators. If you want to concatenate an
array's text equivalent, cast it to text explicitly.)

'Value: ' || 42 → Value: 42"

regards,
Darryl Dixon

On Wed, 1 May 2024 at 09:13, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Monday, April 29, 2024, PG Bug reporting form <noreply(at)postgresql(dot)org>
> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18451
>> Logged by: Darryl Dixon
>> Email address: darryl(dot)dixon(at)gmail(dot)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.
>
>