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.
