Skip to content

Partial-prefix lookup on a composite index returns rows of OTHER keys when the first key component contains some accented characters #5321

Closed

Description

@adepase

ArcadeDB version: 26.7.2 (official docker image), single server, remote HTTP API.
Reproduced with index compaction DISABLED (-Darcadedb.indexCompactionMinPagesSchedule=0) on a freshly created index, so it does not look related to the compacted-page reading issues fixed after the 26.7.2 tag.
Also reproduced UNCHANGED on 26.7.3 (official docker image), both on a fresh database with default settings and on a server upgraded in place from 26.7.2: same result sets as below, 2026-07-18.

Summary

Given a NOTUNIQUE composite index on three STRING properties (w, l, t):

  • equality on the FULL key (w = ? and l = ? and t = ?) is always correct;
  • equality on a PARTIAL prefix (w = ? and l = ?) returns rows belonging to other keys when w contains certain non-ASCII characters (Italian accented vowels). With w = 'è' the scan returns the whole table.

Not every accented value misbehaves: in the reproduction below 'cioè' and 'è' break while 'così' and 'perché' are fine - it looks like the upper bound of the prefix range scan is computed incorrectly for some multi-byte final characters, widening the range to a shorter prefix (with 'cioè' the result set is exactly the 'cio*' words; with 'è' it degenerates to a full
range).

Reproduction

CREATE DOCUMENT TYPE ReproN7;
CREATE PROPERTY ReproN7.w STRING;
CREATE PROPERTY ReproN7.l STRING;
CREATE PROPERTY ReproN7.t STRING;
CREATE INDEX ON ReproN7 (w, l, t) NOTUNIQUE;

-- 10 words x 2 types
INSERT INTO ReproN7 SET w = 'cioccolato', l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'cioccolato', l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'cinema',     l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'cinema',     l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'cioè',       l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'cioè',       l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'ciondolo',   l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'ciondolo',   l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'citare',     l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'citare',     l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'così',       l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'così',       l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'è',          l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'è',          l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'perché',     l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'perché',     l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'casa',       l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'casa',       l = 'it', t = 'B';
INSERT INTO ReproN7 SET w = 'cane',       l = 'it', t = 'A'; INSERT INTO ReproN7 SET w = 'cane',       l = 'it', t = 'B';

Full-key equality - correct for every word, accented included:

SELECT w FROM ReproN7 WHERE w = 'cioè' AND l = 'it' AND t = 'A';
-- [cioè]  (1 row, correct)

Partial-prefix equality - expected 2 rows of the same w:

SELECT w FROM ReproN7 WHERE w = 'casa' AND l = 'it';
-- [casa, casa]                                    2 rows, CORRECT
SELECT w FROM ReproN7 WHERE w = 'così' AND l = 'it';
-- [così, così]                                    2 rows, CORRECT
SELECT w FROM ReproN7 WHERE w = 'perché' AND l = 'it';
-- [perché, perché]                                2 rows, CORRECT
SELECT w FROM ReproN7 WHERE w = 'cioè' AND l = 'it';
-- [cioccolato, cioccolato, cioè, cioè, ciondolo, ciondolo]   6 rows, WRONG
SELECT w FROM ReproN7 WHERE w = 'è' AND l = 'it';
-- ALL 20 rows of the table                                    WRONG

The same statements with positional parameters behave identically.

Impact on our dataset

On our real 434k-row type (an Italian morphological dictionary, mixed with the same composite indexes) the partial-prefix form returned thousands of foreign rows for accented keys (w = 'è' and l = 'it' → 8289 rows of unrelated words), which our application consumed as garbage candidates.
Workaround applied on our side: bind only the single-property exact key (healthy) and filter the second component in the caller.

Environment

  • arcadedata/arcadedb:26.7.2, JVM options -Darcadedb.server.rootPassword=*** -Darcadedb.indexCompactionMinPagesSchedule=0 -Xms1G -Xmx2G

  • Database created by the OrientDB importer + explicit
    CREATE INDEX ... NOTUNIQUE (LSM); indexes freshly recreated with DROP + CREATE right before the test.

  • Client: HTTP /api/v1/command, UTF-8 JSON payloads (the full-key results prove the parameters reach the server correctly encoded).

Activity

  1. self-assigned this
    on Jul 18, 2026
  2. added theissue type on Jul 18, 2026
  3. added this to the 26.8.1 milestone on Jul 18, 2026
  4. lvca commented on Jul 18, 2026

    @lvca
    Member

    Checking it...

  5. lvca commented on Jul 18, 2026

    @lvca
    Member

    Confirmed and fixed on main.

    Root cause: for string keys, the LSM index positioned entries on disk using a signed byte comparison, while the code that decides where a range/prefix scan should stop used an unsigned one. For plain ASCII the two agree, so nothing showed up. But UTF-8 accented characters (è, ì, ...) use bytes ≥ 0x80, which are negative when read as signed - so the two comparisons disagreed on where those keys sort. The scan therefore stopped at the wrong boundary and returned rows belonging to unrelated keys (with è the range widened to the whole type).

    Fix: the on-disk ordering now uses the same unsigned/lexicographic order as the rest of the engine, so full-key, partial-prefix and range lookups all agree - accented keys included. A regression test covering your exact scenario is included.

    One note for existing databases: any index already containing accented string keys was written in the old order. After upgrading, please rebuild those indexes (drop + recreate, as you already do) so they are stored in the corrected order. Indexes with ASCII-only keys are unaffected.

    This will ship in the next release.


    ❤️ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.

  6. added 2 commits that reference this issue on Jul 21, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions