[rt-users] bug in charting for Pg

[Ruslan Zakirov]

Hi,

It's been fixed in repository. Anyway, thanks for posting solution.

On Thu, Jul 15, 2010 at 12:11 AM, Kenneth Marshall <ktm at 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.