BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input

Started by PG Bug reporting form2 months ago4 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2shihao zhong
zhong950419@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input

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_2
v1-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
#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: shihao zhong (#2)
Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
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

#4shihao zhong
zhong950419@gmail.com
In reply to: Tom Lane (#3)
Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input

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