agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
4+ messages / 3 participants
[nested] [flat]
* BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
@ 2026-09-18 07:31 PG Bug reporting form <noreply@postgresql.org>
2026-09-25 04:59 ` Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-09-18 07:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
The following bug has been logged on the website:
Bug reference: 19699
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux/amd64
Description:
PostgreSQL version: PostgreSQL 20devel at
a12600b762c36d91450ce085fa25ef75250bc1c2; PostgreSQL 18.6; PostgreSQL 17.11
Operating system: Linux/amd64
Description
-----------
LIKE does not reject a pattern ending in its active escape character when
the input string is empty. Both the default backslash escape and a custom
escape silently return instead of raising SQLSTATE 22025. The equivalent
cases with nonempty input raise 22025.
Steps to reproduce
------------------
Run the following input with psql:
BEGIN;
CREATE TEMP TABLE bugseer_postgres_00013_like_escape_results
(
case_name text PRIMARY KEY,
returned_sqlstate text
);
DO $block$
DECLARE
state text;
BEGIN
PERFORM ''::text LIKE E'\\';
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('empty_default', NULL);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS state = RETURNED_SQLSTATE;
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('empty_default', state);
END
$block$;
DO $block$
DECLARE
state text;
BEGIN
PERFORM 'x'::text LIKE E'\\';
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('nonempty_default', NULL);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS state = RETURNED_SQLSTATE;
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('nonempty_default', state);
END
$block$;
DO $block$
DECLARE
state text;
BEGIN
PERFORM ''::text LIKE '#' ESCAPE '#';
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('empty_custom', NULL);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS state = RETURNED_SQLSTATE;
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('empty_custom', state);
END
$block$;
DO $block$
DECLARE
state text;
BEGIN
PERFORM 'x'::text LIKE '#' ESCAPE '#';
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('nonempty_custom', NULL);
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS state = RETURNED_SQLSTATE;
INSERT INTO bugseer_postgres_00013_like_escape_results VALUES
('nonempty_custom', state);
END
$block$;
TABLE bugseer_postgres_00013_like_escape_results;
SELECT count(*) = 4 AS all_cases_ran,
bool_and(coalesce(returned_sqlstate = '22025', false)) AS
oracle_all_rejected
FROM bugseer_postgres_00013_like_escape_results;
ROLLBACK;
Actual result
-------------
case_name | returned_sqlstate
------------------+-------------------
empty_default |
nonempty_default | 22025
empty_custom |
nonempty_custom | 22025
(4 rows)
all_cases_ran | oracle_all_rejected
---------------+---------------------
t | f
(1 row)
Expected result
---------------
Every pattern ending in its active escape character should raise SQLSTATE
22025, regardless of whether the input string is empty or nonempty. All four
returned_sqlstate values should therefore be 22025 and oracle_all_rejected
should be true.
Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and
PostgreSQL 17.11.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
2026-09-18 07:31 BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-25 04:59 ` shihao zhong <zhong950419@gmail.com>
2026-09-25 05:05 ` Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: shihao zhong @ 2026-09-25 04:59 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
Hi,
Same issue as bug #18765 [1]. The trailing escape check only runs when
matching reaches the end of the pattern, so 'x' LIKE 'y\' and
'x' LIKE 'x\' also return false.
Tom's concern there was the cost of an extra pass over the pattern. We
don't need one. A pattern ends with an escape exactly when it ends with
an odd number of backslashes, so the check looks at the last byte and
usually stops there. The attached patch does that at the top of
MatchText(). 0002 adds tests and is optional.
Master only, I think, since some queries that return false today will
now fail.
The planner still reads 'abc\' as an exact match for 'abc'. So an index
scan that finds no 'abc' rows returns nothing instead of failing. I left
that alone, but can make like_fixed_prefix() throw too if wanted.
[1] https://postgr.es/m/18765-6c26d2047e6f5143@postgresql.org
Thanks,
Shihao
Attachments:
[application/octet-stream] v1-0001-Reject-LIKE-patterns-that-end-with-an-escape-what.patch (2.7K, ../../CAGRkXqS3zxLWfmTF3q4oe7hunOqAAjjFMa_PB2Dvzjo7P3a5Hw@mail.gmail.com/3-v1-0001-Reject-LIKE-patterns-that-end-with-an-escape-what.patch)
download | inline diff:
From fc55b9d81938c4121804a5f4d1dce19561e042cb Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 25 Sep 2026 00:55:47 -0400
Subject: [PATCH v1 1/2] Reject LIKE patterns that end with an escape, whatever
the input
MatchText() raised "LIKE pattern must not end with escape character"
only when matching got to the end of the pattern. If the text ran out
first, or an earlier character did not match, the bad pattern quietly
returned false. So '' LIKE '\' and 'x' LIKE 'y\' returned false, while
'xy' LIKE 'x\' raised the error.
Check for this before matching. A pattern ends with an escape exactly
when it ends with an odd number of backslashes, so we only need to look
back from the last byte. Usually that byte is not a backslash and we
stop there. This avoids the extra pass over the whole pattern that was
the objection when this came up in bug #18765.
This turns some queries that returned false into errors, so no
back-patch, same as 3d8fd757326 and ece869b11ee.
Reported-by: Qifan Liu <imchifan@163.com>
Reported-by: Anmol Mohanty <anmol.mohanty@salesforce.com>
Discussion: https://postgr.es/m/19699-dbaa58bbf8db1859@postgresql.org
Discussion: https://postgr.es/m/18765-6c26d2047e6f5143@postgresql.org
---
src/backend/utils/adt/like_match.c | 22 ++++++++++++++++++++++
1 file changed, 22 insertions(+)
diff --git a/src/backend/utils/adt/like_match.c b/src/backend/utils/adt/like_match.c
index defcaa96fb5..23bb6daeb9e 100644
--- a/src/backend/utils/adt/like_match.c
+++ b/src/backend/utils/adt/like_match.c
@@ -89,6 +89,28 @@ MatchText(const char *t, int tlen, const char *p, int plen, pg_locale_t locale)
if (plen == 1 && *p == '%')
return LIKE_TRUE;
+ /*
+ * Reject a pattern that ends with an escape character. The loop below
+ * only notices that if matching gets that far, so without this check the
+ * error would depend on the text. A pattern ends with an escape iff it
+ * ends with an odd number of backslashes, since each pair is an escaped
+ * backslash. That holds in multibyte encodings too, since a backslash
+ * byte can't be part of a multibyte character (see below). Usually the
+ * last byte is not a backslash, so this is cheap enough to repeat in
+ * recursive calls.
+ */
+ if (plen > 0 && p[plen - 1] == '\\')
+ {
+ int nbackslashes = 1;
+
+ while (nbackslashes < plen && p[plen - 1 - nbackslashes] == '\\')
+ nbackslashes++;
+ if (nbackslashes % 2 != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ESCAPE_SEQUENCE),
+ errmsg("LIKE pattern must not end with escape character")));
+ }
+
/* Since this function recurses, it could be driven to stack overflow */
check_stack_depth();
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v1-0002-Add-tests-for-LIKE-patterns-that-end-with-an-esca.patch (2.8K, ../../CAGRkXqS3zxLWfmTF3q4oe7hunOqAAjjFMa_PB2Dvzjo7P3a5Hw@mail.gmail.com/4-v1-0002-Add-tests-for-LIKE-patterns-that-end-with-an-esca.patch)
download | inline diff:
From 04c5afcded0308208a57bce0965f2b302ba73d84 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 25 Sep 2026 00:55:47 -0400
Subject: [PATCH v1 2/2] Add tests for LIKE patterns that end with an escape
There were no tests for this error outside the nondeterministic
collation case. Cover inputs where matching never reaches the end of
the pattern, plus a pattern ending in an escaped backslash, which must
still work.
Discussion: https://postgr.es/m/19699-dbaa58bbf8db1859@postgresql.org
---
src/test/regress/expected/strings.out | 27 +++++++++++++++++++++++++++
src/test/regress/sql/strings.sql | 12 ++++++++++++
2 files changed, 39 insertions(+)
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index fa29abfd829..ef997409bed 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1832,6 +1832,33 @@ SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true";
t
(1 row)
+-- escape at end of pattern is an error, even if matching doesn't reach it
+SELECT '' LIKE '\' AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT 'x' LIKE 'x\' AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT 'x' NOT LIKE 'y\' AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT '' LIKE '#' ESCAPE '#' AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT '' ILIKE '\' AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT ''::bytea LIKE '\\'::bytea AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT 'x\' LIKE 'x\\\' AS "error";
+ERROR: LIKE pattern must not end with escape character
+SELECT 'x\' LIKE 'x\\' AS "true";
+ true
+------
+ t
+(1 row)
+
+SELECT 'x\' NOT LIKE 'x\\' AS "false";
+ false
+-------
+ f
+(1 row)
+
--
-- test ILIKE (case-insensitive LIKE)
-- Be sure to form every test as an ILIKE/NOT ILIKE pair.
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 7d9c7275a02..286c7bccab7 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -500,6 +500,18 @@ SELECT 'be_r' NOT LIKE 'b_e__r' ESCAPE '_' AS "false";
SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false";
SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true";
+-- escape at end of pattern is an error, even if matching doesn't reach it
+SELECT '' LIKE '\' AS "error";
+SELECT 'x' LIKE 'x\' AS "error";
+SELECT 'x' NOT LIKE 'y\' AS "error";
+SELECT '' LIKE '#' ESCAPE '#' AS "error";
+SELECT '' ILIKE '\' AS "error";
+SELECT ''::bytea LIKE '\\'::bytea AS "error";
+SELECT 'x\' LIKE 'x\\\' AS "error";
+
+SELECT 'x\' LIKE 'x\\' AS "true";
+SELECT 'x\' NOT LIKE 'x\\' AS "false";
+
--
-- test ILIKE (case-insensitive LIKE)
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
2026-09-18 07:31 BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input PG Bug reporting form <noreply@postgresql.org>
2026-09-25 04:59 ` Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input shihao zhong <zhong950419@gmail.com>
@ 2026-09-25 05:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-25 13:35 ` Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2026-09-25 05:05 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
shihao zhong <zhong950419@gmail.com> writes:
+ * ... this is cheap enough to repeat in
+ * recursive calls.
This claim is unsubstantiated. Even if it were, I fail to understand
why it's necessary to expend any cycles whatsoever on this point.
Who cares if we don't throw that error?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
2026-09-18 07:31 BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input PG Bug reporting form <noreply@postgresql.org>
2026-09-25 04:59 ` Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input shihao zhong <zhong950419@gmail.com>
2026-09-25 05:05 ` Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-09-25 13:35 ` shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: shihao zhong @ 2026-09-25 13:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
Hi Tom,
> This claim is unsubstantiated. Even if it were, I fail to understand
> why it's necessary to expend any cycles whatsoever on this point.
> Who cares if we don't throw that error?
You're right that it isn't cheap as written. After a %, each candidate
position in the text gets its own recursive call, and each call repeats
the check. Checking once before the recursion would fix that.
But I agree with your main point. Even a cheap check buys nothing. A
bad pattern just returns false, and both reports were only about the
inconsistency. So I'm dropping the patch.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-09-25 13:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 07:31 BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input PG Bug reporting form <noreply@postgresql.org>
2026-09-25 04:59 ` shihao zhong <zhong950419@gmail.com>
2026-09-25 05:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-25 13:35 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox