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

[Loos, Christian]

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
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: error_message_details.txt
Url: http://lists.bestpractical.com/pipermail/rt-devel/attachments/20090723/7b2739e9/attachment.txt