Обсуждение: BUG #18451: NULL fails to coerce to string when performing string comparison

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)

postdb=# select where 'ab'||format('%s', NULL::text) like '%a%';
--
(1 row)

postdb=# 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)

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)

postdb=# select where 'ab'||format('%s', NULL::text) like '%a%';
--
(1 row)

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

Darryl Dixon <darryl.dixon@gmail.com> writes:
> 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.

I wonder what you think NULL means.  You certainly don't appear to
understand its semantics at all.  Every SQL data type allows NULL
as a value, so "casting NULL to text" doesn't change the value,
only what type the parser deems it to have.

> Further examples below:

Every one of these looks as-expected to me.

            regards, tom lane

On Wed, 1 May 2024 at 10:01, Darryl Dixon <darryl.dixon@gmail.com> wrote:
>
> The bug is that:
> 1) The concatenation is not producing NULL, but rather Text, and yet the comparison fails regardless, and

I think you're just wrongly assuming casting an unknown NULL to a type
is making it somehow non-NULL. That's just not the case. NULLs can
have a type. They're still NULL.

postgres=# select 'ab' || NULL::text is null;
 ?column?
----------
 t

David

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

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Fair, we don’t explicitly document that ‘…’ || NULL yields NULL.  It’s more
> of “unless otherwise noted doing stuff with null results in unknown/null
> outcomes”.

Yeah, there's a documentation gap here.  I dug around a little and
really couldn't find anything anywhere in our SGML docs that explains
NULL in any detail; we tend to assume that you've already heard of it.
There are a couple of parenthetical remarks in func.sgml that
reference the notion that NULL means "unknown", but you'd never
find those if you were looking for an explanation of what NULL is.
Perhaps that'd be worth a few paras somewhere, though I'm not
very sure where's a good place.

            regards, tom lane

On Wed, 1 May 2024 at 10:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Fair, we don’t explicitly document that ‘…’ || NULL yields NULL.  It’s more
> > of “unless otherwise noted doing stuff with null results in unknown/null
> > outcomes”.
>
> Yeah, there's a documentation gap here.  I dug around a little and
> really couldn't find anything anywhere in our SGML docs that explains
> NULL in any detail; we tend to assume that you've already heard of it.
> There are a couple of parenthetical remarks in func.sgml that
> reference the notion that NULL means "unknown", but you'd never
> find those if you were looking for an explanation of what NULL is.
> Perhaps that'd be worth a few paras somewhere, though I'm not
> very sure where's a good place.

What is really generic enough about SQL NULLs to put somewhere
generic? I mean NULL = NULL is NULL rather than true, but NULLs are
treated as equal in DISTINCT and GROUP BY. It seems to me it would be
hard to generically write something about it without referencing
specific situations, and if we have to do that, why don't we just
write it specifically in that location?

I see we already mention that false and NULL don't match in a WHERE
clause in [1], per "(i.e., if the result is false or null)".  Maybe
that could be expanded to elaborate more on 3-way SQL boolean logic
and [2] could be expanded to mention that concatenating a NULL yields
NULL.

David

[1] https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-WHERE
[2] https://www.postgresql.org/docs/current/functions-string.html

David Rowley <dgrowleyml@gmail.com> writes:
> On Wed, 1 May 2024 at 10:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, there's a documentation gap here.  I dug around a little and
>> really couldn't find anything anywhere in our SGML docs that explains
>> NULL in any detail; we tend to assume that you've already heard of it.

> What is really generic enough about SQL NULLs to put somewhere
> generic?

I think the key points I'd want to get across include:

* Any value can be NULL rather than a "normal" value of its datatype.

* There are various ways to think about the meaning of NULL, but
an often-useful viewpoint is that it represents an unknown value.

* A majority of SQL operations, including nearly all built-in
functions and operators, are strict meaning they yield NULL
if any input is NULL.  This is consistent with interpreting
NULL as "unknown".

Depending on how ambitious we wanted to be, we could add
examples illustrating these points.  For instance we could
explain the behavior of the boolean operators (such as
"true OR null => true", "true AND null => null") as being
consistent with the "unknown" interpretation.  Some of this
material could be moved or copied from existing text.  There
is attraction in centralizing the treatment, but on the
other hand those examples are all pretty on-point where
they are.  I wouldn't have a problem with being a bit
repetitious, though.  It's not like these facts are going
to change and need updates.

> I mean NULL = NULL is NULL rather than true, but NULLs are
> treated as equal in DISTINCT and GROUP BY.

It would not hurt to say that the SQL standard isn't 100%
logically consistent about how it handles NULLs.  But this
particular point isn't that bad: we could introduce the
definition of distinctness ("IS [NOT] DISTINCT FROM") and
then say that grouping operations use that behavior rather
than what "=" does.

The bottom line is that I think nowadays a lot of people
learn SQL from our documentation, rather than coming to
our docs with pre-existing SQL knowledge.  So we need to
fill in these sorts of explanatory gaps.

            regards, tom lane