From: Tom Lane <tgl@sss.pgh.pa.us>
To: Judith Meyer <yutian.mei@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Bug / Unexpected behaviour of NOT LIKE
Date: Wed, 06 Oct 2021 12:52:54 -0400
Message-ID: <4148675.1633539174@sss.pgh.pa.us> (raw)
In-Reply-To: <CAD9YNfx05sRf2DSd4Z5VvudYuS_Wxk7HD9L5-=Ez8QaAJY3qzA@mail.gmail.com>
References: <CAD9YNfx05sRf2DSd4Z5VvudYuS_Wxk7HD9L5-=Ez8QaAJY3qzA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, yutian.mei@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Bug / Unexpected behaviour of NOT LIKE
In-Reply-To: <4148675.1633539174@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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