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
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
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
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
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
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