You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ORDER BY [...] DESC does not work with LocalDateTime properties #839
I've created this test case from your source (your source had not assertions that failed, so when I run it was green I didn't read the output). And it works with the main branch version:
/** * From Issue https://github.com/ArcadeData/arcadedb/issues/839 */publicclassOrderByTest {
@TestpublicvoidtestLocalDateTimeOrderBy() {
DatabaseFactorydatabaseFactory = newDatabaseFactory("databases/OrderByTest");
if (databaseFactory.exists())
databaseFactory.open().drop();
try (Databasedb = databaseFactory.create()) {
db.transaction(() -> {
DocumentTypedtProduct = db.getSchema().createDocumentType("Product");
dtProduct.createProperty("name", Type.STRING);
dtProduct.createProperty("type", Type.STRING);
dtProduct.createProperty("start", Type.DATETIME_MICROS);
dtProduct.createProperty("stop", Type.DATETIME_MICROS);
dtProduct.createTypeIndex(Schema.INDEX_TYPE.LSM_TREE, true, "name");
dtProduct.createTypeIndex(Schema.INDEX_TYPE.LSM_TREE, true, "type", "start", "stop");
});
}
GlobalConfiguration.DATE_TIME_IMPLEMENTATION.setValue(java.time.LocalDateTime.class);
GlobalConfiguration.DATE_TIME_FORMAT.setValue("yyyy-MM-dd'T'HH:mm:ss.SSSSSS");
finalDatabasedatabase = databaseFactory.open();
database.transaction(() -> {
Stringname;
Stringtype;
LocalDateTimestart;
LocalDateTimestop;
DateTimeFormatterFILENAME_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyyMMdd'T'HHmmss");
System.out.println();
Resultresult;
StringsqlString = "INSERT INTO Product SET name = ?, type = ?, start = ?, stop = ?";
try {
name = "CS_OPER_AUX_ORBDOR_20220318T215523_20220320T002323_F001.EEF";
type = "AUX_ORBDOR";
start = LocalDateTime.parse("20220318T215523", FILENAME_TIME_FORMAT);
stop = LocalDateTime.parse("20220320T002323", FILENAME_TIME_FORMAT);
Object[] parameters1 = { name, type, start, stop };
try (ResultSetresultSet = database.command("sql", sqlString, parameters1)) {
Assertions.assertTrue(resultSet.hasNext());
result = resultSet.next();
} catch (Exceptione) {
System.out.println(e.getMessage());
e.printStackTrace();
}
sqlString = "SELECT name, start, stop FROM Product WHERE type = ? AND start <= ? AND stop >= ? ORDER BY start DESC";
type = "AUX_ORBDOR";
start = LocalDateTime.parse("2022-03-19T00:26:24.404379", DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSSSS"));
stop = LocalDateTime.parse("2022-03-19T00:28:26.525650", DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSSSS"));
Object[] parameters3 = { type, start, stop };
LocalDateTimelastStart = null;
try (ResultSetresultSet = database.query("sql", sqlString, parameters3)) {
Assertions.assertTrue(resultSet.hasNext());
while (resultSet.hasNext()) {
result = resultSet.next();
start = result.getProperty("start");
if (lastStart != null)
Assertions.assertTrue(start.compareTo(lastStart) <= 0, "" + start + " is greater than " + lastStart);
lastStart = start;
}
}
lastStart = null;
sqlString = "SELECT name, start, stop FROM Product WHERE type = ? AND start <= ? AND stop >= ? ORDER BY start ASC";
try (ResultSetresultSet = database.query("sql", sqlString, parameters3)) {
Assertions.assertTrue(resultSet.hasNext());
while (resultSet.hasNext()) {
result = resultSet.next();
start = result.getProperty("start");
if (lastStart != null)
Assertions.assertTrue(start.compareTo(lastStart) >= 0, "" + start + " is smaller than " + lastStart);
lastStart = start;
}
}
} catch (Exceptione) {
System.out.println("Error");
e.printStackTrace();
}
});
}
}
Maybe I'm being dumb, but I think you test passes by design, because:
in the first iteration of the while loop, resultSet.hasNext() is true (so the body of the while block is executed), but lastStart = null, so the assertion is not performed
in the second iteration of the while loop, resultSet.hasNext() is false (because there is only one record in the type), so the body of the while loop is not executed at all
I have pulled the latest main branch and repeated the test, and it still fails.
I have re-written a simplified test case using assertions and I have added an assertion specifically to test that the context configuration uses the correct datetime implementation.
I hope this will allow you to reproduce the problem. testLocalDateTimeOrderBy.zip
Assertions.assertTrue(result.getProperty("start").equals(start), "start value retrieved does not match start value inserted");
The value can't be the same.
My bad. I copied and pasted the previous assertion but forgot that in the meantime I had assigned another value to the start variable. In facts, the whole while loop is unnecessary, it is sufficient to test hasNext().
As soon as I get a chance, I will pull the latest version and perform a more extensive test with real values.
Activity
vic0824 commentedon Mar 23, 2023
Any news on this issue?
lvca commentedon Mar 23, 2023
I've created this test case from your source (your source had not assertions that failed, so when I run it was green I didn't read the output). And it works with the
mainbranch version:lvca commentedon Mar 23, 2023
Tried the same test with a server and actually it fails.
lvca commentedon Mar 23, 2023
The issue with the server version is that the date implementation was not set. If set, the test passes:
test: added test cases for #839
vic0824 commentedon Mar 24, 2023
Maybe I'm being dumb, but I think you test passes by design, because:
resultSet.hasNext()is true (so the body of the while block is executed), butlastStart = null, so the assertion is not performedresultSet.hasNext()is false (because there is only one record in the type), so the body of the while loop is not executed at allso your test is not testing the issue.
vic0824 commentedon Mar 25, 2023
I have pulled the latest main branch and repeated the test, and it still fails.
I have re-written a simplified test case using assertions and I have added an assertion specifically to test that the context configuration uses the correct datetime implementation.
I hope this will allow you to reproduce the problem.
testLocalDateTimeOrderBy.zip
lvca commentedon Mar 30, 2023
Downloaded and I see the assertion failing. Thanks, it's much easier for me. Working on it.
lvca commentedon Mar 30, 2023
I don't understand this assert at the end:
The value can't be the same.
lvca commentedon Mar 30, 2023
In the meanwhile I found the issue with the
ORDER BY DESCand fixed locally waiting for the CI tests to pass.fix: order by desc with partial index match
lvca commentedon Mar 30, 2023
Fixed the
order by descissue and changed your latest assert to let the test pass.vic0824 commentedon Mar 31, 2023
My bad. I copied and pasted the previous assertion but forgot that in the meantime I had assigned another value to the start variable. In facts, the whole while loop is unnecessary, it is sufficient to test
hasNext().As soon as I get a chance, I will pull the latest version and perform a more extensive test with real values.
chore(studio-deps-dev)(deps-dev): bump semver from 7.7.3 to 7.7.4 in …
chore(deps): update testcontainers requirement from >=4.9.1 to >=4.13…
test: added test cases for ArcadeData#839
fix: order by desc with partial index match
chore(studio-deps-dev)(deps-dev): bump semver from 7.7.3 to 7.7.4 in …
chore(deps): update testcontainers requirement from >=4.9.1 to >=4.13…