pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Manu <manuelreyesbravo@gmail.com>
To: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
Cc: Aleksander Alekseev <aleksander@tigerdata.com>
Cc: pgsql-bugs@lists.postgresql.org
Cc: 1217816127@qq.com
Subject: Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
Date: Wed, 11 Nov 2026 02:09:59 -0300
Message-ID: <179437379918.1102417.1701789241865044164@gmail.com> (raw)
In-Reply-To: <CAKs5thwCELQ_0F7fkq7kZzp5d-c=-gCz0qYUO5v-6JCz5umY-g@mail.gmail.com>
References: <CAKs5thwCELQ_0F7fkq7kZzp5d-c=-gCz0qYUO5v-6JCz5umY-g@mail.gmail.com>

Hi Aleksander and Jaehyuck,

On Tue, Nov 10, 2026 at 4:42 PM Aleksander Alekseev
<aleksander@tigerdata.com> wrote:
> Without WHEN .. ELSE and COALESCE it should be faster too.

It is, and it is also more exact, so v2 uses your version; thanks. My
20000-case check for v1 used lower case only.  On 300000 random ASCII
cases with mixed case and regex metacharacters and 70 directed ones,
both bodies match an exact reference.  The only differences I found
are on U+01C5, U+01C8, U+01CB and U+01F2, which ~* does not match
against themselves; v1 misses them as delimiters, v2 does not.  v2 is
attached.

On Wed, Nov 11, 2026 at 10:22 AM Jaehyuck Sa
<jaehyuck.sa.dev@gmail.com> wrote:
> split_part(v, ',', 2) over 200000 rows went from about 80 ms to
> 250 ms. Is that acceptable?

v2 is inlined again. On 200000 rows like that, v1 takes 593 ms, 1.8
208 ms and v2 184 ms (release build, versions alternated, median of
30 runs); 1.8 is not a fair baseline, as it gets negative positions
wrong. At 5000 characters per row v1 and v2 differ by about 2%.

> split_part() is IMMUTABLE, so expression indexes and stored generated
> columns built under 1.8 keep the old results, e.g. NULL where the
> position is negative or past the last field.

It reproduces with both bodies (index scan 0 rows, seq scan 1, until
REINDEX), and so do partition keys. v2's commit message lists these,
and that a stored position of zero now makes REINDEX fail.

> Is this meant for master only? Branches before 18 have citext 1.6.

Yes, master only: 14 to 17 have the same bug in the old body, but the
1.8--1.9 script only fits where 1.8 is installed, 18 and master.

Regards,
Manu

Attachments:

v2-0001-Make-citext-s-split_part-handle-field-positions-l.patch (text/x-patch, 12.0K) download From b087ca4d2966cbbae3eb97c4ff5e24b8ba0c1c60 Mon Sep 17 00:00:00 2001 From: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Date: Wed, 11 Nov 2026 01:46:35 -0300 Subject: [PATCH v2] Make citext's split_part() handle field positions like the core one citext's split_part() splits with a case-insensitive regular expression and subscripts the resulting array, so it returns NULL for a position of zero, where the core function raises an error, and for a position past the last field, where the core function returns an empty string. Since ec0294fb2c8, the core function also counts negative positions from the end, while citext's returns NULL for them. With an empty delimiter it also splits the string into single characters, where the core function treats the whole string as the only field. Fix by replacing each case-insensitive occurrence of the delimiter with the delimiter itself and leaving the splitting to the core function, so that all the position rules are the core ones. The fields keep their original case. The new body is a single expression, so the function is still inlined, as it was in 1.8. This needs a new extension version, 1.9. Because the final split is exact, the delimiter is now also found where the regular expression engine fails to match it case-insensitively against itself, which happens for the titlecase letters U+01C5, U+01C8, U+01CB and U+01F2. After updating to 1.9, results of citext's split_part() stored under 1.8 are not recomputed: expression indexes using it need a REINDEX, and rows routed by a partition key, stored generated columns and materialized views using it need to be rewritten or refreshed. Stored rows that used a position of zero now raise an error when recomputed, and existing rows may no longer pass CHECK constraints that use it. Co-authored-by: Aleksander Alekseev <aleksander@tigerdata.com> Reported-by: Yuelin Wang <1217816127@qq.com> Discussion: https://postgr.es/m/19602-5ec4b4e30fa6f5f2@postgresql.org --- contrib/citext/Makefile | 1 + contrib/citext/citext--1.8--1.9.sql | 22 +++++++ contrib/citext/citext.control | 2 +- contrib/citext/expected/citext.out | 85 +++++++++++++++++++++++++ contrib/citext/expected/citext_1.out | 85 +++++++++++++++++++++++++ contrib/citext/expected/citext_utf8.out | 15 +++++ contrib/citext/meson.build | 1 + contrib/citext/sql/citext.sql | 18 ++++++ contrib/citext/sql/citext_utf8.sql | 6 ++ 9 files changed, 234 insertions(+), 1 deletion(-) create mode 100644 contrib/citext/citext--1.8--1.9.sql diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile index fc99060..f4db865 100644 --- a/contrib/citext/Makefile +++ b/contrib/citext/Makefile @@ -4,6 +4,7 @@ MODULES = citext EXTENSION = citext DATA = citext--1.4.sql \ + citext--1.8--1.9.sql \ citext--1.7--1.8.sql \ citext--1.6--1.7.sql \ citext--1.5--1.6.sql \ diff --git a/contrib/citext/citext--1.8--1.9.sql b/contrib/citext/citext--1.8--1.9.sql new file mode 100644 index 0000000..aa02961 --- /dev/null +++ b/contrib/citext/citext--1.8--1.9.sql @@ -0,0 +1,22 @@ +/* contrib/citext/citext--1.8--1.9.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION citext UPDATE TO '1.9'" to load this file. \quit + +-- Normalize the case of the delimiter occurrences in the string, and let the +-- core split_part() do the splitting. The fields themselves are not touched, +-- so they keep their original case, while the field positions, the empty +-- delimiter and the zero-position error all behave exactly as in core. +-- The delimiter is escaped twice: once as a regular expression to search for, +-- and once as a replacement string, where a backslash is also special. +CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT +LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE +RETURN pg_catalog.split_part( + pg_catalog.regexp_replace( + $1::pg_catalog.text, + pg_catalog.regexp_replace($2::pg_catalog.text, + '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), + pg_catalog.replace($2::pg_catalog.text, E'\\', E'\\\\'), + 'gi'), + $2::pg_catalog.text, + $3); diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control index 2b0f3fa..a76f6bb 100644 --- a/contrib/citext/citext.control +++ b/contrib/citext/citext.control @@ -1,6 +1,6 @@ # citext extension comment = 'data type for case-insensitive character strings' -default_version = '1.8' +default_version = '1.9' module_pathname = '$libdir/citext' relocatable = true trusted = true diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out index 8c0bf54..0c96b5e 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -2120,6 +2120,91 @@ SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t; t (1 row) +-- positions are handled as in the core split_part() +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -1) = 'ghi' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -3) = 'abc' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 4) = '' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -4) = '' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, ''::citext, 1) = 'abcTdefTghi' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, ''::citext, 2) = '' AS t; + t +--- + t +(1 row) + +-- the delimiter is matched regardless of case, even with characters that are +-- special in a regular expression or in its replacement string +SELECT split_part('a\B\c'::citext, '\'::citext, -1) = 'c' AS t; + t +--- + t +(1 row) + +SELECT split_part('a\1b\1c'::citext, '\1'::citext, 2) = 'b' AS t; + t +--- + t +(1 row) + +SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -1) = 'd' AS t; + t +--- + t +(1 row) + +SELECT split_part('a.*B.*c'::citext, '.*'::citext, -2) = 'B' AS t; + t +--- + t +(1 row) + +-- occurrences of the delimiter do not overlap, and fields keep their case +SELECT split_part('AaA'::citext, 'aa'::citext, -1) = 'A' AS t; + t +--- + t +(1 row) + +SELECT split_part('xaAb'::citext, 'AB'::citext, 1) = 'xa' AS t; + t +--- + t +(1 row) + +SELECT split_part('a,b,c'::citext, ','::citext, -2147483648) = '' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0); +ERROR: field position must not be zero +CONTEXT: SQL function "split_part" statement 1 SELECT strpos('high'::citext, 'gh' ) = 3 AS t; t --- diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out index c5e5f18..00300af 100644 --- a/contrib/citext/expected/citext_1.out +++ b/contrib/citext/expected/citext_1.out @@ -2120,6 +2120,91 @@ SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t; t (1 row) +-- positions are handled as in the core split_part() +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -1) = 'ghi' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -3) = 'abc' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 4) = '' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -4) = '' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, ''::citext, 1) = 'abcTdefTghi' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, ''::citext, 2) = '' AS t; + t +--- + t +(1 row) + +-- the delimiter is matched regardless of case, even with characters that are +-- special in a regular expression or in its replacement string +SELECT split_part('a\B\c'::citext, '\'::citext, -1) = 'c' AS t; + t +--- + t +(1 row) + +SELECT split_part('a\1b\1c'::citext, '\1'::citext, 2) = 'b' AS t; + t +--- + t +(1 row) + +SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -1) = 'd' AS t; + t +--- + t +(1 row) + +SELECT split_part('a.*B.*c'::citext, '.*'::citext, -2) = 'B' AS t; + t +--- + t +(1 row) + +-- occurrences of the delimiter do not overlap, and fields keep their case +SELECT split_part('AaA'::citext, 'aa'::citext, -1) = 'A' AS t; + t +--- + t +(1 row) + +SELECT split_part('xaAb'::citext, 'AB'::citext, 1) = 'xa' AS t; + t +--- + t +(1 row) + +SELECT split_part('a,b,c'::citext, ','::citext, -2147483648) = '' AS t; + t +--- + t +(1 row) + +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0); +ERROR: field position must not be zero +CONTEXT: SQL function "split_part" statement 1 SELECT strpos('high'::citext, 'gh' ) = 3 AS t; t --- diff --git a/contrib/citext/expected/citext_utf8.out b/contrib/citext/expected/citext_utf8.out index 19538db..eb2e0d0 100644 --- a/contrib/citext/expected/citext_utf8.out +++ b/contrib/citext/expected/citext_utf8.out @@ -154,3 +154,18 @@ SELECT 'à'::citext ~>=~ 'À'::citext AS t; t (1 row) +-- split_part() positions with a multibyte delimiter, and a titlecase letter +-- (U+01C5) used as the delimiter: the regex engine does not match it +-- case-insensitively against itself, but the split is still found +SELECT split_part('aΔbδc'::citext, 'δ'::citext, -1) = 'c' AS t; + t +--- + t +(1 row) + +SELECT split_part('aDžb'::citext, 'Dž'::citext, 2) = 'b' AS t; + t +--- + t +(1 row) + diff --git a/contrib/citext/meson.build b/contrib/citext/meson.build index 1cc49fc..d8aba86 100644 --- a/contrib/citext/meson.build +++ b/contrib/citext/meson.build @@ -27,6 +27,7 @@ install_data( 'citext--1.5--1.6.sql', 'citext--1.6--1.7.sql', 'citext--1.7--1.8.sql', + 'citext--1.8--1.9.sql', kwargs: contrib_data_args, ) diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql index aa1cf9a..53d92e6 100644 --- a/contrib/citext/sql/citext.sql +++ b/contrib/citext/sql/citext.sql @@ -653,6 +653,24 @@ SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 2) = 'def' AS t; SELECT split_part('abcTdefTghi'::citext, 't', 2) = 'def' AS t; SELECT split_part('abcTdefTghi'::citext, 't'::citext, 2) = 'def' AS t; SELECT split_part('abcTdefTghi', 't'::citext, 2) = 'def' AS t; +-- positions are handled as in the core split_part() +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -1) = 'ghi' AS t; +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -3) = 'abc' AS t; +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 4) = '' AS t; +SELECT split_part('abcTdefTghi'::citext, 't'::citext, -4) = '' AS t; +SELECT split_part('abcTdefTghi'::citext, ''::citext, 1) = 'abcTdefTghi' AS t; +SELECT split_part('abcTdefTghi'::citext, ''::citext, 2) = '' AS t; +-- the delimiter is matched regardless of case, even with characters that are +-- special in a regular expression or in its replacement string +SELECT split_part('a\B\c'::citext, '\'::citext, -1) = 'c' AS t; +SELECT split_part('a\1b\1c'::citext, '\1'::citext, 2) = 'b' AS t; +SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -1) = 'd' AS t; +SELECT split_part('a.*B.*c'::citext, '.*'::citext, -2) = 'B' AS t; +-- occurrences of the delimiter do not overlap, and fields keep their case +SELECT split_part('AaA'::citext, 'aa'::citext, -1) = 'A' AS t; +SELECT split_part('xaAb'::citext, 'AB'::citext, 1) = 'xa' AS t; +SELECT split_part('a,b,c'::citext, ','::citext, -2147483648) = '' AS t; +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0); SELECT strpos('high'::citext, 'gh' ) = 3 AS t; SELECT strpos('high', 'gh'::citext) = 3 AS t; diff --git a/contrib/citext/sql/citext_utf8.sql b/contrib/citext/sql/citext_utf8.sql index ba28332..ef1104e 100644 --- a/contrib/citext/sql/citext_utf8.sql +++ b/contrib/citext/sql/citext_utf8.sql @@ -59,3 +59,9 @@ SELECT 'à'::citext ~<=~ 'À'::citext AS t; -- Test ~>~ and ~>=~ SELECT 'à'::citext ~>~ 'À'::citext AS f; SELECT 'à'::citext ~>=~ 'À'::citext AS t; + +-- split_part() positions with a multibyte delimiter, and a titlecase letter +-- (U+01C5) used as the delimiter: the regex engine does not match it +-- case-insensitively against itself, but the split is still found +SELECT split_part('aΔbδc'::citext, 'δ'::citext, -1) = 'c' AS t; +SELECT split_part('aDžb'::citext, 'Dž'::citext, 2) = 'b' AS t; -- 2.56.0

view thread (9+ messages)  latest in thread

Message-ID: <179437379918.1102417.1701789241865044164@gmail.com>
Permalink:  ../179437379918.1102417.1701789241865044164@gmail.com/
Also on:    postgresql.org/message-id/179437379918.1102417.1701789241865044164@gmail.com

 ·  · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: manuelreyesbravo@gmail.com, jaehyuck.sa.dev@gmail.com, aleksander@tigerdata.com, 1217816127@qq.com
  Subject: Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
  In-Reply-To: <179437379918.1102417.1701789241865044164@gmail.com>
  References: <CAKs5thwCELQ_0F7fkq7kZzp5d-c=-gCz0qYUO5v-6JCz5umY-g@mail.gmail.com> <179437379918.1102417.1701789241865044164@gmail.com>

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

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