Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation

пт, 28 авг. 2026 г. в 14:06, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19641
> Logged by: Suyang Zhong
> Email address: syzhong16(at)gmail(dot)com
> PostgreSQL version: 19beta3
> Operating system: Ubuntu 22.04
> Description:
>
> Hi,
>
> Consider the following test case:
>
> CREATE COLLATION nd (provider = icu, locale = 'und-u-ks-level2',
> deterministic = false);
>
> CREATE TABLE t0(c1 text);
> INSERT INTO t0 VALUES ('ALPHA');
> CREATE INDEX ON t0 USING spgist (c1 COLLATE nd);
>
> SELECT c1, c1 COLLATE nd = 'alpha' AS p FROM t0;
> -- ALPHA | t
>
> SET enable_seqscan = off;
> SELECT count(*) FROM t0 WHERE c1 COLLATE nd = 'alpha';
> -- Expected: 1, Actual: 0
>
> RESET enable_seqscan;
> SELECT count(*) FROM t0 WHERE c1 COLLATE nd = 'alpha';
> -- 1
>
> The predicate evaluates to true for the row, so filtering on the same
> predicate should return it.
>
> The original test case, where the planner chooses the index by itself:
>
> CREATE TABLE t1(c1 text);
> INSERT INTO t1
> SELECT CASE WHEN i % 3 = 0 THEN 'alpha'
> WHEN i % 3 = 1 THEN 'ALPHA'
> ELSE 'beta' END
> FROM generate_series(1, 340) AS i;
> CREATE INDEX ON t1 USING spgist (c1 COLLATE nd);
>
> SELECT count(*) FROM t1 WHERE c1 COLLATE nd = 'alpha';
> -- Expected: 227, Actual: 113
>
> With the v2 patch from #19633 applied, this case is still unchanged.
> Reproduced on 20devel, 19beta3 and 18.6.
>
>
>
>
Hi Suyang!

Thanks for the report.

It is separate from BUG #19633.
The semijoin unique-ification patch does not change it.
The wrong answer comes from the opclass.

What goes wrong
---------------
SP-GiST text_ops builds a byte radix tree. Insert
partitions by the next byte, not by collation. The equality (=)
support functions then compare with memcmp.

That matches texteq for deterministic collations, where texteq is
bitwise. For a nondeterministic collation, texteq uses
collation-aware comparison. So 'ALPHA' and 'alpha' are equal under
the query, but the index still prunes by bytes. Searching for
'alpha' drops the branch that holds 'ALPHA' (node label 'A').

On a table with 340 rows (about one third each of 'alpha', 'ALPHA',
and 'beta'), seqscan returns 227. An SP-GiST index scan returns 113
(only bitwise 'alpha').

Directions
----------
A. Refuse CREATE INDEX for SP-GiST text_ops with a nondeterministic
collation.

That stops new indexes that can silently lie. btree with a
nondeterministic collation already supports equality correctly.

B. Fix the equality scan for nondeterministic collations: skip memcmp
pruning in inner_consistent, and compare at the leaf with varstr_cmp.
I tried this. The same 340-row case then returns 227 from the index
scan, matching seqscan and btree.

That does not restore useful index pruning. The tree is still built
by bytes, so nd-equal strings stay in different branches. The scan
must visit much more of the tree. On that same case the B scan
returned 227 index rows and used more index buffers than a btree
index on the same column, which also returned 227. So B makes the
index honest, but not a good accelerator for nd equality. btree
already is.

Precedents
----------
text_pattern_ops hit the same class of problem: the opclass assumes
bitwise equality, but texteq is no longer bitwise under an nd
collation. The chosen fix was to refuse CREATE INDEX (commit
281039631), not to invent a special equality operator:

https://www.postgresql.org/message-id/22566.1568675619@sss.pgh.pa.us

In 2018, SP-GiST text_ops gave wrong results for non-C ordering
operators. Emre Hasegeli proposed removing those operators from the
opclass. Tom instead fixed leaf_consistent (full-string
varstr_cmp) in commit b15e8f71dbf. Inner already walked the whole
tree for non-C on the collation-aware ordering operators. That
thread was about deterministic non-C ordering, not nondeterministic
equality:

https://www.postgresql.org/message-id/CAE2gYzzb6K51VnTq5i5p52z+j9p2duEa-K1T3RrC_GQEynAKEg@mail.gmail.com

I have a draft of "A" and "B" ready, but I decided not to publish it until
an agreement on the direction is reached.

Thoughts?

--
Regards,
Rachitskiy Andrey