Bug in charting for Pg

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

Hi,

It’s been fixed in repository. Anyway, thanks for posting solution.On Thu, Jul 15, 2010 at 12:11 AM, Kenneth Marshall ktm@rice.edu wrote:

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

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.