ORDER BY @rid ASC adds spurious _$$$ORDER_BY_ALIAS$$$_0 column to SELECT results when @rid is explicitly included in list of fields

[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

Consistent behavior of ORDER BY [@]rid ASC and ORDER BY [@]rid DESC when [@]rid is explicitly included in list of fields.
NOTE: I write [@]rid with square brackets because otherwise GitHub will interpret it as a mention of the user rid, who probably does not want to receive notifications about this issue.

Actual behavior

If ORDER BY [@]rid ASC is used in a select query, the console shows an additional spurious _$$$ORDER_BY_ALIAS$$$_0 column to the results.
The same behavior can be observed in Studio.

Steps to reproduce

1. Create a database
2. Create a document type called "Product"
3. add some properties, including a property called "name"
4. open the Console
5. connect to the database
6. execute the following query: "select from Product limit 2" and verify that all the properties are included and the records are sorted by [@]rid ascending (in lexicographical order, but that's a separate issue)
7. execute "select from Product order by [@]rid limit 2" and verify that the result is the same as in the previous step
8. execute "select from Product order by [@]rid ASC limit 2" and verify that the result is still the same, thus proving that the default sorting is by [@]rid ascending
9. execute "select from Product order by [@]rid DESC limit 2" and verify that the records are sorted by [@]rid descending
10. execute "select [@]rid, name from Product order by [@]rid limit 2" and verify that the records are sorted by [@]rid ascending but they include an additional spurious column, called _$$$ORDER_BY_ALIAS$$$_0, which contains the [@]rid value
11. execute "select [@]rid, name from Product order by [@]rid ASC limit 2" and verify that the result is the same as in the previous step
12. execute "select [@]rid, name from Product order by [@]rid DESC limit 2" and verify that the records are sorted by [@]rid descending and there is no additional spurious column
13. Open Studio and log in
14. repeat steps 6-12 and verify that the same behavior is observed in Studio
    This are the steps that I have performed:

select from Product limit 2

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

select from Product order by [@]rid limit 2

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

select from Product order by [@]rid ASC limit 2

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

select from Product order by [@]rid DESC limit 2

+----+-----+-------+----------------------+----+-----------------------------------------------------------+----------------------+----------+
|# |[@]RID |@type |stop |v |name |start |type |
+----+-----+-------+----------------------+----+-----------------------------------------------------------+----------------------+----------+
|0 |#13:0|Product|20220401_000000.000000|0001|CS_OPER_AUX_SUNACT_19910101T000000_20220401T000000_0001.DBL|19910101_000000.000000|AUX_SUNACT|
|1 |#12:0|Product|20221213_013153.018765|0001|CS_OPER_SIR1LRM_0__20221213T012441_20221213T013153_0001.DBL|20221213_012440.808499|SIR1LRM_0_|
+----+-----+-------+----------------------+----+-----------------------------------------------------------+----------------------+----------+
Command executed in 2ms

select [@]rid, name from Product order by [@]rid limit 2

+----+----+-----------------------+-----------------------------------------------------------+
|# |[@]RID|_$$$ORDER_BY_ALIAS$$$_0|name |
+----+----+-----------------------+-----------------------------------------------------------+
|0 |#4:0|#4:0 |CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL|
|1 |#4:1|#4:1 |CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL|
+----+----+-----------------------+-----------------------------------------------------------+
Command executed in 2ms

select [@]rid, name from Product order by [@]rid ASC limit 2

+----+----+-----------------------+-----------------------------------------------------------+
|# |[@]RID|_$$$ORDER_BY_ALIAS$$$_0|name |
+----+----+-----------------------+-----------------------------------------------------------+
|0 |#4:0|#4:0 |CS_OPER_AUX_LS_MAP_00000000T000000_99999999T999999_0002.DBL|
|1 |#4:1|#4:1 |CS_OPER_AUX_ODLE___00000000T000000_99999999T999999_0002.DBL|
+----+----+-----------------------+-----------------------------------------------------------+
Command executed in 1ms

select [@]rid, name from Product order by [@]rid DESC limit 2

+----+-----+-----------------------------------------------------------+
|# |[@]RID |name |
+----+-----+-----------------------------------------------------------+
|0 |#13:0|CS_OPER_AUX_SUNACT_19910101T000000_20220401T000000_0001.DBL|
|1 |#12:0|CS_OPER_SIR1LRM_0__20221213T012441_20221213T013153_0001.DBL|
+----+-----+-----------------------------------------------------------+
Command executed in 3ms

If the same steps are executed on Studio,

[lvca]

Reproduced, it's definitely a bug. I see there is a part in the SQL grammar that never supported ORDER BY @rid. Just checked the code for the OrientDB SQL layer and it's the same. I guess the developer started something but ended up using @rid as a record attribute instead of a proper identifier.

The reason why the internal alias is set in the result is that the engine doesn't recognize you used @rid in your projection, so it creates an additional property to index the RID.

Working out a solution and a clean of the grammar...

[lvca]

Fixed and added a test case to avoid regression. Thanks @vic0824!