Skip to content

ORDER BY [...] DESC does not work with LocalDateTime properties #839

Closed

Description

@vic0824

ArcadeDB Version: v23.1.2 (build 9dbe3ce/1675372179356/main)

JDK Version: openjdk version "11.0.12" 2021-07-20 LTS

OS: Windows 10 Enterprise 22H2

Expected behavior

ORDER BY works with both ASC and DESC options, when used with a DATETIME_MICROS property.

Actual behavior

ORDER BY [...] ASC works, ORDER BY [...] DESC does not work.

Steps to reproduce

Executing the attached testLocalDateTimeComparison test case, produces the following result:

sql String: INSERT INTO Product SET name = ?, type = ?, start = ?, stop = ?, parameters = [CS_OPER_AUX_ORBDOR_20220318T215523_20220320T002323_F001.EEF, AUX_ORBDOR, 2022-03-18T21:55:23, 2022-03-20T00:23:23]
result: CS_OPER_AUX_ORBDOR_20220318T215523_20220320T002323_F001.EEF, AUX_ORBDOR, 2022-03-18T21:55:23, 2022-03-20T00:23:23
sql String: SELECT name, start, stop FROM Product WHERE type = ? AND start <= ? AND stop >= ? ORDER BY start DESC, parameters = [AUX_ORBDOR, 2022-03-19T00:26:24.404379, 2022-03-19T00:28:26.525650]
sql String: SELECT name, start, stop FROM Product WHERE type = ? AND start <= ? AND stop >= ? ORDER BY start ASC, parameters = [AUX_ORBDOR, 2022-03-19T00:26:24.404379, 2022-03-19T00:28:26.525650]
start = 2022-03-18T21:55:23, stop = 2022-03-20T00:23:23

testLocalDateTimeOrderBy.zip

Activity

  1. self-assigned this
    on Feb 15, 2023
  2. added this to the 23.3.1 milestone on Mar 1, 2023
  3. vic0824 commented on Mar 23, 2023

    @vic0824
    Author

    Any news on this issue?

  4. lvca commented on Mar 23, 2023

    @lvca
    Member

    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
     */
    public class OrderByTest {
    
      @Test
      public void testLocalDateTimeOrderBy() {
        DatabaseFactory databaseFactory = new DatabaseFactory("databases/OrderByTest");
    
        if (databaseFactory.exists())
          databaseFactory.open().drop();
    
        try (Database db = databaseFactory.create()) {
          db.transaction(() -> {
            DocumentType dtProduct = 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");
    
        final Database database = databaseFactory.open();
    
        database.transaction(() -> {
          String name;
          String type;
          LocalDateTime start;
          LocalDateTime stop;
          DateTimeFormatter FILENAME_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyyMMdd'T'HHmmss");
          System.out.println();
          Result result;
          String sqlString = "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 (ResultSet resultSet = database.command("sql", sqlString, parameters1)) {
              Assertions.assertTrue(resultSet.hasNext());
              result = resultSet.next();
            } catch (Exception e) {
              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 };
    
            LocalDateTime lastStart = null;
    
            try (ResultSet resultSet = 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 (ResultSet resultSet = 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 (Exception e) {
            System.out.println("Error");
            e.printStackTrace();
          }
        });
      }
    }
  5. lvca commented on Mar 23, 2023

    @lvca
    Member

    Tried the same test with a server and actually it fails.

  6. lvca commented on Mar 23, 2023

    @lvca
    Member

    The issue with the server version is that the date implementation was not set. If set, the test passes:

      @Test
      public void testLocalDateTimeOrderBy() {
        final ContextConfiguration serverConfiguration = new ContextConfiguration();
        final String rootPath = IntegrationUtils.setRootPath(serverConfiguration);
    
        try (DatabaseFactory databaseFactory = new DatabaseFactory(rootPath + "/databases/" + DATABASE_NAME)) {
          if (databaseFactory.exists())
            databaseFactory.open().drop();
    
          try (Database db = databaseFactory.create()) {
            db.transaction(() -> {
              DocumentType dtProduct = 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");
    
          ArcadeDBServer arcadeDBServer = new ArcadeDBServer(serverConfiguration);
          arcadeDBServer.start();
          Database database = arcadeDBServer.getDatabase(DATABASE_NAME);
    
          try {
            database.transaction(() -> {
              String name;
              String type;
              LocalDateTime start;
              LocalDateTime stop;
              DateTimeFormatter FILENAME_TIME_FORMAT = DateTimeFormatter.ofPattern("yyyyMMdd'T'HHmmss");
              System.out.println();
              Result result;
              String sqlString = "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 (ResultSet resultSet = database.command("sql", sqlString, parameters1)) {
                  Assertions.assertTrue(resultSet.hasNext());
                  result = resultSet.next();
                } catch (Exception e) {
                  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 };
    
                LocalDateTime lastStart = null;
    
                try (ResultSet resultSet = 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 (ResultSet resultSet = 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 (Exception e) {
                System.out.println("Error");
                e.printStackTrace();
              }
            });
    
          } finally {
            arcadeDBServer.stop();
            databaseFactory.open().drop();
          }
        }
      }
  7. added a commit that references this issue on Mar 23, 2023
  8. vic0824 commented on Mar 24, 2023

    @vic0824
    Author

    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

    so your test is not testing the issue.

  9. vic0824 commented on Mar 25, 2023

    @vic0824
    Author

    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

  10. lvca commented on Mar 30, 2023

    @lvca
    Member

    Downloaded and I see the assertion failing. Thanks, it's much easier for me. Working on it.

  11. lvca commented on Mar 30, 2023

    @lvca
    Member

    I don't understand this assert at the end:

    Assertions.assertTrue(result.getProperty("start").equals(start), "start value retrieved does not match start value inserted");

    The value can't be the same.

  12. lvca commented on Mar 30, 2023

    @lvca
    Member

    In the meanwhile I found the issue with the ORDER BY DESC and fixed locally waiting for the CI tests to pass.

  13. lvca commented on Mar 30, 2023

    @lvca
    Member

    Fixed the order by desc issue and changed your latest assert to let the test pass.

  14. vic0824 commented on Mar 31, 2023

    @vic0824
    Author

    I don't understand this assert at the end:

    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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions