Reporter: Unable to use relative dates with Year + Month transform and On or After operator

[Jason Boyer]

Eg 2.12 and below

When building report templates if you select a date and apply the Year + Month transform (yyyy-mm) with the On or After (>=) operator, you can't use a relative month when scheduling the report. The generated SQL will contain a WHERE clause like this:

EXTRACT(YEAR FROM "table"."column") || '-' || LPAD(EXTRACT(MONTH FROM "table"."column")::text,2,'0') >=
EXTRACT(YEAR FROM 'now'::TIMESTAMPTZ + '-1 months') || '-' || LPAD(EXTRACT(MONTH FROM 'now'::TIMESTAMPTZ + '-1 months')::text,2,'0')

And will result in this type of (truncated) error message:

ERROR: operator does not exist: text >= double precision

The simplest query that demonstrates the issue is this:
evergreen@db1-prod:~# select '2001' || '-' || '01' >= '1999' || '-' || '12' as "test";
test
true-12
(1 row)

So the || and >= operators have the same priority and so are all executed in left to right order. (The test executed is '2001-01' >= '1999' which is t, then it's converted to text (true) and concatenated to '-' and '12')

Forcing the desired order of operations works as expected:
evergreen@db1-prod:~# select ('2001' || '-' || '01') >= ('1999' || '-' || '12') as "test";
test
t
(1 row)

(But why the complaint about text >= double precision above? double precision is the return type of EXTRACT().)

Branch to fix OpenILS::Reporter::SQLBuilder.pm incoming soon.

[Jason Boyer (jboyer)]

Branch lives at http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jboyer/LP1690434_reporter_dates

To test: put together a simple report template with a date in the Basic Filters section, using the Year + Month transform and the On or After operator. (a count of circs with an xact_start filter is a simple way to set this up.) Schedule it with a relative date such as 1 month ago and run it.

Pre Patch: the report will fail and the error text will reference a missing text >= double precision comparison function.

Post Patch: you'll get a count of how many circs were opened last month (or whatever report you wrote).

This patch also applies the same fix to the Quarter Transform.

[Mike Rylander (mrylander)]

Picked into 2.11, 2.12, and master, for great justice. Thanks, Jason!