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

Bug #1690434 reported by Jason Boyer
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Fix Released

Bug Description

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";
(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";
(1 row)

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

Branch to fix incoming soon.

Jason Boyer (jboyer)
Changed in evergreen:
importance: Undecided → Medium
Revision history for this message
Jason Boyer (jboyer) wrote :

Branch lives at;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.

Jason Boyer (jboyer)
tags: added: pullrequest
Jason Boyer (jboyer)
description: updated
Revision history for this message
Mike Rylander (mrylander) wrote :

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

Changed in evergreen:
status: New → Fix Committed
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.