Обсуждение: 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 https://www.postgresql.org/message-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