Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025
 for empty input
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: imchifan@163.com
Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 18 Sep 2026 07:31:04 +0000
Message-ID: <19699-dbaa58bbf8db1859@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19699-dbaa58bbf8db1859%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D 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 =3D 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 =3D 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 =3D 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(*) =3D 4 AS all_cases_ran,
       bool_and(coalesce(returned_sqlstate =3D '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.