[rt-users] bug in charting for Pg

Kenneth Marshall ktm at rice.edu
Wed Jul 14 16:11:28 EDT 2010


I received the following error for plotting a search by
"CreatedMonthly":

ERROR:  column reference "created" is ambiguous at character 37
STATEMENT:  SELECT COUNT(main.id) AS id, SUBSTR(Created::text,1,7) AS createdmonthly FROM Tickets main CROSS JOIN Users Users_3 JOIN Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (Users_3.EmailAddress ILIKE '%ktm@%') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id IS NOT NULL ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  GROUP BY SUBSTR(Created::text,1,7) 

Here are the applicable lines from lib/RT/Report/Tickets.pm:

sub _FieldToFunction {
    my $self = shift;
    my %args = (@_);

    my $field = $args{'FIELD'};

    if ($field =~ /^(.*)(Daily|Monthly|Annually)$/) {
        my ($field, $grouping) = ($1, $2);
        # Pg 8.3 requires explicit casting
        $field .= '::text' if RT->Config->Get('DatabaseType') eq 'Pg';
        if ( $grouping =~ /Daily/ ) {
            $args{'FUNCTION'} = "SUBSTR($field,1,10)";
        }
        elsif ( $grouping =~ /Monthly/ ) {
            $args{'FUNCTION'} = "SUBSTR($field,1,7)";
        }
        elsif ( $grouping =~ /Annually/ ) {
            $args{'FUNCTION'} = "SUBSTR($field,1,4)";
        }
    } elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use CFDecipher method
...

I added a table specifier "main." to the field to fix the
problem. You may want to do something similar since it looks
like 3.8.8 has the same problem (we are running 3.8.5):

> diff -u lib/RT/Report/Tickets.pm local/lib/RT/Report/Tickets.pm 
--- lib/RT/Report/Tickets.pm    2009-10-13 12:24:43.000000000 -0500
+++ local/lib/RT/Report/Tickets.pm      2010-07-14 15:02:59.000000000 -0500
@@ -171,13 +171,13 @@
         # Pg 8.3 requires explicit casting
         $field .= '::text' if RT->Config->Get('DatabaseType') eq 'Pg';
         if ( $grouping =~ /Daily/ ) {
-            $args{'FUNCTION'} = "SUBSTR($field,1,10)";
+            $args{'FUNCTION'} = "SUBSTR(main.$field,1,10)";
         }
         elsif ( $grouping =~ /Monthly/ ) {
-            $args{'FUNCTION'} = "SUBSTR($field,1,7)";
+            $args{'FUNCTION'} = "SUBSTR(main.$field,1,7)";
         }
         elsif ( $grouping =~ /Annually/ ) {
-            $args{'FUNCTION'} = "SUBSTR($field,1,4)";
+            $args{'FUNCTION'} = "SUBSTR(main.$field,1,4)";
         }
     } elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use CFDecipher method
         my $cf_name = $1;

Regards,
Ken



More information about the rt-users mailing list