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

[Ruslan Zakirov]

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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: RT-3.8-explicit_alias_in_reports_by_dates.patch
Type: application/octet-stream
Size: 1094 bytes
Desc: not available
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20090724/738dc0a4/attachment.obj