Order by on an indexed property does not work anymore after sequence of insert -> delete -> insert #1152

Activity

1. vic0824 commented on Jun 30, 2023

   

   vic0824

   on Jun 30, 2023

   Author

   More actions

   I attach a snapshot of the database after the second insert, i.e. when the assertion fails.

   Let me know if you need anything else to investigate.

   At the moment, this is the only thing that stops my migration from Hsqldb to ArcadeDB.

   databases.zip
2. vic0824 commented on Jun 30, 2023

   

   vic0824

   on Jun 30, 2023

   Author

   More actions

   I have pinpointed the exact issue: order by does not work, but order by desc works.
   I have used the following code:

   final Collection<TypeIndex> indexes = database.getSchema().getType("Order").getIndexesByProperties("id");
   final Index index = indexes.iterator().next();
   System.out.println("index name = " + index.getName() + ", properties = " + index.getPropertyNames());
   IndexCursor iterator = ((RangeIndex) index).iterator(true);
   System.out.println("iterating ASC: ");
   while (iterator.hasNext()) {
       System.out.println(iterator.next().asDocument(true).toJSON());
   }
   System.out.println("iterating DESC: ");
   iterator = ((RangeIndex) index).iterator(false);
   while (iterator.hasNext()) {
       System.out.println(iterator.next().asDocument(true).toJSON());
   }
   

   which produces the following output:

   index name = Order[status,id], properties = [status, id]
   interating ASC: 
   interating DESC: 
   {"id":4,"processor":"SIR1LRM-7.1","trigger":"#19:0","vstart":1685447870057592000,"vstop":1685447930057592000,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:3"}
   {"id":3,"processor":"SIR1LRM-7.1","trigger":"#19:0","vstart":1685447810057592000,"vstop":1685447870057592000,"status":"PENDING","@cat":"d","@type":"Order","@rid":"#2:2"}
   
3. lvca

   added a commit that references this issue on Jun 30, 2023

   fix: query with order by doesn't handle properly the cursor when the …

   ...

   bae4766
4. lvca commented on Jun 30, 2023

   

   lvca

   on Jun 30, 2023

   Member

   More actions

   Thanks, @vic0824 I was able to reproduce the issue, thanks to your test case, download and run 👍

   The issue is in the way the cursors are initiated and it wasn't working correctly when the 1st item found was a deleted item.

   I've added this test case to the test suite in ConsoleAsyncInsertTest.testOrderByAfterDeleteInsert()
5. lvca

   closed this as completedon Jun 30, 2023
6. lvca

   self-assigned this

   on Jun 30, 2023
7. lvca

   added

   bug

   bug fixed

   on Jun 30, 2023
8. lvca

   added this to the 23.6.1 milestone on Jun 30, 2023
9. vic0824 commented on Jul 1, 2023

   

   vic0824

   on Jul 1, 2023

   Author

   More actions

   Excellent! thanks.
10. tae898

    added a commit that references this issue on Jun 28, 2026

    fix: query with order by doesn't handle properly the cursor when the …

    ...

    760d4ed