Comment 0 for bug 1690434

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

Here are a few variations on the theme boiled down to their essence:

The initial construct:
evergreen@db1-prod:~# SELECT COUNT(id) FROM action.circulation AS circ
evergreen[-] # WHERE circ.circ_lib = 73 AND
evergreen[-] # EXTRACT(YEAR FROM "circ"."xact_start") || '-' || LPAD(EXTRACT(MONTH FROM "circ"."xact_start")::text,2,'0') >=
evergreen[-] # EXTRACT(YEAR FROM 'now'::TIMESTAMPTZ + '-6 months') || '-' || LPAD(EXTRACT(MONTH FROM 'now'::TIMESTAMPTZ + '-6 months')::text,2,'0');

ERROR: operator does not exist: text >= double precision
LINE 3: ...LPAD(EXTRACT(MONTH FROM "circ"."xact_start")::text,2,'0') >=
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

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)

evergreen@db1-prod:~# SELECT COUNT(id) FROM action.circulation AS circ
evergreen[-] # WHERE circ.circ_lib = 73 AND
evergreen[-] # (EXTRACT(YEAR FROM "circ"."xact_start") || '-' || LPAD(EXTRACT(MONTH FROM "circ"."xact_start")::text,2,'0')) >=
evergreen[-] # (EXTRACT(YEAR FROM 'now'::TIMESTAMPTZ + '-6 months') || '-' || LPAD(EXTRACT(MONTH FROM 'now'::TIMESTAMPTZ + '-6 months')::text,2,'0'));
count
4284
(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.