BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
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.
Hi,
Same issue as bug #18765 [1]/messages/by-id/18765-6c26d2047e6f5143@postgresql.org Thanks, Shihao. 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]: /messages/by-id/18765-6c26d2047e6f5143@postgresql.org Thanks, Shihao
Thanks,
Shihao
Attachments:
t253837_2v1-0001-Reject-LIKE-patterns-that-end-with-an-escape-what.patchapplication/octet-stream; name=v1-0001-Reject-LIKE-patterns-that-end-with-an-escape-what.patchDownload+22-1
v1-0002-Add-tests-for-LIKE-patterns-that-end-with-an-esca.patchapplication/octet-stream; name=v1-0002-Add-tests-for-LIKE-patterns-that-end-with-an-esca.patchDownload+39-1
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
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