Skip to content

SQL: Index can return too many entries when used with ORDER BY #1760

New issue
New issue
Closed
Bug
Closed
SQL: Index can return too many entries when used with ORDER BY#1760
Bug
Assignees
lvcarobfrankcopilot-swe-agent
Milestone
25.9.1

Description

@gramian
gramian
opened on Oct 16, 2024

ArcadeDB Version:

ArcadeDB Server v24.11.1-SNAPSHOT (build 4207821d055e37cbd74b76683e45073da759c2de/1729083296142/console)

OS and JDK Version:

Running on Mac OS X 12.7.6 - OpenJDK 64-Bit Server VM 17.0.12 (Homebrew)

Querying a database can return the wrong number of records, in particular too many records. This problem is not easy to reproduce so I attach a small database (75 records) generated from public data. This behavior occurs when from certain databases, such as the one provided below, is queried via SQL, ie SELECT, where an (not-unique) indexed property is used in the projection and also as ordering quantity (in ORDER BY). The schema is given here: https://github.com/ulbmuenster/dataasee/blob/main/database/schema.sql

Expected behavior

Correct number of records returned.

Actual behavior

Too many records are returned.

Steps to reproduce

Restore this backup: metadatalake-backup-20240829-100753793.zip (337KB)

SELECT count(*) FROM metadata; -- There are 75 records in the database

SELECT name, publicationYear FROM metadata; -- Returns 75 records correctly

SELECT name FROM metadata ORDER BY publicationYear; -- Returns 75 records correctly

SELECT name, publicationYear FROM metadata ORDER BY publicationYear; -- Returns 117 records INCORRECTLY

Notes

End of August (2024-08-28 -- 2024-08-31) @lvca (and I) looked into this and potential sources of this behavior could be:

  • FetchFromIndexStep
  • The UPSERT entering the data.

Here is a sample UPSERT statement that generated the provided database:

UPDATE metadata MERGE {"creators":[{"@type":"pair","name":"Neumann, Georg"},{"@type":"pair","name":"Boivin, Odette"},{"@type":"pair","name":"Kleber, Kristin"},{"@type":"pair","name":"Neumann, Georg"},{"@type":"pair","name":"Boivin, Odette"},{"@type":"pair","name":"Kleber, Kristin"}],"dataLocation":"https://data-management.uni-muenster.de/direct-access/wwurdm/07988641231","dataSteward":"https://datastore.uni-muenster.de","description":"The dataset comprises the RTI-data of the cuneiform tablet VAT 17966 (N5:27).\\n\\nRTI = Reflectance Transformation Imaging (RTI) is a computational photographic method that captures a subject’s surface shape and color and enables the interactive re-lighting of the subject from any direction in a software viewer, revealing details not visible with the naked eye (cf. https://culturalheritageimaging.org/).","identifiers":[{"@type":"pair","data":"10.17879/07988641225","name":"DE-6"},{"@type":"pair","data":"10.17879/07988641225","name":"null"}],"keywords":"Babylon,Cuneiform,Neo-Babylonian","language":"#4:0","metadataQuality":"Incomplete","name":"VAT 17966 - RTI dataset","publicationYear":2024,"publisher":"Universität Münster","rawChecksum":"TcrJlnTQYfugD2rROs2Y3g==","rawType":"marc21","recordId":"MTA0MzQ4NDE4NDMwODAyNDg3NA","resourceType":null,"rights":"CC BY-NC-SA 4.0","source":"https://datastore.uni-muenster.de/oai","synonyms":[{"@type":"pair","data":"GoviB","name":"Alternative Title"}]} UPSERT WHERE recordId == 'MTA0MzQ4NDE4NDMwODAyNDg3NA';

As a workaround the projection can be renamed, ie:

SELECT name, publicationYear AS pubYear FROM metadata ORDER BY publicationYear;

but this also excludes the use of the index!

Activity

  1. gramian
    added
    bug
    on Oct 16, 2024
  2. gramian
    assigned
    lvca
    on Oct 16, 2024
  3. gramian
    changed the title [-]Index returns too many entries when used with `ORDER BY`[/-] [+]SQL: Index returns too many entries when used with `ORDER BY`[/+] on Oct 16, 2024
  4. gramian
    changed the title [-]SQL: Index returns too many entries when used with `ORDER BY`[/-] [+]SQL: Index can return too many entries when used with `ORDER BY`[/+] on Oct 16, 2024

  5. gramian commented on Oct 17, 2024

    @gramian
    gramian
    on Oct 17, 2024
    CollaboratorAuthor

    Using either UPDATE ... MERGE ... UPSERT or UPDATE ... CONTENT ... UPSERT produces too many index results.

  6. gramian commented on Nov 29, 2024

    @gramian
    gramian
    on Nov 29, 2024
    CollaboratorAuthor

    Here is some more testing which may help locate the source:

    • Both sorting directions ASC and DESC produce these duplicate results but different numbers: 146 for ASC, and 117 for DESC for a database with 81 records.
    • This index behavior occurs also for INSERT INTO ... CONTENT ...
    • Using DISTINCT removes the duplicates but this is no workaround as the in paged results the duplicates may be spread.

  7. gramian commented on Dec 11, 2024

    @gramian
    gramian
    on Dec 11, 2024
    CollaboratorAuthor

    More testing reveals:

    • Using multiple order keys like ORDER BY publicationYear, name returns the correct amount of results. @lvca This does also not use the publicationYear index, right?
    • The data type seems not to make a difference (tested with SHORT, INTEGER, and LONG).

  8. gramian commented on Jan 30, 2025

    @gramian
    gramian
    on Jan 30, 2025
    CollaboratorAuthor

    @robfrank Here is another test case using 25.1.1. It is the smallest I could produce yet with 39 records: metadatalake-backup-20250130-122441030.zip

    The query: SELECT name FROM metadata ORDER BY publicationYear produces correctly 39 results, while the query SELECT name, publicationYear FROM metadata ORDER BY publicationYear returns 50 results.

    (Reminder: these duplicates appear when a projected indexed property is also order key, here publicationYear)

    And another observation:

    Using REBUILD INDEX * causes the query SELECT name, publicationYear FROM metadata ORDER BY publicationYear to return 40 results. So the rebuild still produces the duplicate results, yet lesser.

    I hope this can help.

  9. robfrank
    added this to the 25.2.1 milestone on Jan 30, 2025
  10. robfrank
    self-assigned this
    on Jan 30, 2025

  11. gramian commented on Feb 12, 2025

    @gramian
    gramian
    on Feb 12, 2025
    CollaboratorAuthor

    Here is some more info that may help:

    • The records are UPSERTed (see example above) via the HTTP command endpoint using the SQLscript language.
    • A Unique index is running on the property recordId which is of type string with the attributes (mandatory true, notnull true, readonly true, max 31)
    • Wrapping the UPSERTs in an extra transaction does not make a difference.
  12. lvca
    modified the milestones: 25.2.1, 25.3.1 on Feb 27, 2025
  13. lvca
    modified the milestones: 25.3.1, 25.3.2 on Mar 7, 2025

  14. 22 remaining items

  15. lvca
    added
    fixed / implemented
    on Sep 30, 2025

  16. gramian commented on Sep 30, 2025

    @gramian
    gramian
    on Sep 30, 2025
    CollaboratorAuthor

    Works! Very Cool. Thank you very much @lvca

  17. robfrank
    added 2 commits that reference this issue on Nov 10, 2025

    fix: index cursor returning same entry multiple times

    0dcdbb1

    fix: fixed query with order by that uses the index to browse the resu…

    eee62d4
  18. mergify
    added a commit that references this issue on Apr 13, 2026

    chore(deps): bump jline.version from 4.0.10 to 4.0.12 [skip ci]

    79b0905
  19. mergify
    added a commit that references this issue on May 3, 2026

    chore(deps): bump org.neo4j.driver:neo4j-java-driver from 6.0.5 to 6.…

    5fed928
  20. tae898
    added 4 commits that reference this issue on Jun 28, 2026

    fix: index cursor returning same entry multiple times

    103faa4

    fix: fixed query with order by that uses the index to browse the resu…

    034dfc9

    chore(deps): bump jline.version from 4.0.10 to 4.0.12 [skip ci]

    ce6d462

    chore(deps): bump org.neo4j.driver:neo4j-java-driver from 6.0.5 to 6.…

    b027dd1
  21. mergify
    added 4 commits that reference this issue on Jun 29, 2026

    chore(deps): bump actions/cache/restore from 5.0.5 to 6.1.0 [skip ci]

    f9032ac

    chore(deps): bump actions/cache from 5.0.5 to 6.1.0 [skip ci]

    0d228fa

    chore(deps): bump actions/cache/save from 5.0.5 to 6.1.0 [skip ci]

    6b67d53

    chore(deps): bump org.neo4j.driver:neo4j-java-driver from 4.4.20 to 6…

    3273526
  22. mergify
    added a commit that references this issue on Aug 10, 2026

    chore(deps): bump actions/cache from 4.3.0 to 6.1.0 [skip ci]

    a694541
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

Bug

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions