From 57e2ea9f0b29841140d17149c0b479fd0f134812 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Fri, 12 May 2017 15:14:54 -0400 Subject: [PATCH] LP1690434: Date Comparisons in the Reporter Because of the way the where clause is constructed in queries using the Year + Month Transform reports using some types of operators could produce invalid SQL. 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). Signed-off-by: Jason Boyer Signed-off-by: Mike Rylander --- Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm index f3cef565d0..d98a04fa25 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Reporter/SQLBuilder.pm @@ -386,8 +386,8 @@ sub toSQL { my $val = $self->{params}; $val = $$val[0] if (ref($val)); - return "EXTRACT(YEAR FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)" . - " || \$_$$\$-\$_$$\$ || LPAD(EXTRACT(MONTH FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)::text,2,\$_$$\$0\$_$$\$)"; + return "(EXTRACT(YEAR FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)" . + " || \$_$$\$-\$_$$\$ || LPAD(EXTRACT(MONTH FROM \$_$$\$$rtime\$_$$\$::TIMESTAMPTZ + \$_$$\$$val months\$_$$\$)::text,2,\$_$$\$0\$_$$\$))"; } @@ -694,8 +694,8 @@ package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc; sub toSQL { my $self = shift; - return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' . - ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")::text,2,\'0\')'; + return '(EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' . + ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")::text,2,\'0\'))'; } sub is_aggregate { return 0 } @@ -728,8 +728,8 @@ package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter; sub toSQL { my $self = shift; - return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' . - ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")'; + return '(EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' . + ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '"))'; } sub is_aggregate { return 0 } -- 2.11.0