Wishlist: Add BETWEEN SYMMETRIC feature to reporter

[Chris Sharp]

One of the most common reports mistakes is that the end user will unwittingly enter the higher value first in a "between" filter, which results in an empty result set, since PostgreSQL accepts those values literally (example, there will be no circulations with a circ date between '2019-10-01' and '2019-09-01'). PostgreSQL offers BETWEEN SYMMETRIC as a solution to this. From the docs:

"BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that the argument to the left of AND be less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied."

(from https://www.postgresql.org/docs/9.6/functions-comparison.html)

I suggest we alter the SQL builder code to make any "between" or "not between" become "BETWEEN SYMMETRIC" or "NOT BETWEEN SYMMETRIC" in the resulting query. I'm unable to think of a use case where any end user of the Evergreen reporter would want it any other way.

[Galen Charlton (gmc)]

Noting that BETWEEN SYMMETRIC is available in all Pg versions required by supported versions of Evergreen. I would not be averse to classifying this as a backport-able usability bug.

[Chris Sharp (chrissharp123)]

Branch available for testing:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/csharp/lp1846484_add_between_symmetric_to_reporter

[Jason Stephenson (jstephenson)]

I changed the milestone for this bug to 3.5-alpha because it is classified as Wishlist. I don't think wishlist bugs should be backported.

[Rogan Hamby (rogan-hamby)]

I'm all for quality of life improvements, especially if they prevent confusion/help desk tickets. Works as advertised. Sign off at user/rogan/lp1846484_symmetric

[Bill Erickson (berick)]

Thanks all. Merged to master.

Note I would also be OK with treating this as a bug instead of a wishlist.