BUG #15421: Error: LIKE pattern must not end with escape character
The following bug has been logged on the website:
Bug reference: 15421
Logged by: Pavel Shapovalov
Email address: pavel@bnovo.ru
PostgreSQL version: 9.4.19
Operating system: Linux (4.9.0-4-amd64 #1 SMP Debian 4.9.65-3+deb9u)
Description:
Schema:
CREATE TABLE public.tmp (
id integer NOT NULL,
name text
);
Test data:
INSERT INTO tmp VALUES(1, 'test')
If I try to query the table with LIKE statement:
SELECT COUNT(id) AS "records_found" FROM "tmp" WHERE lower("tmp"."name")
LIKE 't\' I get the error "Error: LIKE pattern must not end with escape
character". The same situation is for statement SELECT COUNT(id) AS
"records_found" FROM "tmp" WHERE lower("tmp"."name") LIKE 'te\' and others
if the LIKE pattern matches the beginning of the name field in any record.
If I try to query the statement SELECT COUNT(id) AS "records_found" FROM
"tmp" WHERE lower("tmp"."name") LIKE 'rs\' and others that do not match any
records, there are no errors at all.
This behaviour is not clear from the documentation. Is it a bug or
undocumented feature?
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
If I try to query the table with LIKE statement:
SELECT COUNT(id) AS "records_found" FROM "tmp" WHERE lower("tmp"."name")
LIKE 't\' I get the error "Error: LIKE pattern must not end with escape
character".
If I try to query the statement SELECT COUNT(id) AS "records_found" FROM
"tmp" WHERE lower("tmp"."name") LIKE 'rs\' and others that do not match any
records, there are no errors at all.
Yeah, the pattern is wrong, but the error is only detected if scanning of
the pattern reaches the end, which it won't if the match always fails
before that.
This has been complained of several times before, but it's something
that doesn't seem worth taking any performance hit for. It's possible
that something along the lines of what I sketched in
/messages/by-id/28169.1489781085@sss.pgh.pa.us
would be close enough to zero net cost to be OK. Nobody's pursued it
though.
regards, tom lane