Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

Поиск
Список
Период
Сортировка
Искать

Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
Manu <manuelreyesbravo@gmail.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

Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
Aleksander Alekseev <aleksander@tigerdata.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

Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
JaeHyuck Sa <jaehyuck.sa.dev@gmail.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


Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Вложения:
Hi Aleksander and Jaehyuck,

On Tue, Nov 10, 2026 at 4:42 PM Aleksander Alekseev
 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
 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

Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
JaeHyuck Sa <jaehyuck.sa.dev@gmail.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


Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
Manu <manuelreyesbravo@gmail.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
 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

Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
JaeHyuck Sa <jaehyuck.sa.dev@gmail.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


Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp

От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Вложения:
Hi Jaehyuck,

On Fri, Nov 13, 2026 at 3:10 PM Jaehyuck Sa
 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
FAQ