Report filters - unhandled SQL error when filtering IP address

[pstolpe]

In NAV version 5.6.1, trying to filter a https://navserver/report/netbox report with IP address generates following error:

There was an unhandled SQL error! There may be something wrong with the definition of the 'Devices in Operation' report: operator does not exist: inet ~~* unknown LINE 1: ... AND var='function')) AS foo WHERE ip ilike '172... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[lunkwill42]

That is true. The so-called "advanced" search form isn't very advanced 😄 It doesn't know what data types the fields are, so it presents the same set of matching operators for all fields, not knowing that ILIKE matches won't work on IP address fields.

I might suggest two alternate ways to approach a solution, @pstolpe:

1. When the search form is generated, the field type should already be known from the database results, so the operators that don't work for IP fields could be removed from the dropdown list.

2. ILIKE is only valid for text data, so the SQL generator could simply generate SQL to cast any field to TEXT when the ILIKE operator is applied. A downside to this might be that most specialized database indexes cannot be utilized by PostgreSQL in this case, so if you have large amounts of IP address rows to filter, things are going to slow down considerably (not usually a big problem for the netbox report, though).