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

error_message_details.txt (9.83 KB)

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: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Best regards, Ruslan.

RT-3.8-explicit_alias_in_reports_by_dates.patch (1.07 KB)

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

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:
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel


Best regards, Ruslan.

Best regards, Ruslan.

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!

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)

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:

  1. Add a normal custom field to RT
  2. Create a custom class and assign it to the event in the particular
    queue, or use the script facility as well
  3. 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 :wink:

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: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel