Duplicate documents when index used with Greater Than #2328
Description
Hi,
I have a Long Property on a document, used as a 'High Water Mark' for tracking the processing of data. When I have a non unique index present, and I do a select with > x I am returned duplicate records. When I remove the index it works fine.
Please see test case below, I have tried on 25.5.1 to 24.11.2 and all fail. If you comment out the index creation it works.
Thanks!
`package arcadedb_issues;
import java.io.File;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;
import com.arcadedb.database.BasicDatabase;
import com.arcadedb.database.DatabaseFactory;
import com.arcadedb.database.MutableDocument;
import com.arcadedb.query.sql.executor.Result;
import com.arcadedb.query.sql.executor.ResultSet;
import com.arcadedb.schema.DocumentType;
import com.arcadedb.schema.Schema;
import com.arcadedb.schema.Schema.INDEX_TYPE;
public class GTLongIndexIssue {
private GTLongIndexIssue() {}
public static void main(final String[] args) throws IOException {
final File tmp = File.createTempFile("ArcadeTmp", "");
tmp.delete();
System.out.println("Tmp Database: " + tmp);
try(final DatabaseFactory fact = new DatabaseFactory(tmp.toString())){
try(final BasicDatabase local = fact.create()){
local.begin();
final Schema schema = local.getSchema();
// Create type
final DocumentType tc = schema.createDocumentType("example");
// Create props
tc.createProperty("p1", String.class);
tc.createProperty("p2", Long.class);
// Index props
tc.createTypeIndex(INDEX_TYPE.LSM_TREE, false, "p2");
// Add dummy data
final String[] perm = { "a", "b", "c", "d", "e", "f", "g", "h" };
long idx = 0;
for(final String l1 : perm) {
for(final String l2 : perm) {
for(final String l3 : perm) {
final MutableDocument doc = local.newDocument("example");
doc.set("p1", l1 + l2 + l3);
doc.set("p2", idx);
doc.save();
}
}
idx++;
}
local.commit();
local.begin();
// Get data from high water mark
final ResultSet rs = local.command("sql", "select * from example where p2>?", 5);
// Output data check for dups
int count = 0;
int dup = 0;
final Set<String> found = new HashSet<>();
System.out.println();
while(rs.hasNext()) {
final Result res = rs.next();
final String p1 = res.getProperty("p1");
final boolean add = found.add(p1);
if(!add) {
dup++;
}
System.out.println(p1 + " - " + (Long)res.getProperty("p2") + " - dup: " + !add);
count++;
}
System.out.println("Found: " + count);
System.out.println("Duplicates: " + dup);
if(dup>0) {
throw new IllegalStateException("Duplicates found: " + dup);
}
}
}
}
}
`