Skip to content

Composite Index corrupted after UPDATE #1113

New issue
New issue
Closed
Closed
Composite Index corrupted after UPDATE#1113
Assignees
lvca
Milestone
23.6.1

Description

@vic0824
vic0824
opened on Jun 5, 2023

ArcadeDB Version:

v23.5.1-SNAPSHOT (build 5412036/1685452164158/main)

OS and JDK Version:

Windows 10 10.0 - OpenJDK 64-Bit Server VM 11.0.12 (Corretto-11.0.12.7.1)

Expected behavior

After an update, a Select query with a Where clause on a field which is part of a composite index will find the correct record.

Actual behavior

After an update, a Select query with a Where clause on a field which is part of a composite index cannot find the correct record.

Steps to reproduce

Execute the attached testWhereAfterUpdate test case.

testWhereAfterUpdate.zip

Activity

  1. lvca commented on Jun 6, 2023

    @lvca
    lvca
    on Jun 6, 2023
    Member

    Trying to run your test case but I don't see errors:

    
insert result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#1:0"}
insert result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:0"}
update result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"COMPLETED","@cat":"d","@type":"Order","@rid":"#1:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"COMPLETED","@cat":"d","@type":"Order","@rid":"#1:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:0"}

Process finished with exit code 0
  2. lvca
    self-assigned this
    on Jun 6, 2023
  3. lvca
    added
    bug
    on Jun 6, 2023
  4. lvca
    added this to the 23.5.1 milestone on Jun 6, 2023

  5. vic0824 commented on Jun 7, 2023

    @vic0824
    vic0824
    on Jun 7, 2023
    Author

    It's weird, it fails for me:

    2023-06-07 08:31:35.073 INFO  [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server v23.5.1-SNAPSHOT (build 5412036f00b3f2a91c31c83174a14e032e19b85c/1685452164158/main) is starting up...
2023-06-07 08:31:35.084 INFO  [ArcadeDBServer] <ArcadeDB_0> Running on Windows 10 10.0 - OpenJDK 64-Bit Server VM 11.0.12 (Corretto-11.0.12.7.1)
2023-06-07 08:31:35.085 INFO  [ArcadeDBServer] <ArcadeDB_0> Starting ArcadeDB Server in development mode with plugins [] ...
2023-06-07 08:31:35.174 INFO  [ArcadeDBServer] <ArcadeDB_0> - JMX Metrics Started...
2023-06-07 08:31:35.387 INFO  [HttpServer] <ArcadeDB_0> - Starting HTTP Server (host=0.0.0.0 port=2480-2489 httpsPort=2490-2499)...
2023-06-07 08:31:35.969 INFO  [HttpServer] <ArcadeDB_0> - HTTP Server started (host=0.0.0.0 port=2480 httpsPort=2490)
2023-06-07 08:31:35.970 INFO  [ArcadeDBServer] <ArcadeDB_0> Available query languages: [sqlscript, java, js, sql]
2023-06-07 08:31:35.973 INFO  [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server started in 'development' mode (CPUs=8 MAXRAM=3.93GB)
2023-06-07 08:31:35.973 INFO  [ArcadeDBServer] <ArcadeDB_0> Studio web tool available at http://172.21.32.1:2480 
insert result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:0"}
insert result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:1"}
update result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"COMPLETED","@cat":"d","@type":"Order","@rid":"#2:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"COMPLETED","@cat":"d","@type":"Order","@rid":"#2:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:1"}
Exception in thread "main" org.opentest4j.AssertionFailedError: expected: <true> but was: <false>
	at org.junit.jupiter.api.AssertionFailureBuilder.build(AssertionFailureBuilder.java:151)
	at org.junit.jupiter.api.AssertionFailureBuilder.buildAndThrow(AssertionFailureBuilder.java:132)
	at org.junit.jupiter.api.AssertTrue.failNotTrue(AssertTrue.java:63)
	at org.junit.jupiter.api.AssertTrue.assertTrue(AssertTrue.java:36)
	at org.junit.jupiter.api.AssertTrue.assertTrue(AssertTrue.java:31)
	at org.junit.jupiter.api.Assertions.assertTrue(Assertions.java:180)
	at eu.serco.csrs.persistence.ArcadeDBTest.testWhereAfterUpdate(ArcadeDBTest.java:195)
	at eu.serco.csrs.persistence.ArcadeDBTest.main(ArcadeDBTest.java:67)

    In my test class, line 195 is: Assertions.assertTrue(resultSet.hasNext());

  6. vic0824 commented on Jun 7, 2023

    @vic0824
    vic0824
    on Jun 7, 2023
    Author

    Pulled the latest snapshot, run clean + install, re-compiled my Test class, and it still fails:

    2023-06-07 08:45:40.746 INFO  [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server v23.5.1-SNAPSHOT (build 3a5380ee1e5fe0a7514bf74119a901b09380c1e7/1686119608920/main) is starting up...
2023-06-07 08:45:40.754 INFO  [ArcadeDBServer] <ArcadeDB_0> Running on Windows 10 10.0 - OpenJDK 64-Bit Server VM 11.0.12 (Corretto-11.0.12.7.1)
2023-06-07 08:45:40.756 INFO  [ArcadeDBServer] <ArcadeDB_0> Starting ArcadeDB Server in development mode with plugins [] ...
2023-06-07 08:45:40.837 INFO  [ArcadeDBServer] <ArcadeDB_0> - JMX Metrics Started...
2023-06-07 08:45:41.052 INFO  [HttpServer] <ArcadeDB_0> - Starting HTTP Server (host=0.0.0.0 port=2480-2489 httpsPort=2490-2499)...
2023-06-07 08:45:41.573 INFO  [HttpServer] <ArcadeDB_0> - HTTP Server started (host=0.0.0.0 port=2480 httpsPort=2490)
2023-06-07 08:45:41.574 INFO  [ArcadeDBServer] <ArcadeDB_0> Available query languages: [sqlscript, java, js, sql]
2023-06-07 08:45:41.575 INFO  [ArcadeDBServer] <ArcadeDB_0> ArcadeDB Server started in 'development' mode (CPUs=8 MAXRAM=3.93GB)
2023-06-07 08:45:41.575 INFO  [ArcadeDBServer] <ArcadeDB_0> Studio web tool available at http://172.21.32.1:2480 
insert result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:0"}
insert result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:1"}
update result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"COMPLETED","@cat":"d","@type":"Order","@rid":"#2:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557014764069841000,"vstop":1557014877423797000,"id":1,"status":"COMPLETED","@cat":"d","@type":"Order","@rid":"#2:0"}
select result = {"processor":"SIR1LRM-7.1","vstart":1557015037288211000,"vstop":1557015158236835000,"id":2,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:1"}
Exception in thread "main" org.opentest4j.AssertionFailedError: expected: <true> but was: <false>
	at org.junit.jupiter.api.AssertionFailureBuilder.build(AssertionFailureBuilder.java:151)
	at org.junit.jupiter.api.AssertionFailureBuilder.buildAndThrow(AssertionFailureBuilder.java:132)
	at org.junit.jupiter.api.AssertTrue.failNotTrue(AssertTrue.java:63)
	at org.junit.jupiter.api.AssertTrue.assertTrue(AssertTrue.java:36)
	at org.junit.jupiter.api.AssertTrue.assertTrue(AssertTrue.java:31)
	at org.junit.jupiter.api.Assertions.assertTrue(Assertions.java:180)
	at eu.serco.csrs.persistence.ArcadeDBTest.testWhereAfterUpdate(ArcadeDBTest.java:195)
	at eu.serco.csrs.persistence.ArcadeDBTest.main(ArcadeDBTest.java:67)

  7. lvca commented on Jun 8, 2023

    @lvca
    lvca
    on Jun 8, 2023
    Member

    Could you please run the test com.arcadedb.server.CompositeIndexTest?

  8. vic0824 commented on Jun 8, 2023

    @vic0824
    vic0824
    on Jun 8, 2023
    Author

    It passes if I run it using Maven from the arcadedb project.
    It passes also if I copy it and paste it into my project.
    But my test case fails, so now I need to find out what's different between the two methods.

  9. vic0824 commented on Jun 8, 2023

    @vic0824
    vic0824
    on Jun 8, 2023
    Author

    The only difference between my test case and yours is that I create all the database schema objects in a transaction scope, but you don't.
    Sure enough, if I move the type and index creation code out of the transaction scope, the test passes.
    Why does this happens? The code examples in the manual show how to create types and it is always done inside a transaction.

  10. lvca
    modified the milestones: 23.5.1, 23.6.1 on Jun 9, 2023

  11. vic0824 commented on Jun 9, 2023

    @vic0824
    vic0824
    on Jun 9, 2023
    Author

    Tried to remove the transaction from my Project, and it still does not work.
    After the update, the select query using WHERE = 'PENDING' does not work, however if I modify it to use WHERE LIKE 'PENDING%' it works. Does LIKE not use the index?

  12. lvca commented on Jun 9, 2023

    @lvca
    lvca
    on Jun 9, 2023
    Member

    This issue is getting more interesting :-) I'm checking to see what's going on.

  13. vic0824 commented on Jun 9, 2023

    @vic0824
    vic0824
    on Jun 9, 2023
    Author

    Wait, it gets even weirder.
    Out of sheer desperation, I have tried changing things that shouldn't have anything to do with the issue.
    I have replaced the value PENDING with the value ELIGIBLE and now everything works, with or without transaction.
    Is there something special about the way the value PENDING is interpreted by the engine?

  14. lvca commented on Jun 9, 2023

    @lvca
    lvca
    on Jun 9, 2023
    Member

    No, pending is not a keyword... I'm debugging the 1st simple case where you create the schema in a tx.

  15. vic0824 commented on Jun 9, 2023

    @vic0824
    vic0824
    on Jun 9, 2023
    Author

    False alarm, I have tried again and it fails also with ELIGIBLE.

  16. lvca commented on Jun 9, 2023

    @lvca
    lvca
    on Jun 9, 2023
    Member

    I see you use this UPSERT:

    UPDATE Order SET id = ?, processor = ?, vstart = ?, vstop = ?, status = ? UPSERT RETURN AFTER WHERE processor = ? AND vstart = ? AND vstop = ?

    But it shouldn't be allowed because it requires the WHERE condition to use the index. Look at the docs (UPSERT limitation paragraph):

    https://docs.arcadedb.com/#SQL-Update

    So I'm surprised the SQL engine swallows the syntax without any error.

  17. lvca commented on Jun 9, 2023

    @lvca
    lvca
    on Jun 9, 2023
    Member

    Checked the OrientDB code and it's the same. I'm wondering if a syntax check is totally missing here.

  18. vic0824 commented on Jun 9, 2023

    @vic0824
    vic0824
    on Jun 9, 2023
    Author

    In my Project I create a temporary index before inserting a lot of records and then I delete the index; I didn't include it in the test case because I forgot about the constraint about the UPSERT.
    Anyway, I have added
    dtOrders.createTypeIndex(Schema.INDEX_TYPE.LSM_TREE, true, "processor", "vstart", "vstop"); to the test case
    and it does not work if I create the db schema objects inside a transaction, but it works if I create them outside of a transaction.

  19. lvca commented on Jun 9, 2023

    @lvca
    lvca
    on Jun 9, 2023
    Member

    It looks like that constraint was never applied in OrientDB, then ArcadeDB. So nevermind about the UPSERT. The point here is that the failing query is using a composite index with a partial key and fails. Checking on it.

  20. lvca commented on Jun 9, 2023

    @lvca
    lvca
    on Jun 9, 2023
    Member

    Found the issue. The LSMTreeCursor, at build time, checks if the cursor is valid, but when it finds out that the 1st entry has been updated, it didn't continue with the next element. Fixed the issue locally, waiting to check for any regressions.

  21. lvca
    added a commit that references this issue on Jun 9, 2023

    fix: fixed issue with index cursor and deleted/updated items

    0725210
  22. lvca
    closed this as completedon Jun 9, 2023
  23. lvca
    added
    bug fixed
    on Jun 9, 2023
  24. lvca
    added a commit that references this issue on Jun 9, 2023

    fix: fixed latest test case for issue #1113

    f07f377

  25. vic0824 commented on Jun 10, 2023

    @vic0824
    vic0824
    on Jun 10, 2023
    Author

    It works, thanks!

  26. tae898
    added 2 commits that reference this issue on Jun 28, 2026

    fix: fixed issue with index cursor and deleted/updated items

    3bb8c5d

    fix: fixed latest test case for issue ArcadeData#1113

    ef7b0a6
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

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions