order of properties returned by SELECT does not match order specified

[vic0824]

ArcadeDB Version: v22.11.1

JDK Version: openjdk 11.0.12 2021-07-20 LTS

OpenJDK Runtime Environment Corretto-11.0.12.7.1 (build 11.0.12+7-LTS)
OpenJDK 64-Bit Server VM Corretto-11.0.12.7.1 (build 11.0.12+7-LTS, mixed mode)

OS: CentOS release 6.9 (Final)

Expected behavior

the records returned by a SELECT are presented with the properties listed in the same order in which they were specified in the SELECT.

Actual behavior

the records returned by a SELECT are presented in an "ArcadeDB default order", which is not easy to understand and is not the same order specified in the SELECT.

Steps to reproduce

1. create a database
2. create a Document type called Product
3. create properties called name, type, start, stop (in this order)
4. add a record: name = CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL, type = AUX_LS_MAP, start = 19000101_000000.000000, stop = 99991231_235959.000000
5. add another record: name = CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL, type = AUX_ODLE__, start = 19000101_000000.000000, stop = 99991231_235959.000000
6. execute "select name, type from Product limit 2" and verify that records are presented with the properties in the following order: [name, type]
7. execute "select name, type, start from Product limit 2" and verify that records are presented with the properties in the following order: [name, start, type]
8. execute "select name, type, start, stop from Product limit 2" and verify that records are presented with the properties in the following order: [stop, name, start, type]
9. execute "select from Product limit 2" and verify that the record are presented with the properties in the following order: [#, [@]RID, @type, stop, name, start, type]

This is the test that I have performed:

select name, type from Product limit 2

+----+-----------------------------------------------------------+----------+
|# |name |type |
+----+-----------------------------------------------------------+----------+
|0 |CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL|AUX_LS_MAP|
|1 |CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL|AUX_ODLE__|
+----+-----------------------------------------------------------+----------+
Command executed in 3ms

select name, type, start from Product limit 2

+----+-----------------------------------------------------------+----------------------+----------+
|# |name |start |type |
+----+-----------------------------------------------------------+----------------------+----------+
|0 |CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL|19000101_000000.000000|AUX_LS_MAP|
|1 |CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL|19000101_000000.000000|AUX_ODLE__|
+----+-----------------------------------------------------------+----------------------+----------+
Command executed in 2ms

select name, type, start, stop from Product limit 2

+----+----------------------+-----------------------------------------------------------+----------------------+----------+
|# |stop |name |start |type |
+----+----------------------+-----------------------------------------------------------+----------------------+----------+
|0 |99991231_235959.000000|CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL|19000101_000000.000000|AUX_LS_MAP|
|1 |99991231_235959.000000|CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL|19000101_000000.000000|AUX_ODLE__|
+----+----------------------+-----------------------------------------------------------+----------------------+----------+
Command executed in 2ms

select from Product limit 2

+----+----+-------+----------------------+-----------------------------------------------------------+----------------------+----------+
|# |@Rid|@type |stop |name |start |type |
+----+----+-------+----------------------+-----------------------------------------------------------+----------------------+----------+
|0 |#4:0|Product|99991231_235959.000000|CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL|19000101_000000.000000|AUX_LS_MAP|
|1 |#4:1|Product|99991231_235959.000000|CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL|19000101_000000.000000|AUX_ODLE__|
+----+----+-------+----------------------+-----------------------------------------------------------+----------------------+----------+
Command executed in 2ms

[lvca]

I believe the issue is with using a Set instead of an ArrayList for the resultset. I already fix a similar issue in the past week. Could you please try the most recent version?

[vic0824]

Tested it with v23.1.1-SNAPSHOT (build 1b37118/1672647527307/main).

It still does not return the properties in the expected order:

> select name, type, start, stop, v from Product

+-----+-----------------------------------------------------------+
|NAME |VALUE                                                      |
+-----+-----------------------------------------------------------+
|stop |20221030_193923.000000                                     |
|v    |0001                                                       |
|name |CS_OPER_MPL_ORBREF_20220606T002921_20221030T193923_0001.EEF|
|start|20220606_002921.000000                                     |
|type |MPL_ORBREF                                                 |
+-----+-----------------------------------------------------------+
Command executed in 4ms

[lvca]

I tried against the latest snapshot and always works for me in the console. Could you please provide a simple test case in terms of SQL commands to execute in the console?

[vic0824]

Tested it against v23.1.1-SNAPSHOT (build 8c8df17/1672742152255/main).
These are the commands I have executed in the Console:

{csrsdb}> select from Order

Command executed in 1ms
{csrsdb}> insert into Order set processor = 'SIR1LRM-7.1', vstart = '20220319_002624.404379', vstop = '20220319_002826.525650', status = 'PENDING'

DOCUMENT @type:Order @rid:#1:0
+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|processor|SIR1LRM-7.1           |
|status   |PENDING               |
+---------+----------------------+
Command executed in 11ms
{csrsdb}> select from Order

DOCUMENT @type:Order @rid:#1:0
+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|processor|SIR1LRM-7.1           |
|status   |PENDING               |
+---------+----------------------+
Command executed in 2ms
{csrsdb}> select processor, vstart, vstop, pstart, pstop, status, node from Order

+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|node     |<null>                |
|pstop    |<null>                |
|vstart   |20220319_002624.404379|
|pstart   |<null>                |
|vstop    |20220319_002826.525650|
|processor|SIR1LRM-7.1           |
|status   |PENDING               |
+---------+----------------------+
Command executed in 2ms
{csrsdb}>

[lvca]

Weird, the same test returns (I'm using the latest main branch):

+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|processor|SIR1LRM-7.1           |
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|pstart   |<null>                |
|pstop    |<null>                |
|status   |PENDING               |
|node     |<null>                |
+---------+----------------------+
Command executed in 24ms

[lvca]

I've written a test case on that and it passes:

/**
   * Issue https://github.com/ArcadeData/arcadedb/issues/726
   */
  @Test
  public void testProjectionOrder() throws IOException {
    Assertions.assertTrue(console.parse("connect " + DB_NAME, false));
    Assertions.assertTrue(console.parse("create document type Order", false));
    Assertions.assertTrue(console.parse(
        "insert into Order set processor = 'SIR1LRM-7.1', vstart = '20220319_002624.404379', vstop = '20220319_002826.525650', status = 'PENDING'", false));

    {
      final StringBuilder buffer = new StringBuilder();
      console.setOutput(output -> buffer.append(output));
      Assertions.assertTrue(console.parse("select processor, vstart, vstop, pstart, pstop, status, node from Order", false));

      int pos = buffer.toString().indexOf("processor");
      Assertions.assertTrue(pos > -1);
      pos = buffer.toString().indexOf("vstart", pos);
      Assertions.assertTrue(pos > -1);
      pos = buffer.toString().indexOf("vstop", pos);
      Assertions.assertTrue(pos > -1);
      pos = buffer.toString().indexOf("pstart", pos);
      Assertions.assertTrue(pos > -1);
      pos = buffer.toString().indexOf("pstop", pos);
      Assertions.assertTrue(pos > -1);
      pos = buffer.toString().indexOf("status", pos);
      Assertions.assertTrue(pos > -1);
      pos = buffer.toString().indexOf("node", pos);
      Assertions.assertTrue(pos > -1);
    }
  }

[vic0824]

Mystery solved.
I have executed your test case and it passes, but mine doesn't.
The only difference is that you are connecting to a local database, while I connect to a remote database.
I have therefore tested both scenarios in the same console session and I have verified that the order is only preserved in local mode, not in remote mode. There must be something in the server code that re-arranges the property order before sending the results back to the Console.

> create database Test

Database 'Test' created
{Test}> create document type Order

+---------+--------------------+
|NAME     |VALUE               |
+---------+--------------------+
|operation|create document type|
|typeName |Order               |
+---------+--------------------+
Command executed in 120ms
{Test}> insert into Order set processor = 'SIR1LRM-7.1', vstart = '20220319_002624.404379', vstop = '20220319_002826.525650', status = 'PENDING'

DOCUMENT @type:Order @rid:#1:0
+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|processor|SIR1LRM-7.1           |
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|status   |PENDING               |
+---------+----------------------+
Command executed in 18ms
{Test}> select from Order

DOCUMENT @type:Order @rid:#1:0
+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|processor|SIR1LRM-7.1           |
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|status   |PENDING               |
+---------+----------------------+
Command executed in 12ms
{Test}> select processor, vstart, vstop, status from Order

+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|processor|SIR1LRM-7.1           |
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|status   |PENDING               |
+---------+----------------------+
Command executed in 5ms

{Test}> close

> create database remote:localhost:8080/Test1 testUsr testPwd

Database 'Test1' created
{Test1}> create document type Order

+---------+--------------------+
|NAME     |VALUE               |
+---------+--------------------+
|typeName |Order               |
|operation|create document type|
+---------+--------------------+
Command executed in 15ms
{Test1}> insert into Order set processor = 'SIR1LRM-7.1', vstart = '20220319_002624.404379', vstop = '20220319_002826.525650', status = 'PENDING'

DOCUMENT @type:Order @rid:#1:0
+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|processor|SIR1LRM-7.1           |
|status   |PENDING               |
+---------+----------------------+
Command executed in 14ms
{Test1}> select from Order

DOCUMENT @type:Order @rid:#1:0
+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|processor|SIR1LRM-7.1           |
|status   |PENDING               |
+---------+----------------------+
Command executed in 11ms
{Test1}> select processor, vstart, vstop, status from Order

+---------+----------------------+
|NAME     |VALUE                 |
+---------+----------------------+
|vstart   |20220319_002624.404379|
|vstop    |20220319_002826.525650|
|processor|SIR1LRM-7.1           |
|status   |PENDING               |
+---------+----------------------+
Command executed in 6ms
{Test1}>

[lvca]

Gotcha! It must be the JSON library that removes the ordering. I've sent a PR to the JSON library project but takes >1y to approve it... Thinking to an alternative solution.

[lvca]

There is no hope of having this fix in org.json library :-(

stleary/JSON-java#681 (comment)

I'm going to write a wrapper compatible with org.json but that uses the Google GSON library under the hood. GSON is already included in ArcadeDB because used by 3rd party libs.

[vic0824]

Thanks for the status update.