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
In-reply-to: 
 <CAD9YNfx05sRf2DSd4Z5VvudYuS_Wxk7HD9L5-=Ez8QaAJY3qzA@mail.gmail.com>
References: 
 <CAD9YNfx05sRf2DSd4Z5VvudYuS_Wxk7HD9L5-=Ez8QaAJY3qzA@mail.gmail.com>
Comments: In-reply-to Judith Meyer <yutian.mei@gmail.com>
	message dated "Wed, 06 Oct 2021 17:55:20 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4148674.1633539174.1@sss.pgh.pa.us>
Date: Wed, 06 Oct 2021 12:52:54 -0400
Message-ID: <4148675.1633539174@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4148675.1633539174%40sss.pgh.pa.us>
Precedence: bulk

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