Reports break when using "Not NULL or Blank" on an integer field

[Michael Peters]

Evergreen 2.1
PostgreSQL 9.1
Debian Squeeze

When attempting to use a 'Is Not NULL or Blank" filter on an integer field in the reports module, the system dies with an error:

DBD::Pg::st execute failed: ERROR: invalid input syntax for integer: "" LINE 17: ...NOT NULL AND "00f1e2d98c06dd5dd943d92cd460ea49"."id" <> '')) ^ at /openils/bin/clark-kent.pl line 217.

The solution, of course, is to only use the "Is not NULL" operator, however this may not be intuitive to novice users, so we should prevent using this filter on integer fields.

[Jason Stephenson (jstephenson)]

Requires confirmation by someone other than the reporter.

[Rogan Hamby (rogan-hamby)]

Using Evergreen 2.7.3 on Postgres 9.1 and I still get this. I did a very simple report to give me a count of patron IDs where claimed return count is not null or blank and got :

DBD::Pg::st execute failed: ERROR: invalid input syntax for integer: "" LINE 3: ...ca9ecd516f33ec379ecc11aa7051"."claims_returned_count" <> '') ^ at /opt/sequoia/apps/evergreen/bin/clark-kent.pl line 242.

 SELECT * FROM (SELECT COUNT("fe21ca9ecd516f33ec379ecc11aa7051"."id") AS "User ID"
  FROM actor.usr AS "fe21ca9ecd516f33ec379ecc11aa7051"
  WHERE ("fe21ca9ecd516f33ec379ecc11aa7051"."claims_returned_count" IS NOT NULL AND "fe21ca9ecd516f33ec379ecc11aa7051"."claims_returned_count" <> '')
  ORDER BY COUNT("fe21ca9ecd516f33ec379ecc11aa7051"."id") ASC
) limited_to_1048576_hits LIMIT 1048576
;

Is the SQL that's generated.

[Mike Rylander (mrylander)]

This can probably be addressed by restricting the "or blank" variant of the operator to string type columns. It doesn'make sense for numeric, Boolean, and date-time types. That can be done in the JS that defines the operators for various types, and I believe right now the operator in question is on the "all types" list.