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

Loos_Christian · July 23, 2009, 2:55pm

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

error_message_details.txt (9.83 KB)

Ruslan_Zakirov · July 23, 2009, 9:49pm

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, ChristianCLoos@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: The rt-devel Archives

Best regards, Ruslan.

RT-3.8-explicit_alias_in_reports_by_dates.patch (1.07 KB)

Loos_Christian · July 24, 2009, 12:47pm

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

Ruslan_Zakirov · July 24, 2009, 9:28pm

It’s already in the 3.8’s branch for 3.8.5.On Fri, Jul 24, 2009 at 4:47 PM, Loos, ChristianCLoos@netcologne.de 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@gmail.com]
Gesendet: Donnerstag, 23. Juli 2009 23:49
An: Loos, Christian
Cc: rt-devel@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, ChristianCLoos@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:
The rt-devel Archives

–
Best regards, Ruslan.

Best regards, Ruslan.

shaker_Khzym · August 13, 2009, 5:36pm

Dear Sir,

  I am new to open source code world.
 1-   In  Ticket/display.html
             $m->callback (TicketObj =>$TicketObj, ARGSRef => \ARGS, CallbackName =>'Initial');
              my question , CallbackName -> 'Initial' , you call Initail function , or  what is it mean. I couldn't find Initail function. 

 2-    I am training to add customer text box in display ticket pag ,and join it with ledgersmb customer data, anyone has any information around this point.

  Please, advise me for my 2 questions.

Thanks a lot for your patience.
Best wishes,
Shaekir,

Stay in the loop and chat with friends, right from your inbox!

shaker_Khzym · August 13, 2009, 5:46pm

Dear Sir,
Sorry for my English spilling.

  I am new to open source code world.
 1-   In  Ticket/display.html
             $m->callback (TicketObj =>$TicketObj, ARGSRef => \ARGS, CallbackName =>'Initial');
              my question , CallbackName -> 'Initial' , you call Initail function , or  what is it mean. I couldn't find Initail function. 

 2-    my new task is adding  customer text box in display ticket pag ,and join it with ledgersmb customer data, anyone has any information around this point.

  Please, advise me for my 2 questions.

Thanks a lot for your patience.
Best wishes,
Shaekir,

Send and receive email from all of your webmail accounts.

ATT00000 (126 Bytes)

Alejandro_Imass · August 13, 2009, 6:18pm

Just add the field as a custom field and then do the interface with
ledgersmb via database. I take it you want to invoice in ledgersmb for
resolving ticket. Here is what I would do:

Add a normal custom field to RT
Create a custom class and assign it to the event in the particular
queue, or use the script facility as well
In the custom class/script, you will have access to the ticket
object and the extra field(s).
4a) Take that info and leave it in a transaction table for later
pickup by the ledgersmb interface, or
4b) Use DBI to create a record directly in the ledgersmb database, or
4c) post to the ledgersmb shell API

Start by reading:

http://rt.bestpractical.com/view/CleanlyCustomizeRT
http://rt.bestpractical.com/view/WriteCustomAction
http://rt.bestpractical.com/view/Documentation

Mentioning your interface with ledgersmb without any context was a bit
far-fetched and since you mention you are new to the FOSS world:

http://catb.org/~esr/faqs/smart-questions.html

So go RTFM

Best,
Alejandro ImassOn Thu, Aug 13, 2009 at 1:36 PM, shaker Khzymshakerramses@hotmail.com wrote:

Dear Sir,
  I am new to open source code world.
 1-   In  Ticket/display.html
             $m->callback (TicketObj =>$TicketObj, ARGSRef => \ARGS,
CallbackName =>‘Initial’);
my question , CallbackName → ‘Initial’ , you call Initail
function , or what is it mean. I couldn’t find Initail function.
 2-    I am training to add customer text box in display ticket pag ,and
join it with ledgersmb customer data, anyone has any information around this
point.
  Please, advise me for my 2 questions.
Thanks a lot for your patience.
Best wishes,
Shaekir,

Stay in the loop and chat with friends, right from your inbox! Learn how!

List info: The rt-devel Archives