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

The bug is that:
1) The concatenation is not producing NULL, but rather Text, and yet the
comparison fails regardless, and
2) The explicit CAST of NULL -> Text fails to remedy this.

Further examples below:

postdb=# SELECT pg_typeof(NULL);
pg_typeof
-----------
unknown
(1 row)

postdb=# SELECT pg_typeof(NULL::text);
pg_typeof
-----------
text
(1 row)

postdb=# SELECT pg_typeof('ab'||NULL::text);
pg_typeof
-----------
text
(1 row)

postdb=# SELECT pg_typeof('ab'||NULL);
pg_typeof
-----------
text
(1 row)

postdb=# select where 'ab'||(NULL::text) like '%a%';

--
(0 rows)

postdhb=# select version();
version

----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

ems=#

regards,
Darryl Dixon

On Wed, 1 May 2024 at 09:25, Darryl Dixon <darryl(dot)dixon(at)gmail(dot)com> wrote:

> 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.
>>
>>
On Wed, 1 May 2024 at 09:34, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 1 May 2024 at 09:06, PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> > postdb=# select where 'ab'||NULL like '%a%';
> > --
> > (0 rows)
> >
> > postdb=# select where 'ab'||NULL::text like '%a%';
> > --
> > (0 rows)
>
> You've not mentioned where you think the bug is, but if you think it's
> in either of the above, PostgreSQL is following the SQL standard here.
>
> The SQL2016 copy I have here says:
>
> "6.31 <string value expression>
>
> If at least one of S1 and S2 is the null value, then the result of the
> <concatenation> is the null value."
>
> There are a few RDBMSs that are lax on this rule, perhaps you're here
> because PostgreSQL isn't doing what you're used to?
>
> David
>