pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug / Unexpected behaviour of NOT LIKE
3+ messages / 3 participants
[nested] [flat]
* Bug / Unexpected behaviour of NOT LIKE
@ 2021-10-06 15:55 Judith Meyer <yutian.mei@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Judith Meyer @ 2021-10-06 15:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi,
I believe that I have found a bug or at least an undocumented unexpected
behaviour in the NOT LIKE operator (tested in PG 13). Namely, the query
SELECT * FROM users WHERE comment NOT LIKE 'hello%';
*never returns any users where the comment cell is empty, even though empty
also isn't "hello%".* This caused bugs in several parts of my code and
there is nothing about it in the documentation of the LIKE operator.
If this is intended behaviour, I believe that it deserves a big warning
note in the documentation: all occasions where someone might reasonably use
NOT LIKE should be combined with a IS NULL OR, i.e. "comment IS NULL OR
comment NOT LIKE 'hello%'"
Best wishes,
Judith Meyer
>>DiEM25
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug / Unexpected behaviour of NOT LIKE
@ 2021-10-06 16:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Judith Meyer <yutian.mei@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2021-10-06 16:52 UTC (permalink / raw)
To: Judith Meyer <yutian.mei@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Judith Meyer <yutian.mei@gmail.com> writes:
> I believe that I have found a bug or at least an undocumented unexpected
> behaviour in the NOT LIKE operator (tested in PG 13). Namely, the query
> SELECT * FROM users WHERE comment NOT LIKE 'hello%';
> *never returns any users where the comment cell is empty, even though empty
> also isn't "hello%".* This caused bugs in several parts of my code and
> there is nothing about it in the documentation of the LIKE operator.
If by "empty" you mean NULL, this is expected and well documented.
"null LIKE something" returns NULL just like most other operations
on nulls, and then NOT (NULL) is also NULL, and both of those make
sense given the interpretation that NULL means "unknown".
Admittedly, this is explained over in the coverage of boolean logic
operators [1] and not with LIKE specifically, but we're not going to
repeat it for every single operator in the system.
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-logical.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug / Unexpected behaviour of NOT LIKE
@ 2021-10-06 17:52 David G. Johnston <david.g.johnston@gmail.com>
parent: Judith Meyer <yutian.mei@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2021-10-06 17:52 UTC (permalink / raw)
To: Judith Meyer <yutian.mei@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Oct 6, 2021 at 9:30 AM Judith Meyer <yutian.mei@gmail.com> wrote:
> all occasions where someone might reasonably use NOT LIKE should be
>
done on fields that prohibit null values ensuring that, absent an outer
join, the check for null-ness is unnecessary.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-10-06 17:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-06 15:55 Bug / Unexpected behaviour of NOT LIKE Judith Meyer <yutian.mei@gmail.com>
2021-10-06 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-06 17:52 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox