agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation
3+ messages / 3 participants
[nested] [flat]
* BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation
@ 2026-08-28 04:10 PG Bug reporting form <noreply@postgresql.org>
2026-08-28 19:38 ` Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-08-28 04:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: syzhong16@gmail.com
The following bug has been logged on the website:
Bug reference: 19641
Logged by: Suyang Zhong
Email address: syzhong16@gmail.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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation
2026-08-28 04:10 BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-28 19:38 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-23 19:50 ` Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andrey Rachitskiy @ 2026-08-28 19:38 UTC (permalink / raw)
To: syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
пт, 28 авг. 2026 г. в 14:06, PG Bug reporting form <noreply@postgresql.org>:
> The following bug has been logged on the website:
>
> Bug reference: 19641
> Logged by: Suyang Zhong
> Email address: syzhong16@gmail.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...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation
2026-08-28 04:10 BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-28 19:38 ` Re: BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-23 19:50 ` Manu <manuelreyesbravo@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Manu @ 2026-09-23 19:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; syzhong16@gmail.com
Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
> 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?
Some data for that choice, all on master (374522aa63a).
First, the scope. With the 340-row table from the report, the column
under the nondeterministic collation, and the same equality query, each
index type against a sequential scan (227 rows):
- SP-GiST text_ops: 113
- btree, hash, BRIN, GiST (btree_gist): 227
Every plan used its index, so SP-GiST's text_ops is the only one of
them that gets this wrong. It also does so at any size, one row
included.
Second, what A does to indexes that already exist. The precedent,
281039631, went in before v12's rc1, when no such index could exist
yet, while an SP-GiST index under a nondeterministic collation has been
accepted since v12. So I built a prototype of A with the same check as
the pattern_ops one in index.c, for SP-GiST text_ops, created such an
index on an unpatched master cluster, and took it to the prototype:
- a pg_dump restored with psql loads the table (340 rows) and skips
the index with one ERROR; psql exits with 0, so a restore script
that does not stop on errors ends up without the index and says
nothing;
- pg_upgrade fails, during the schema restore, with the same error.
So A cannot be back-patched, and in master it would need a pg_upgrade
check that reports these indexes before the upgrade, as pg_upgrade
does for other objects it cannot carry over.
B has neither problem. If I read your description right, it changes
only how the scan uses the tree, not how the tree is built, so an
existing index returns correct results after a minor update, without a
REINDEX. That seems to me the one that can go to all the branches.
Your point that B does not make the index a good accelerator for this
equality stands; that seems like something for the documentation to
say (a btree index serves it better), rather than a reason to break
existing schemas.
If you post the B draft, I am happy to test it on the back branches:
the existing-index case above, and the other text_ops operators under
the same collation.
Regards,
Manu
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-23 19:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-28 04:10 BUG #19641: Unexpected results on an SP-GiST indexed column with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-28 19:38 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-23 19:50 ` Manu <manuelreyesbravo@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox