pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
9+ messages / 4 participants
[nested] [flat]

* BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-08-03 06:58  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 9+ messages in thread

From: PG Bug reporting form @ 2026-08-03 06:58 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 1217816127@qq.com

The following bug has been logged on the website:

Bug reference:      19602
Logged by:          Yuelin Wang
Email address:      1217816127@qq.com
PostgreSQL version: 19beta2
Operating system:   Linux (Ubuntu 24.04, x86_64)
Description:        

## Vuln46: citext split_part silently returns NULL for a zero field position
instead of raising core split_part's error

### Summary

citext.split_part(citext, citext, int) is implemented in SQL as an array
subscript expression (regexp_split_to_array(...))[$3] rather than by calling
pg_catalog.split_part. Postgres array subscripting silently returns NULL for
an out of range index such as 0, so citext's split_part diverges from core
split_part, which explicitly raises "field position must not be zero" for a
zero field argument.

CWE: CWE-1284. Severity: Low.

### PoC

```sql
CREATE EXTENSION citext;
SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 0) IS NULL AS is_null_0;
SELECT split_part('abc~@~def~@~ghi'::citext, '~@~', 0);
SELECT pg_catalog.split_part('abc~@~def~@~ghi', '~@~', 0);
```

### Result

Real captured output from the independent verification run:

```
CREATE EXTENSION
 is_null_0
-----------
 t
(1 row)

 split_part
------------
 
(1 row)

ERROR:  field position must not be zero
```

### Impact

An application that relies on split_part raising an error for a zero field
position to catch a programming or input validation bug will instead
silently receive NULL when operating on citext values, potentially masking
the underlying logic error rather than failing loudly.








^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-09-23 19:40  Manu <manuelreyesbravo@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 9+ messages in thread

From: Manu @ 2026-09-23 19:40 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 1217816127@qq.com

Hi,

This reproduces on master, and zero is only one of the positions that
differ.  citext's split_part() splits with a case-insensitive regular
expression and subscripts the resulting array, so it was left behind
when ec0294fb2c8 (v14) taught the core split_part() negative positions.
On master:

- split_part('a,b,c'::citext, ',', -1): NULL, core returns 'c'
- split_part('a,b,c'::citext, ',', 4): NULL, core returns ''
- split_part('a,b,c'::citext, ',', 0): NULL, core raises an error
- split_part('abc'::citext, '', 1): 'a', core returns 'abc'

The last one is the empty delimiter: the regular expression splits
between every character, while the core function treats the whole
string as the only field.

The attached patch keeps the case-insensitive split and applies the
core rules to the result: a negative position counts from the end, a
position past the last field gives an empty string.  A zero position
and an empty delimiter, which have no case to ignore, are passed to
the core function, so the error message is the same.  Since the
function is part of the extension script, this needs citext 1.9.

Tested on master:

- the citext regression tests pass, with new cases for all of the above;
- ALTER EXTENSION citext UPDATE TO '1.9' changes the -1 case from NULL
  to 'c' in an existing database;
- on 20000 random lower-case strings, delimiters of up to three
  characters (some of them regex metacharacters) and positions from -6
  to 6, the result matches the core split_part() in every case.

Regards,
Manu

Attachments:

0001-Make-citext-s-split_part-handle-field-positions-like.patch (text/x-patch, 7.3K) download From 3af9c0ddaa8c82879528e468fac11e0b8eaf8b4c Mon Sep 17 00:00:00 2001 From: Manu <manuelreyesbravo@gmail.com> Date: Wed, 23 Sep 2026 16:30:11 -0300 Subject: [PATCH] Make citext's split_part() handle field positions like the core one ec0294fb2c8 taught split_part() to count negative positions from the end and to reject a position of zero. citext's version, which splits with a case-insensitive regular expression and subscripts the result, was not updated, so it returned NULL for negative positions, for a position of zero and for a position past the last field, where the core function returns the field, an error and an empty string. With an empty delimiter it also split the string into single characters, where the core function treats the whole string as the only field. Handle the positions the same way, and leave a zero position and an empty delimiter, which have no case to ignore, to the core function. This needs a new extension version, 1.9. 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 | 20 ++++++++++++++ contrib/citext/citext.control | 2 +- contrib/citext/expected/citext.out | 40 ++++++++++++++++++++++++++++ contrib/citext/expected/citext_1.out | 40 ++++++++++++++++++++++++++++ contrib/citext/meson.build | 1 + contrib/citext/sql/citext.sql | 8 ++++++ 7 files changed, 111 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 fc990607bf2..f4db865ced5 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 00000000000..6573f55b5f9 --- /dev/null +++ b/contrib/citext/citext--1.8--1.9.sql @@ -0,0 +1,20 @@ +/* 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 + +-- Follow the core split_part(): a negative position counts from the end, a +-- position past the last field returns an empty string, and a position of +-- zero is an error. A zero position and an empty delimiter, where there is no +-- case to ignore, are left to the core function. +CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT +LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE +RETURN CASE + WHEN $3 = 0 OR pg_catalog.length($2::pg_catalog.text) = 0 THEN + pg_catalog.split_part($1::pg_catalog.text, $2::pg_catalog.text, $3) + ELSE + (SELECT COALESCE(fields[CASE WHEN $3 > 0 THEN $3 + ELSE pg_catalog.array_length(fields, 1) + $3 + 1 END], + '') + FROM (SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i') AS fields) AS s) +END; diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control index 2b0f3fa8407..a76f6bb2292 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 8c0bf54f0f3..1e8e7c2a5b1 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -2120,6 +2120,46 @@ 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) + +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 c5e5f180f2b..e6c64d60a67 100644 --- a/contrib/citext/expected/citext_1.out +++ b/contrib/citext/expected/citext_1.out @@ -2120,6 +2120,46 @@ 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) + +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/meson.build b/contrib/citext/meson.build index 1cc49fc999f..d8aba86ca44 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 aa1cf9abd5c..ccbcae8d6bd 100644 --- a/contrib/citext/sql/citext.sql +++ b/contrib/citext/sql/citext.sql @@ -653,6 +653,14 @@ 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; +SELECT split_part('abcTdefTghi'::citext, 't'::citext, 0); SELECT strpos('high'::citext, 'gh' ) = 3 AS t; SELECT strpos('high', 'gh'::citext) = 3 AS t; -- 2.55.0
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-10 13:42  Aleksander Alekseev <aleksander@tigerdata.com>
  parent: Manu <manuelreyesbravo@gmail.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Aleksander Alekseev @ 2026-11-10 13:42 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: Manu <manuelreyesbravo@gmail.com>; 1217816127@qq.com

Hi Manu,

> Tested on master:
>
> - the citext regression tests pass, with new cases for all of the above;
> - ALTER EXTENSION citext UPDATE TO '1.9' changes the -1 case from NULL
>   to 'c' in an existing database;
> - on 20000 random lower-case strings, delimiters of up to three
>   characters (some of them regex metacharacters) and positions from -6
>   to 6, the result matches the core split_part() in every case.

I think v1 can be simplified like this:

```
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);
```

Without WHEN .. ELSE and COALESCE it should be faster too.

Thoughts?

-- 
Best regards,
Aleksander Alekseev

Attachments:

v1-changes.txt (text/plain, 2.1K) download diff --git a/contrib/citext/citext--1.8--1.9.sql b/contrib/citext/citext--1.8--1.9.sql index 6573f55b5f9..aa02961618f 100644 --- a/contrib/citext/citext--1.8--1.9.sql +++ b/contrib/citext/citext--1.8--1.9.sql @@ -3,18 +3,20 @@ -- 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 --- Follow the core split_part(): a negative position counts from the end, a --- position past the last field returns an empty string, and a position of --- zero is an error. A zero position and an empty delimiter, where there is no --- case to ignore, are left to the core function. +-- 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 CASE - WHEN $3 = 0 OR pg_catalog.length($2::pg_catalog.text) = 0 THEN - pg_catalog.split_part($1::pg_catalog.text, $2::pg_catalog.text, $3) - ELSE - (SELECT COALESCE(fields[CASE WHEN $3 > 0 THEN $3 - ELSE pg_catalog.array_length(fields, 1) + $3 + 1 END], - '') - FROM (SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i') AS fields) AS s) -END; +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);
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-11 01:22  JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
  parent: Manu <manuelreyesbravo@gmail.com>
  1 sibling, 1 reply; 9+ messages in thread

From: JaeHyuck Sa @ 2026-11-11 01:22 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; 1217816127@qq.com

Hi Manu,

A few comments:

> - ALTER EXTENSION citext UPDATE TO '1.9' changes the -1 case from NULL
> to 'c' in an existing database;

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. With the patch on master
(d0a4f66217c), an index scan on split_part(addr, '@', 2) and a seq
scan disagreed about 'nodomain' until I ran REINDEX. Maybe worth a
line in the commit message.

The new body also isn't inlined anymore. With 1.9, EXPLAIN VERBOSE
shows a plain function call, where 1.8 had
regexp_split_to_array(...)[2]. On a release build,
split_part(v, ',', 2) over 200000 rows went from about 80 ms to
250 ms. Is that acceptable?

> Since the function is part of the extension script, this needs citext 1.9.

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

Regards,
Jaehyuck Sa






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-11 05:09  Manu <manuelreyesbravo@gmail.com>
  parent: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Manu @ 2026-11-11 05:09 UTC (permalink / raw)
  To: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>; +Cc: Aleksander Alekseev <aleksander@tigerdata.com>; pgsql-bugs@lists.postgresql.org; 1217816127@qq.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
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-12 05:29  JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
  parent: Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: JaeHyuck Sa @ 2026-11-12 05:29 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; +Cc: Aleksander Alekseev <aleksander@tigerdata.com>; pgsql-bugs@lists.postgresql.org; 1217816127@qq.com

Hi Manu,

Like you, I compared v2 against an exact case-preserving reference on
30000 random mixed-case ASCII inputs (delimiters such as '.', '|', '*'
and 'aB.', positions from -6 to 6 except 0) and found no differences.

EXPLAIN VERBOSE shows that v2 is inlined again. The index scan / seq
scan mismatch after ALTER EXTENSION UPDATE is still there with v2, and
the new paragraph in the commit message covers it.

I have two minor comments:

> +SELECT split_part('aB&cb&d'::citext, 'b&'::citext, -1) = 'd' AS t;

It looks like this test passes even if 'gi' is changed to 'g' in the
function body, since the last field is 'd' either way. With -2, it
would return 'c' only when the match is case-insensitive, so I think
it would catch a missing 'i'. (The same goes for the 'aΔbδc' line in
citext_utf8.sql.)

> +-- (U+01C5) used as the delimiter: the regex engine does not match it
> +-- case-insensitively against itself, but the split is still found

This doesn't seem to hold everywhere. With the builtin provider
(PG_UNICODE_FAST), U+01C5 ~* U+01C5 is false, since allcases() adds
only the lower and upper case forms of a character, not the character
itself. With libc on macOS (en_US.UTF-8), though, it is true, because
towlower() maps U+01C5 to itself, so the 'aDžb' line passes with the
1.8 body as well. Since v2 returns 'b' either way, I think only the
wording needs to change (e.g. "does not match" -> "may not match" in
the comment, and "which happens for" -> "which can happen for" in the
commit message).

Jeff Davis described the same allcases() limitation before, using
'Dž' ~* 'dž' as an example:

https://postgr.es/m/03959b5f8b37b6126d0b9c6ac16c960a94fcd3bb.camel@j-davis.com

Other than that, v2 looks good to me.

Regards,
Jaehyuck Sa






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-12 23:00  Manu <manuelreyesbravo@gmail.com>
  parent: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Manu @ 2026-11-12 23:00 UTC (permalink / raw)
  To: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>; +Cc: Aleksander Alekseev <aleksander@tigerdata.com>; pgsql-bugs@lists.postgresql.org; 1217816127@qq.com

Hi Jaehyuck,

You were right on both points; v3, attached, has both changes.

On Thu, Nov 12, 2026 at 2:29 PM Jaehyuck Sa
<jaehyuck.sa.dev@gmail.com> wrote:
> With -2, it
> would return 'c' only when the match is case-insensitive, so I think
> it would catch a missing 'i'. (The same goes for the 'aΔbδc' line in
> citext_utf8.sql.)

Both lines now use -2. With 'gi' changed to 'g', the two -1 lines
passed and the two -2 lines fail; the tests still catch an unescaped
pattern or replacement string as before.

> I think only the
> wording needs to change

Done as you suggested. Of the eight locales I tried on Linux,
U+01C5 ~* U+01C5 is true only with the plain C locale (libc and
builtin); it is false with builtin C.UTF-8 and PG_UNICODE_FAST,
libc en_US.UTF-8 and C.UTF-8, and ICU. I could not try macOS.

Regards,
Manu

Attachments:

v3-0001-Make-citext-s-split_part-handle-field-positions-l.patch (text/x-patch, 12.0K) download From db0b4a03f429d3c3e484023605d1c270146fef90 Mon Sep 17 00:00:00 2001 From: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Date: Thu, 12 Nov 2026 19:44:45 -0300 Subject: [PATCH v3] 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 can happen 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..e99c3e1 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, -2) = 'c' 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..66aff57 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, -2) = 'c' 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..df35322 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 may not match it +-- case-insensitively against itself, but the split is still found +SELECT split_part('aΔbδc'::citext, 'δ'::citext, -2) = 'b' 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..abc81f6 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, -2) = 'c' 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..d923d6d 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 may not match it +-- case-insensitively against itself, but the split is still found +SELECT split_part('aΔbδc'::citext, 'δ'::citext, -2) = 'b' AS t; +SELECT split_part('aDžb'::citext, 'Dž'::citext, 2) = 'b' AS t; -- 2.56.1
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-13 06:10  JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
  parent: Manu <manuelreyesbravo@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: JaeHyuck Sa @ 2026-11-13 06:10 UTC (permalink / raw)
  To: Manu <manuelreyesbravo@gmail.com>; +Cc: Aleksander Alekseev <aleksander@tigerdata.com>; pgsql-bugs@lists.postgresql.org; 1217816127@qq.com

Hi Manu,

Thanks for v3.

In my last mail I said the new paragraph in the commit message covers
the index scan / seq scan mismatch, but I missed a case. In a session
that had used the table under 1.8, I ran ALTER EXTENSION citext UPDATE
and then REINDEX. The index was rebuilt with the 1.8 results, so a
new session's index scan still missed rows that a seq scan found. A
REINDEX from a new session fixed it. This seems to be general
PostgreSQL behavior rather than anything in v3, so perhaps you could
add a few words to that paragraph, e.g. "need a REINDEX from a session
started after the update".

Regards,
Jaehyuck Sa






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp
@ 2026-11-13 19:54  Manu <manuelreyesbravo@gmail.com>
  parent: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Manu @ 2026-11-13 19:54 UTC (permalink / raw)
  To: JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>; +Cc: Aleksander Alekseev <aleksander@tigerdata.com>; pgsql-bugs@lists.postgresql.org; 1217816127@qq.com

Hi Jaehyuck,

On Fri, Nov 13, 2026 at 3:10 PM Jaehyuck Sa
<jaehyuck.sa.dev@gmail.com> wrote:
> In a session
> that had used the table under 1.8, I ran ALTER EXTENSION citext UPDATE
> and then REINDEX. The index was rebuilt with the 1.8 results, so a
> new session's index scan still missed rows that a seq scan found.

You are right: after a REINDEX in such a session, an index scan from a
new session found 0 of 500 matching rows and a seq scan found 500, and
after a REINDEX from a new session both found 500. A plain SQL function
changed with CREATE OR REPLACE does the same, so it is not specific to
citext. The same happens with a partial index predicate, a partition
key (an older session that had used the table kept routing new rows by
the 1.8 result) and a domain CHECK (an older session accepted a value
1.9 rejects). The attached v4 adds this to the commit message; the code
is unchanged.

Regards,
Manu

Attachments:

v4-0001-Make-citext-s-split_part-handle-field-positions-l.patch (text/x-patch, 12.3K) download From fb3a27f56651e1e5c28da26f3437b99bb808f391 Mon Sep 17 00:00:00 2001 From: Manuel Reyes Bravo <manuelreyesbravo@gmail.com> Date: Fri, 13 Nov 2026 13:56:22 -0300 Subject: [PATCH v4] 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 can happen 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: indexes using it in an expression or a predicate need a REINDEX, and rows routed by a partition key, stored generated columns and materialized views using it need to be rewritten or refreshed. A session that used such an index, partition key or domain CHECK constraint before the update may keep the 1.8 body it inlined until it ends, so the REINDEX and the rewrite of partitioned tables need a session started after the update. 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..e99c3e1 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, -2) = 'c' 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..66aff57 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, -2) = 'c' 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..df35322 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 may not match it +-- case-insensitively against itself, but the split is still found +SELECT split_part('aΔbδc'::citext, 'δ'::citext, -2) = 'b' 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..abc81f6 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, -2) = 'c' 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..d923d6d 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 may not match it +-- case-insensitively against itself, but the split is still found +SELECT split_part('aΔbδc'::citext, 'δ'::citext, -2) = 'b' AS t; +SELECT split_part('aDžb'::citext, 'Dž'::citext, 2) = 'b' AS t; -- 2.56.1
^ permalink raw reply [nested|flat] 9+ messages in thread


end of thread, other threads:[~2026-11-13 19:54 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-03 06:58 BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:40 ` Manu <manuelreyesbravo@gmail.com>
2026-11-10 13:42   ` Aleksander Alekseev <aleksander@tigerdata.com>
2026-11-11 01:22   ` JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
2026-11-11 05:09     ` Manu <manuelreyesbravo@gmail.com>
2026-11-12 05:29       ` JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
2026-11-12 23:00         ` Manu <manuelreyesbravo@gmail.com>
2026-11-13 06:10           ` JaeHyuck Sa <jaehyuck.sa.dev@gmail.com>
2026-11-13 19:54             ` Manu <manuelreyesbravo@gmail.com>

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