On Thu, Mar 12, 2009 at 11:00:21AM +0000, Kristian Davies wrote: > Postgres: 8.3.6 > RT 3.8.2 > CentOS 4 > > My graphing isn't working under Tools>Reports. > > Any ideas? > > Cheers, > Kristian > > > /var/log/message > > Mar 12 10:50:27 ghole postgres[11326]: [2-1] ERROR: function > lower(timestamp without time zone) does not exist at character 315 > Mar 12 10:50:27 ghole postgres[11326]: [2-2] HINT: No function > matches the given name and argument types. You might need to add > explicit type casts. > Mar 12 10:50:27 ghole postgres[11326]: [2-3] STATEMENT: SELECT > COUNT(main.id) AS id, Users_2.Name AS col1 FROM ( SELECT DISTINCT > main.id FROM Tickets main LEFT JOIN Users > Mar 12 10:50:27 ghole postgres[11326]: [2-4] Users_2 ON ( Users_2.id > = main.Owner ) LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) > WHERE (LOWER(main.Status) != > Mar 12 10:50:27 ghole postgres[11326]: [2-5] 'deleted') AND > (LOWER(main.Status) = 'resolved' AND main.Queue = '1' AND > LOWER(main.Resolved) > '2009-01-01 00:00:00') AND > Mar 12 10:50:27 ghole postgres[11326]: [2-6] (LOWER(main.Type) = > 'ticket') AND (main.EffectiveId = main.id) ) distinctquery, Tickets > main WHERE (main.id = > Mar 12 10:50:27 ghole postgres[11326]: [2-7] distinctquery.id) GROUP > BY Users_1.Name > Mar 12 10:50:28 ghole postgres[11327]: [2-1] ERROR: function > lower(timestamp without time zone) does not exist at character 265 > Mar 12 10:50:28 ghole postgres[11327]: [2-2] HINT: No function > matches the given name and argument types. You might need to add > explicit type casts. > Mar 12 10:50:28 ghole postgres[11327]: [2-3] STATEMENT: SELECT > COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Users Users_2 ON > ( Users_2.id = main.Owner ) LEFT JOIN > Mar 12 10:50:28 ghole postgres[11327]: [2-4] Users Users_1 ON ( > Users_1.id = main.Owner ) WHERE (LOWER(main.Status) != 'deleted') AND > (LOWER(main.Status) = 'resolved' AND > Mar 12 10:50:28 ghole postgres[11327]: [2-5] main.Queue = '1' AND > LOWER(main.Resolved) > '2009-01-01 00:00:00') AND (LOWER(main.Type) = > 'ticket') AND (main.EffectiveId = > Mar 12 10:50:28 ghole postgres[11327]: [2-6] main.id) > > /rt.log > > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. at > /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505. > (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505) > [Thu Mar 12 10:50:27 2009] [warning]: RT::Handle=HASH(0xaa0d998) > couldn't execute the query 'SELECT COUNT(main.id) AS id, Users_2.Name > AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT JOIN > Users Users_2 ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1 > ON ( Users_1.id = main.Owner ) WHERE (LOWER(main.Status) != > 'deleted') AND (LOWER(main.Status) = 'resolved' AND main.Queue = '1' > AND LOWER(main.Resolved) > '2009-01-01 00:00:00') AND > (LOWER(main.Type) = 'ticket') AND (main.EffectiveId = main.id) ) > distinctquery, Tickets main WHERE (main.id = distinctquery.id) GROUP > BY Users_1.Name ' at > /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 518 > DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0xaa0d998)', > 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM ( SELE...') > called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line > 238 > DBIx::SearchBuilder::_DoSearch('RT::Report::Tickets=HASH(0xb166a58)') > called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm > line 2672 > RT::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xb166a58)') > called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Report/Tickets.pm > line 152 > RT::Report::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xb166a58)') > called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line > 498 > DBIx::SearchBuilder::Next('RT::Report::Tickets=HASH(0xb166a58)') > called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Report/Tickets.pm > line 236 > RT::Report::Tickets::Next('RT::Report::Tickets=HASH(0xb166a58)') > called at /disk1/web/vhost/rt.local/rt/share/html/Search/Elements/Chart > line 71 > HTML::Mason::Commands::__ANON__('Query', 'Status = > \'resolved\' AND Queue = \'Systems\' AND Resolved > ...', > 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135 > > HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xb169ca0)', > 'Query', 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > > ...', 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1278 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268 > HTML::Mason::Request::comp('undef', 'undef', 'Query', 'Status > = \'resolved\' AND Queue = \'Systems\' AND Resolved > ...', > 'PrimaryGroupBy', 'Owner') called at > /disk1/web/vhost/rt.local/rt/share/html/Tools/Reports/ResolvedByDates.html > line 83 > HTML::Mason::Commands::__ANON__('ResolvedBefore', '', > 'ResolvedAfter', '01/01/2009', 'Queue', 'Systems') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135 > > HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xaf6cc00)', > 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue', > 'Systems') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1278 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268 > HTML::Mason::Request::comp('undef', 'undef', 'undef', > 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue', > 'Systems', ...) called at > /disk1/web/vhost/rt.local/rt/local/html/autohandler line 311 > HTML::Mason::Commands::__ANON__('ResolvedBefore', '', > 'ResolvedAfter', '01/01/2009', 'Queue', 'Systems') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135 > > HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xaf70ec4)', > 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue', > 'Systems') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1273 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268 > HTML::Mason::Request::comp('undef', 'undef', 'undef', > 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue', > 'Systems', ...) called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 419 > > HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0xad7ed30)') > called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm > line 168 > > HTML::Mason::Request::ApacheHandler::exec('RT::Interface::Web::Request=HASH(0xad7ed30)') > called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm > line 825 > > HTML::Mason::ApacheHandler::handle_request('HTML::Mason::ApacheHandler=HASH(0x9078598)', > 'Apache2::RequestRec=SCALAR(0xa9c66f0)') called at > /disk1/web/vhost/rt.local/rt/bin/webmux.pl line 149 > eval {...} called at /disk1/web/vhost/rt.local/rt/bin/webmux.pl line > 149 > RT::Mason::handler('Apache2::RequestRec=SCALAR(0xa9c66f0)') > called at -e line 0 > eval {...} called at -e line 0 (/usr/lib/perl5/5.8.8/Carp.pm:272) > [Thu Mar 12 10:50:28 2009] [debug]: RT's GnuPG libraries couldn't > successfully read your configured GnuPG home directory > (/disk1/web/vhost/rt.local/rt/var/data/gpg). PGP support has been > disabled (/disk1/web/vhost/rt.local/rt/bin/../local/lib/RT/Config.pm:339) > [Thu Mar 12 10:50:28 2009] [debug]: RT::Date used Time::ParseDate to > make '2009-01-01 00:00:00' 1230768000 > (/disk1/web/vhost/rt.local/rt/bin/../lib/RT/Date.pm:209) > [Thu Mar 12 10:50:28 2009] [warning]: DBD::Pg::st execute failed: > ERROR: function lower(timestamp without time zone) does not exist > LINE 1: ...ain.Status) = 'resolved' AND main.Queue = '1' AND LOWER(main... > ^ > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. at > /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505. > (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505) > [Thu Mar 12 10:50:28 2009] [warning]: RT::Handle=HASH(0xaa0c4d0) > couldn't execute the query 'SELECT COUNT(DISTINCT main.id) FROM > Tickets main LEFT JOIN Users Users_2 ON ( Users_2.id = main.Owner ) > LEFT JOIN Users Users_1 ON ( Users_1.id = main.Owner ) WHERE > (LOWER(main.Status) != 'deleted') AND (LOWER(main.Status) = 'resolved' > AND main.Queue = '1' AND LOWER(main.Resolved) > '2009-01-01 00:00:00') > AND (LOWER(main.Type) = 'ticket') AND (main.EffectiveId = main.id) ' > at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line > 518 > DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0xaa0c4d0)', > 'SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Us...') > called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line > 294 > DBIx::SearchBuilder::_DoCount('RT::Report::Tickets=HASH(0xb0db1dc)') > called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm > line 2678 > RT::Tickets::_DoCount('RT::Report::Tickets=HASH(0xb0db1dc)') > called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line > 1380 > DBIx::SearchBuilder::Count('RT::Report::Tickets=HASH(0xb0db1dc)') > called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm > line 2597 > RT::Tickets::Count('RT::Report::Tickets=HASH(0xb0db1dc)') > called at /disk1/web/vhost/rt.local/rt/share/html/Search/Chart line 93 > HTML::Mason::Commands::__ANON__('Query', 'Status = > \'resolved\' AND Queue = \'Systems\' AND Resolved > ...', > 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135 > > HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xafb1350)', > 'Query', 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > > ...', 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1278 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268 > HTML::Mason::Request::comp('undef', 'undef', 'undef', 'Query', > 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > ...', > 'PrimaryGroupBy', 'Owner') called at > /disk1/web/vhost/rt.local/rt/local/html/autohandler line 311 > HTML::Mason::Commands::__ANON__('Query', 'Status = > \'resolved\' AND Queue = \'Systems\' AND Resolved > ...', > 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135 > > HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xafd2ef4)', > 'Query', 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > > ...', 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1273 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268 > HTML::Mason::Request::comp('undef', 'undef', 'undef', 'Query', > 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > ...', > 'PrimaryGroupBy', 'Owner') called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467 > eval {...} called at > /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 419 > > HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0xaf4d518)') > called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm > line 168 > > HTML::Mason::Request::ApacheHandler::exec('RT::Interface::Web::Request=HASH(0xaf4d518)') > called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm > line 825 > > HTML::Mason::ApacheHandler::handle_request('HTML::Mason::ApacheHandler=HASH(0x9078598)', > 'Apache2::RequestRec=SCALAR(0xa9c66d8)') called at > /disk1/web/vhost/rt.local/rt/bin/webmux.pl line 149 > eval {...} called at /disk1/web/vhost/rt.local/rt/bin/webmux.pl line > 149 > RT::Mason::handler('Apache2::RequestRec=SCALAR(0xa9c66d8)') > called at -e line 0 > eval {...} called at -e line 0 (/usr/lib/perl5/5.8.8/Carp.pm:272) > _______________________________________________
Kristian, I reported this bug and it has been fixed in CVS and should appear in 3.8.3. Here is a diff of my patched version of lib/RT/Report/Tickets.pm. Make a copy of that file to local/lib/RT/Report/Tickets.pm and apply this patch. Cheers, Ken --- lib/RT/Report/Tickets.pm 2009-01-21 13:12:22.000000000 -0600 +++ local/lib/RT/Report/Tickets.pm 2009-02-03 11:06:35.000000000 -0600 @@ -169,13 +169,16 @@ if ($field =~ /^(.*)(Daily|Monthly|Annually)$/) { my ($field, $grouping) = ($1, $2); if ( $grouping =~ /Daily/ ) { - $args{'FUNCTION'} = "SUBSTR($field,1,10)"; +# $args{'FUNCTION'} = "SUBSTR($field,1,10)"; + $args{'FUNCTION'} = "SUBSTR(${field}::text,1,10)"; } elsif ( $grouping =~ /Monthly/ ) { - $args{'FUNCTION'} = "SUBSTR($field,1,7)"; +# $args{'FUNCTION'} = "SUBSTR($field,1,7)"; + $args{'FUNCTION'} = "SUBSTR(${field}::text,1,7)"; } elsif ( $grouping =~ /Annually/ ) { - $args{'FUNCTION'} = "SUBSTR($field,1,4)"; +# $args{'FUNCTION'} = "SUBSTR($field,1,4)"; + $args{'FUNCTION'} = "SUBSTR(${field}::text,1,4)"; } } elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use CFDecipher method my $cf_name = $1; @@ -190,24 +193,25 @@ } elsif ( $field =~ /^(?:(Owner|Creator|LastUpdatedBy))(?:\.(.*))?$/ ) { my $type = $1 || ''; my $column = $2 || 'Name'; - my $u_alias = $self->Join( - TYPE => 'LEFT', - ALIAS1 => 'main', - FIELD1 => $type, - TABLE2 => 'Users', - FIELD2 => 'id', - ); + my $u_alias = $self->{"_sql_report_${type}_users_${column}"} + ||= $self->Join( + TYPE => 'LEFT', + ALIAS1 => 'main', + FIELD1 => $type, + TABLE2 => 'Users', + FIELD2 => 'id', + ); @args{qw(ALIAS FIELD)} = ($u_alias, $column); } elsif ( $field =~ /^(?:Watcher|(Requestor|Cc|AdminCc))(?:\.(.*))?$/ ) { my $type = $1 || ''; my $column = $2 || 'Name'; - if ( my $u_alias = $self->{"_sql_report_watcher_users_alias_$type"} ) { - @args{qw(ALIAS FIELD)} = ($u_alias, $column); - } else { - my ($g_alias, $gm_alias, $u_alias) = $self->_WatcherJoin( $type ); - @args{qw(ALIAS FIELD)} = ($u_alias, $column); + my $u_alias = $self->{"_sql_report_watcher_users_alias_$type"}; + unless ( $u_alias ) { + my ($g_alias, $gm_alias); + ($g_alias, $gm_alias, $u_alias) = $self->_WatcherJoin( $type ); $self->{"_sql_report_watcher_users_alias_$type"} = $u_alias; } + @args{qw(ALIAS FIELD)} = ($u_alias, $column); } return %args; } _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com