Mailing List Archive: Request Tracker: Devel

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

Index | Next | Previous | View Threaded

CLoos at netcologne

Jul 23, 2009, 7:55 AM

Post #1 of 4 (1170 views)
Permalink

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

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

Attachments:

error_message_details.txt (9.83 KB)

ruslan.zakirov at gmail

Jul 23, 2009, 2:49 PM

Post #2 of 4 (1083 views)
Permalink

Re: [Bug] Error within Chart function when query for ticket with custom field and group by created or lastupdated [In reply to]

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> 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.

Attachments:

RT-3.8-explicit_alias_in_reports_by_dates.patch (1.07 KB)

CLoos at netcologne

Jul 24, 2009, 5:47 AM

Post #3 of 4 (1072 views)
Permalink

Re: [Bug] Error within Chart function when query for ticket with custom field and group by created or lastupdated [In reply to]

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]
> Gesendet: Donnerstag, 23. Juli 2009 23:49
> An: Loos, Christian
> Cc: rt-devel [at] lists
> 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> 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.
>
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

ruslan.zakirov at gmail

Jul 24, 2009, 2:28 PM

Post #4 of 4 (1061 views)
Permalink

Re: [Bug] Error within Chart function when query for ticket with custom field and group by created or lastupdated [In reply to]

It's already in the 3.8's branch for 3.8.5.

On Fri, Jul 24, 2009 at 4:47 PM, Loos, Christian<CLoos [at] netcologne> wrote:
> 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]
>> Gesendet: Donnerstag, 23. Juli 2009 23:49
>> An: Loos, Christian
>> Cc: rt-devel [at] lists
>> 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> 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.
>>

--
Best regards, Ruslan.
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads