starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

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

New Issue
Closed
opened 2025-12-29 19:23:23 +01:00 by adam · 3 comments
adam commented 2025-12-29 19:23:23 +01:00
Owner
Copy Link

Originally created by @markkuleinio on GitHub (Jul 1, 2021).

Originally assigned to: @jeremystretch on GitHub.

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.

Originally created by @markkuleinio on GitHub (Jul 1, 2021). Originally assigned to: @jeremystretch on GitHub. ### 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 4. Create another prefix (like 10.1.1.0/24) 6. Create third prefix (like 10.1.2.0/24), with a date (like "2021-07-01") 8. In the prefixes list in GUI select the "Date testing" field as a column 9. See the prefixes with the "Date testing" values: the first two prefixes show "—" while the third prefix show the date "2021-07-01" 9. 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.
adam added the type: bugstatus: accepted labels 2025-12-29 19:23:23 +01:00
adam closed this issue 2025-12-29 19:23:23 +01:00
adam commented 2025-12-29 19:23:24 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jul 1, 2021):

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 commented on GitHub (Jul 1, 2021): 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: ```sql -- 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.
adam commented 2025-12-29 19:23:24 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Jul 1, 2021):

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 commented on GitHub (Jul 1, 2021): Ah, I just noticed something: In my raw SQL I was using the `->>` operator, whereas NetBox is using the `->` operator. These [work differently](https://www.postgresql.org/docs/10/functions-json.html). 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.
adam commented 2025-12-29 19:23:24 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Aug 16, 2021):

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.

@jeremystretch commented on GitHub (Aug 16, 2021): 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.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label status: accepted type: bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#5036
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?