Console does not show property column in SELECT result if returned property values are null

[vic0824]

ArcadeDB Version: v22.12.1

JDK Version: openjdk version "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

If a query against a type is executed, the Console should display a column for each property defined for that type, regardless of the value.

Actual behavior

If the query returns a set of records for which the values of a property are null, the Console only shows columns for which there is at least one property value which is not null.

Steps to reproduce

1. start ArcadeDB
2. start the Console and connect to the server
3. create a database
4. create a type Order
5. create the following properties (all of type String): processor, vstart, vstop, pstart, pstop, status, node
6. insert a record for which some of the properties are null
7. execute a select on the type and verify that it does not contain the columns corresponding to the properties that have a value = null

This is the result of my test (I have written [@]rid using square brackets to avoid mentioning the GitHub user called rid):
`> select from Order

Command executed in 2ms

insert into Order set processor = 'SIR1LRM-7.1', vstart = '20220319_002624.404379', vstop = '20220319_002826.525650', pstart = null, pstop = null, status = 'PENDING', node = null

DOCUMENT @type:Order [@]rid:#14:4
+---------+----------------------+
|NAME |VALUE |
+---------+----------------------+
|vstart |20220319_002624.404379|
|vstop |20220319_002826.525650|
|processor|SIR1LRM-7.1 |
|status |PENDING |
+---------+----------------------+
Command executed in 2ms

select from Order

DOCUMENT @type:Order [@]rid:#14:4
+---------+----------------------+
|NAME |VALUE |
+---------+----------------------+
|vstart |20220319_002624.404379|
|vstop |20220319_002826.525650|
|processor|SIR1LRM-7.1 |
|status |PENDING |
+---------+----------------------+
Command executed in 3ms

`

[lvca]

@vic0824 Thanks a lot for your very detailed reports. Having all this information in your issues helps A LOT.

This behavior is due to the fact that ArcadeDB has a very aggressive optimization with the space created from records by excluding the NULL property from the record before saving it. In fact, there is no difference between setting a NULL value or removing the property from the record. In both cases when you ask for the value the result is NULL.

[vic0824]

I understand the benefits of this design decision, but it has two inconvenient effects:

1. Neither Console nor Studio can distinguish between a String that is null and a String that is = "". In both cases, nothing is displayed in that cell
2. When transforming the select result into a JSON array that is sent to the client (browser), the null properties are omitted, and the Javascript interpreter in the browser assigns them a default value of "undefined", which is displayed to the user

As a workaround, in my http handlers I have added code that replaces every null reference with "" in the various getters used to build a JSON that is sent to the http client, but it's not ideal.

Maybe the code that returns the values could include the property with a null value even if it's not in the stored record, but I don't know how much work would that be, or if it would imply a significant refactoring.
Any other ideas?

[lvca]

If you explicitly ask for a property (column) in your SQL as a projection, then it should return the property as NULL. But if you ask for the whole record (select from, or select * from), then it's omitted.

I think it's useful to replace null with something like <null> on studio and console when null is returned.

[lvca]

Found the issue: it's in the JSONObject:

  public JSONObject put(String key, Object value) throws JSONException {
    if (key == null) {
      throw new NullPointerException("Null key.");
    } else {
      if (value != null) {
        testValidity(value);
        this.map.put(key, value);
      } else {
        this.remove(key);
      }

      return this;
    }
  }

I found on SO a trick: setting JSONObject.NULL constant when the value is null. I'm going to give a try asap.

[lvca]

Ok, now console and studio are consistent in displaying null values as <null>.

[vic0824]

Tested it with v23.1.1-SNAPSHOT (build 8c8df17/1672742152255/main)
It works as expected:

• "null values in Console" screenshot shows the difference between a select that does not specify any property and a select that specifies null properties
• "null values in Studio" screenshot shows the result of a query that specifies null properties

[lvca]

Great, thanks @vic0824 for confirming it.