reporter template can misinterpet expected input for an aggregate filter

[Galen Charlton]

Consider a report that has a filter with the following properties:

- operator is greater than or the like
- field transform is "count"

The generated SQL produces a HAVING clause that looks something like:

  HAVING ((COUNT("f4ec076b93f2f159d6941c11b83555ff"."id")) IS NULL OR COUNT("f4ec076b93f2f159d6941c11b83555ff"."id") > $_20322$XXX$_20322$)...

In context, the value of XXX should just be a number. However, the interface for creating a report has two problems:

- if a filter value is set in the template, that filter value is ignored
- a selection widget is constructed based on the class and field; the only appropriate filter value for a HAVING COUNT(*) is just a plain number

The expected result: such an aggregate filter should only result in a numeric input in the report editor (if no filter value is set) or use the value set in the template.

Evergreen 3.2+

[Galen Charlton (gmc)]

Example of a report template that exercises the bug

[Mike Rylander (mrylander)]

The problem here is that there's logic to override the filter widget for all columns that have a selector defined, such as the ID column of many tables. When we transform the column with an aggregate transform, though, we don't want to pick a particular selector-labeled value.

While I can imagine using a min or max aggregate to get a representative value for a group, when other dependent filters would make the value stable or where the value in particular doesn't matter but isn't needed in a count (say), all the other aggregate transforms that are currently supported are specifically numeric (sum, average, count, and count_distinct). Because of that, and the most likely use case of matching a count to "0" or "1", we should only present the "remote object widget" for non-aggregates, and just use the baseline text string input here.

Branch forthcoming.

[Mike Rylander (mrylander)]

Branch available at:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/lp-1858114-report-agg-param-input

[Mike Rylander (mrylander)]

I've updated the above branch with another commit to allow the "having" clause to actually save and use template-level filter values provided by template creators. Previous to that commit, aggregate columns required report-level filter parameters in all cases.

[Mike Rylander (mrylander)]

And, finally, I've pushed one more commit to that branch which fixes a thinko from [checks notes] 2007 that prevented template filter values on aggregate filter columns ("having" clause) from showing up in the filter area.

Here's to hoping that's the last time we have to touch the bespoke-OO-JS reports UI!

[Angela Kilsdonk (akilsdonk)]

I have tested this code and consent to signing off on it with my name, Angela Kilsdonk, and my email address, akilsdonk[at]equinoxinitiative.org.

[Jason Boyer (jboyer)]

Pushed to master, rel_3_6, and rel_3_5. Thanks Mike and Angela!