Null date in custom field is sorted differently to empty data value

[markkuleinio]

NetBox version

v2.11.7

Python version

3.7

Steps to Reproduce

1. Create a prefix (like 10.1.0.0/24)
2. Create new custom field "date_test", type Date, assign it to prefixes
3. Create another prefix (like 10.1.1.0/24)
4. Create third prefix (like 10.1.2.0/24), with a date (like "2021-07-01")
5. In the prefixes list in GUI select the "Date testing" field as a column
6. See the prefixes with the "Date testing" values: the first two prefixes show "—" while the third prefix show the date "2021-07-01"
7. Sort by the "Date testing" column by clicking the column header "Date testing"

Expected Behavior

It is expected that the prefix list is sorted so that the prefix with the date (10.1.2.0/24) would be at the bottom of the list (assuming that empty date is "smaller" than any date), i.e. the order would be

• 10.1.0.0/24: —
• 10.1.1.0/24: —
• 10.1.2.0/24: 2021-07-01

Observed Behavior

The prefixes are ordered like this:

• 10.1.1.0/24: —
• 10.1.2.0/24: 2021-07-01
• 10.1.0.0/24: —

In the database the prefixes have the following custom_field_data values:

• 10.1.0.0/24: {}
• 10.1.1.0/24: {"date_test": null}
• 10.1.2.0/24: {"date_test": "2021-07-01"}

So it looks like the null value is treated differently to the missing custom field value.

Obviously this only affects installations where the custom field is added after populating the data.

Workaround is to populate null values manually in the database, using the jsonb operators in PostgreSQL I believe.

[jeremystretch]

I think there are two separate issues here. First, there's the distinction between date_test not being set and date_test being set to null. I don't think this is really a problem though, because:

-- Ascending
netbox=> SELECT DISTINCT id, prefix, custom_field_data, (custom_field_data->>'date_test') AS date_test from ipam_prefix where vrf_id=1 order by DATE_TEST ASC;
  id  |   prefix    |      custom_field_data      | date_test  
------+-------------+-----------------------------+------------
 5395 | 10.1.2.0/24 | {"date_test": "2021-07-01"} | 2021-07-01
 5396 | 10.1.3.0/24 | {"date_test": "2021-07-02"} | 2021-07-02
 5393 | 10.1.0.0/24 | {}                          | 
 5394 | 10.1.1.0/24 | {"date_test": null}         | 
(4 rows)

-- Descending
netbox=> SELECT DISTINCT id, prefix, custom_field_data, (custom_field_data->>'date_test') AS date_test from ipam_prefix where vrf_id=1 order by DATE_TEST DESC;
  id  |   prefix    |      custom_field_data      | date_test  
------+-------------+-----------------------------+------------
 5393 | 10.1.0.0/24 | {}                          | 
 5394 | 10.1.1.0/24 | {"date_test": null}         | 
 5396 | 10.1.3.0/24 | {"date_test": "2021-07-02"} | 2021-07-02
 5395 | 10.1.2.0/24 | {"date_test": "2021-07-01"} | 2021-07-01
(4 rows)

PostgreSQL seems to order them appropriately. So the raw SQL queries work as expected.

What I haven't figured out yet is why we're not seeing the same behavior in the UI.

[jeremystretch]

Ah, I just noticed something: In my raw SQL I was using the ->> operator, whereas NetBox is using the -> operator. These work differently. So in its current form, NetBox is getting null for the first prefix and {"date_test": null} for the second.

Need to do some more digging but this seems to be the root issue.

[jeremystretch]

Seems like the best solution here is to simply force the assignment of null custom field values to each instance. This will ensure consistent ordering, since we'll no longer have to account for undefined keys within the custom field data.