| 2017-06-06 11:56:34 |
Jason Boyer |
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
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. |
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. |
|
