Thread: 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
>>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] 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.