Bug / Unexpected behaviour of NOT LIKE
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
Show quoted text
DiEM25
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]https://www.postgresql.org/docs/current/functions-logical.html 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
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.