Chart grouped by customfield

Emmanuel_Lacour · October 10, 2007, 7:43am

Dear RT users/developpers

I’m trying to draw charts grouped by customfield (using the
functionnality at the bottom of a search result) and on a customfiled of
type “select unique value”. But the result graph is empty
It works with other group by like status, queue, …

If someone has some hints on solving this … thank you!

Emmanuel_Lacour · October 10, 2007, 7:46am

Dear RT users/developpers

I’m trying to draw charts grouped by customfield (using the
functionnality at the bottom of a search result) and on a customfiled of
type “select unique value”. But the result graph is empty
It works with other group by like status, queue, …

If someone has some hints on solving this … thank you!

I forgot … I’m using the latest RT 3.6.5 on RH CentOS 5 with Oracle XE
10.

Jesse_Vincent · October 10, 2007, 1:09pm

what shows up in your error logs?------- Original message -------
From: Emmanuel Lacour elacour@easter-eggs.com
Sent: 10-10-'07, 3:43

Dear RT users/developpers

I’m trying to draw charts grouped by customfield (using the
functionnality at the bottom of a search result) and on a customfiled of
type “select unique value”. But the result graph is empty
It works with other group by like status, queue, …

If someone has some hints on solving this … thank you!

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Emmanuel_Lacour · October 10, 2007, 1:38pm

Hi, dear RT primary Author

what shows up in your error logs?

That’s the good question … sorry I forgot to look here

There is DBD prepare errors:

RT: RT::Handle=HASH(0x8134df1c) couldn’t prepare the query 'SELECT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT 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.Status != ‘resolved’) AND (main.Type = ‘ticket’) AND (main.EffectiveId = main.id) ) distinctquery, Tickets main WHERE (main.id = distinctquery.id) GROUP BY ObjectCustomFieldValues_1.Content

'ORA-00904: “OBJECTCUSTOMFIELDVALUES_1”.“CONTENT”: invalid identifier
(DBD ERROR: error possibly near <*> indicator at char 596 in 'SELECT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1

Here is more information on my setup:

DBIx-SearchBuilder-1.49
DBD-Oracle-1.19
Oracle Xe 10.2.0.1
Perl 5.8.8

Thanks for your help

Jesse_Vincent · October 10, 2007, 1:42pm

Hi, dear RT primary Author

what shows up in your error logs?

That’s the good question … sorry I forgot to look here

I suspect that it has to do with “this was tested on mysql and pg”

Any oracle folks see what we’re doing wrong with that SQL?

PGP.sig (186 Bytes)

Emmanuel_Lacour · October 10, 2007, 2:03pm

I suspect that it has to do with “this was tested on mysql and pg”

if only I had power to choose the database Engine …

Gianluca_Cecchi · October 10, 2007, 3:40pm

distinctquery, Tickets main WHERE (main.id = distinctquery.id)

Shouldn’t be present a comma between Tickets and main?

Gianluca_Cecchi · October 10, 2007, 3:46pm

Sorry main was an alias for Tickets table…

If I use the same query on mysql 9without the comma…) I get:

ERROR 1054 (42S22): Unknown column ‘ObjectCustomFieldValues_1.Content’ in ‘field list’

And the oracle error 904 is “invalid column name” too

Emmanuel_Lacour · October 10, 2007, 3:58pm

Sorry main was an alias for Tickets table…

If I use the same query on mysql 9without the comma…) I get:

ERROR 1054 (42S22): Unknown column ‘ObjectCustomFieldValues_1.Content’ in ‘field list’

And the oracle error 904 is “invalid column name” too

Here is the SQL for the same Graph on a MySQL RT, gived by StatementLog (graph by CustomFields works with MySQL):

SELECT DISTINCT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM (Tickets main LEFT 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.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ((main.Created < ‘2007-10-31 23:00:00’) AND (main.Created > ‘2007-09-29 22:00:00’)) GROUP BY ObjectCustomFieldValues_1.Content ;

and the one for the Oracle RT:

SELECT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT 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.Status != ‘resolved’) AND (main.Type = ‘ticket’) AND (main.EffectiveId = main.id) ) distinctquery, Tickets main WHERE (main.id = distinctquery.id) GROUP BY ObjectCustomFieldValues_1.Content
;

Joop · October 10, 2007, 5:40pm

Jesse Vincent wrote:

I suspect that it has to do with “this was tested on mysql and pg”
Any oracle folks see what we’re doing wrong with that SQL?

There is DBD prepare errors:

RT: RT::Handle=HASH(0x8134df1c) couldn’t prepare the query 'SELECT
COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM (
SELECT DISTINCT main.id FROM Tickets main LEFT 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.Status != ‘resolved’) AND (main.Type = ‘ticket’)
AND (main.EffectiveId = main.id) ) distinctquery, Tickets main WHERE
(main.id = distinctquery.id) GROUP BY ObjectCustomFieldValues_1.Content

Problem is that in the group by there is a alias which is defined in the
subquery.
ObjectCustomFieldValues_1 is only known in the innermost query not the
outer one.
There is another problem aswell since modifing the query gives me
another unidentified error.
I’ll see if my DBA can have a look at it.

Joop

Joop · October 10, 2007, 5:48pm

Emmanuel Lacour wrote:> On Wed, Oct 10, 2007 at 05:46:31PM +0200, Gianluca Cecchi wrote:

Sorry main was an alias for Tickets table…

If I use the same query on mysql 9without the comma…) I get:

ERROR 1054 (42S22): Unknown column ‘ObjectCustomFieldValues_1.Content’ in ‘field list’

And the oracle error 904 is “invalid column name” too

Here is the SQL for the same Graph on a MySQL RT, gived by StatementLog (graph by CustomFields works with MySQL):

SELECT DISTINCT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM (Tickets main LEFT 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.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ((main.Created < ‘2007-10-31 23:00:00’) AND (main.Created > ‘2007-09-29 22:00:00’)) GROUP BY ObjectCustomFieldValues_1.Content ;
Weird, this one works without modification on Oracle XE.

Jesse_Vincent · October 10, 2007, 8:25pm

I suspect that it has to do with “this was tested on mysql and pg”

if only I had power to choose the database Engine …

It should work on everything. The fact that it doesn’t is definitely
a bug.

PGP.sig (186 Bytes)

Kenn_Crocker · October 10, 2007, 10:06pm

To all,

We are on Oracle 10g and we have the same problems with custom fields

when it comes to charts from query results or sorting by them as well.

Kenn
LBNLOn 10/10/2007 1:25 PM, Jesse Vincent wrote:

On Oct 10, 2007, at 10:03 AM, Emmanuel Lacour wrote:

On Wed, Oct 10, 2007 at 09:42:21AM -0400, Jesse Vincent wrote:

I suspect that it has to do with “this was tested on mysql and pg”

if only I had power to choose the database Engine …

It should work on everything. The fact that it doesn’t is definitely a bug.

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Ruslan_Zakirov1 · October 11, 2007, 2:44am

It’s an experimental and untested patch that may help.

When a query has a “group by” part we really don’t want to make it
distinct as I don’t see any way it can have not unique entries in the
result set.

Patch is for DBIx::SearchBuilder, to try it use the following steps:

untar DBIx::SB’s tarball
apply patch
install it
stop/start server
test, collect errors if any
reinstall clean DBIx::SB
stop/start serverOn 10/10/07, Joop van de Wege JoopvandeWege@mococo.nl wrote:

Emmanuel Lacour wrote:

On Wed, Oct 10, 2007 at 05:46:31PM +0200, Gianluca Cecchi wrote:

Sorry main was an alias for Tickets table…

If I use the same query on mysql 9without the comma…) I get:

ERROR 1054 (42S22): Unknown column ‘ObjectCustomFieldValues_1.Content’ in ‘field list’

And the oracle error 904 is “invalid column name” too

Here is the SQL for the same Graph on a MySQL RT, gived by StatementLog (graph by CustomFields works with MySQL):

SELECT DISTINCT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM (Tickets main LEFT 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.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND ((main.Type = ‘ticket’)) AND ((main.Created < ‘2007-10-31 23:00:00’) AND (main.Created > ‘2007-09-29 22:00:00’)) GROUP BY ObjectCustomFieldValues_1.Content ;
Weird, this one works without modification on Oracle XE.

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

dbix-sb-oracle.patch (609 Bytes)

Emmanuel_Lacour · October 11, 2007, 6:50am

It’s an experimental and untested patch that may help.

It helps, and works here :))

Thanks you very much!

Ruslan_Zakirov1 · October 11, 2007, 7:11pm

It’s an experimental and untested patch that may help.

It helps, and works here :))

Thanks you very much!

That’s cool, let it spin for several days… And may be during next
week we’ll release new version of SB.

Joop, any comments?

Best regards, Ruslan.

Joop · October 11, 2007, 8:08pm

Ruslan Zakirov wrote:> On 10/11/07, Emmanuel Lacour elacour@easter-eggs.com wrote:

On Thu, Oct 11, 2007 at 06:44:04AM +0400, Ruslan Zakirov wrote:

It’s an experimental and untested patch that may help.

It helps, and works here :))

Thanks you very much!

That’s cool, let it spin for several days… And may be during next
week we’ll release new version of SB.

Joop, any comments?
Haven’t had time to try the patch on our test instance and don’t know
when I will. Might be somewhere next week.
The patch looks clean to me and if that statement that worked is the
result of this patch then it looks certainly OK.

Joop

Ruslan_Zakirov1 · November 24, 2007, 1:33am

The patch is in DBIx::SearchBuilder 1.50 which on its way to the CPAN.
It will be available from mirrors soon.On Oct 11, 2007 11:08 PM, Joop van de Wege JoopvandeWege@mococo.nl wrote:

Ruslan Zakirov wrote:

On 10/11/07, Emmanuel Lacour elacour@easter-eggs.com wrote:

On Thu, Oct 11, 2007 at 06:44:04AM +0400, Ruslan Zakirov wrote:

It’s an experimental and untested patch that may help.

It helps, and works here :))

Thanks you very much!

That’s cool, let it spin for several days… And may be during next
week we’ll release new version of SB.

Joop, any comments?
Haven’t had time to try the patch on our test instance and don’t know
when I will. Might be somewhere next week.
The patch looks clean to me and if that statement that worked is the
result of this patch then it looks certainly OK.

Joop

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

Emmanuel_Lacour · November 26, 2007, 8:57am

The patch is in DBIx::SearchBuilder 1.50 which on its way to the CPAN.
It will be available from mirrors soon.

Great, no problem here with this patch.

Emmanuel Lacour ------------------------------------ Easter-eggs
44-46 rue de l’Ouest - 75014 Paris - France - Mï¿½tro Gaitï¿½
Phone: +33 (0) 1 43 35 00 37 - Fax: +33 (0) 1 41 35 00 76
mailto:elacour@easter-eggs.com - http://www.easter-eggs.com