agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: imchifan@163.com
Subject: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
Date: Fri, 18 Sep 2026 07:31:04 +0000
Message-ID: <19699-dbaa58bbf8db1859@postgresql.org> (raw)

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.

Message-ID: <19699-dbaa58bbf8db1859@postgresql.org>
Permalink:  ../19699-dbaa58bbf8db1859@postgresql.org/
Also on:    postgresql.org/message-id/19699-dbaa58bbf8db1859@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, imchifan@163.com
  Subject: Re: BUG #19699: LIKE with a trailing escape fails to raise SQLSTATE 22025 for empty input
  In-Reply-To: <19699-dbaa58bbf8db1859@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox