Re: BUG #18451: NULL fails to coerce to string when performing string comparison
От | Darryl Dixon |
---|---|
Тема | Re: BUG #18451: NULL fails to coerce to string when performing string comparison |
Дата | |
Msg-id | CAMfewD3sqAr4eD25NZJvSnAp406+r6cVer-3=5HoQ1Nm0Wt6FA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: BUG #18451: NULL fails to coerce to string when performing string comparison (David Rowley <dgrowleyml@gmail.com>) |
Ответы |
Re: BUG #18451: NULL fails to coerce to string when performing string comparison
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: BUG #18451: NULL fails to coerce to string when performing string comparison (David Rowley <dgrowleyml@gmail.com>) |
Список | pgsql-bugs |
The bug is that:
1) The concatenation is not producing NULL, but rather Text, and yet the comparison fails regardless, and
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=#
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=#
Darryl Dixon
On Wed, 1 May 2024 at 09:25, Darryl Dixon <darryl.dixon@gmail.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:"NoteThe 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 DixonOn Wed, 1 May 2024 at 09:13, David G. Johnston <david.g.johnston@gmail.com> wrote: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.
On Wed, 1 May 2024 at 09:34, David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 1 May 2024 at 09:06, PG Bug reporting form
<noreply@postgresql.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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: David RowleyДата:
Сообщение: Re: BUG #18451: NULL fails to coerce to string when performing string comparison
Следующее
От: Thomas MunroДата:
Сообщение: Re: BUG #18452: [PostgreSQL and 16.1]: [Postgres.exe crash observed while installing the application]