[Rt-devel] [Bug] Error within Chart function when query for ticket with custom field and group by created or lastupdated

[Loos, Christian]

Hi Ruslan,

thanks for the quick response.
The patch is working!

I also tested the patch in RT 3.6.3 and 
there it also works.

Are you creating a ticket to integrate this bugfix 
for the next RT release?

Best regards
Chris  

> -----Ursprüngliche Nachricht-----
> Von: Ruslan Zakirov [mailto:ruslan.zakirov at gmail.com] 
> Gesendet: Donnerstag, 23. Juli 2009 23:49
> An: Loos, Christian
> Cc: rt-devel at lists.bestpractical.com
> Betreff: Re: [Rt-devel] [Bug] Error within Chart function 
> when query for ticket with custom field and group by created 
> or lastupdated
> 
> Hello Chris,
> 
> Try attached patch and report back. Don't forget to stop/start server
> after attaching.
> 
> On Thu, Jul 23, 2009 at 6:55 PM, Loos, 
> Christian<CLoos at netcologne.de> wrote:
> > Hi there,
> >
> > I discovered an bug within the chart function.
> >
> > Here are the steps to reproduce:
> > - create custom field 'foo', type 'Enter one value' and 
> assign it to queue 'General'
> > - create a ticket in queue 'General' and enter for custom 
> field 'foo' value 'bar'
> > - create query: Queue = 'General' AND 'CF.{foo}' LIKE 'bar'
> > - on the Show Results page chose at 'chart by' one of the 
> folowing values:
> > * Created{Daily|Monthly|Annually}
> > * LastUpdated{Daily|Monthly|Annually}
> >
> > Attached is the full error message.
> > The first line explains it:
> > DBD::mysql::st execute failed: Column 'Created' in field 
> list is ambiguous
> >
> > Here is the sql statement:
> > SELECT DISTINCT COUNT( main.id ) AS id
> > , SUBSTR( Created, 1, 10 )       AS createddaily
> > FROM Tickets main
> > JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1
> > ON( ObjectCustomFieldValues_1.CustomField = '1' )
> > AND( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' )
> > AND( ObjectCustomFieldValues_1.Disabled = '0' )
> > AND( ObjectCustomFieldValues_1.ObjectId = main.id )
> > WHERE( main.Status != 'deleted' )
> > AND( main.Queue = '1'
> > AND((( ObjectCustomFieldValues_1.Content LIKE '%bar%'
> > OR(( ObjectCustomFieldValues_1.Content = ''
> > OR ObjectCustomFieldValues_1.Content IS NULL )
> > AND ObjectCustomFieldValues_1.LargeContent LIKE '%bar%' ) ) ) ) )
> > AND( main.Type = 'ticket' )
> > AND( main.EffectiveId = main.id )
> > GROUP BY SUBSTR( Created, 1, 10 )
> >
> > Within the select and group by expression the table alias 'main' for
> > the column 'Created' is missing.
> > This is explicit needed because the 'ObjectCustomFieldValues' table
> > have also an 'Created' column.
> >
> > The curious thing is, that this error only appears when you group by
> > the Created or LastUpdated field. If you use one of the 
> other date fields
> > everything is ok.
> >
> > I suppose the DBIx::SearchBuilder is responsible for the error.
> > I am not familiar with this one so maybe someone can help me
> > because i have to fix this problem very shortly.
> >
> > My system:
> > RT 3.8.4
> > Perl v5.10.0
> > DBIx::SearchBuilder v1.56
> > MySQL 5.0.51a-24+lenny1
> >
> > Thanks!
> >
> > Chris
> > _______________________________________________
> > List info: 
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
> >
> >
> 
> 
> 
> -- 
> Best regards, Ruslan.
>