[rt-users] Report "Resolved by owner" fails to output, generates error in log

[Charles Kugelman]

John,

Thanks for the reply.

I honestly have no idea what would be the best way (or the correct way
for that matter) to do this query (my specialty is actually in
networking, not database engineering).

The problem is simply that out of the box, 2 of the 3 reports fail to
produce any results (Resolved by owner and Resolved in date range). The
error generated in the RT log (see below) is all I really have that
shows the problem.

I can make a change to the affected file, but need some guidance in
finding where/how to make this change.

Attempting to run both of these reports produces the following set of
errors:

[Thu Apr 16 19:11:17 2009] [warning]: DBD::Oracle::db prepare failed:
ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*>
indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id)
AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2
ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id
= main.Owner )  WHERE (main.Status != 'deleted') AND (main.Status =
'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND
(main.EffectiveId = main.id)  GROUP BY Users_1.Name "] at
/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465,
<GEN59> line 85.
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465)

[Thu Apr 16 19:11:17 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0)
couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression
(DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT
COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT
JOIN Users Users_2  ON ( Users_2.id = main.Owner ) LEFT JOIN Users
Users_1  ON ( Users_1.id = main.Owner )  WHERE (main.Status !=
'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id)  GROUP BY
Users_1.Name ')
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472)

[Thu Apr 16 19:11:18 2009] [warning]: DBD::Oracle::db prepare failed:
ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*>
indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id)
AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2
ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id
= main.Owner )  WHERE (main.Status != 'deleted') AND (main.Status =
'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND
(main.EffectiveId = main.id)  GROUP BY Users_1.Name "] at
/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465,
<GEN61> line 85.
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465)

[Thu Apr 16 19:11:18 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0)
couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression
(DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT
COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT
JOIN Users Users_2  ON ( Users_2.id = main.Owner ) LEFT JOIN Users
Users_1  ON ( Users_1.id = main.Owner )  WHERE (main.Status !=
'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id)  GROUP BY
Users_1.Name ')
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472)

[Thu Apr 16 19:11:29 2009] [warning]: DBD::Oracle::db prepare failed:
ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*>
indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id)
AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2
ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id
= main.Owner )  WHERE (main.Status != 'deleted') AND (main.Status =
'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND
(main.EffectiveId = main.id)  GROUP BY Users_1.Name "] at
/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465,
<GEN67> line 85.
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465)

[Thu Apr 16 19:11:29 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0)
couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression
(DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT
COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT
JOIN Users Users_2  ON ( Users_2.id = main.Owner ) LEFT JOIN Users
Users_1  ON ( Users_1.id = main.Owner )  WHERE (main.Status !=
'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id)  GROUP BY
Users_1.Name ')
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472)

[Thu Apr 16 19:11:29 2009] [warning]: DBD::Oracle::db prepare failed:
ORA-00979: not a GROUP BY expression (DBD ERROR: error possibly near <*>
indicator at char 29 in 'SELECT COUNT(main.id) AS id, <*>Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name ') [for Statement "SELECT COUNT(main.id)
AS id, Users_2.Name AS col1 FROM Tickets main LEFT JOIN Users Users_2
ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id
= main.Owner )  WHERE (main.Status != 'deleted') AND (main.Status =
'resolved' AND main.Queue = '5') AND (main.Type = 'ticket') AND
(main.EffectiveId = main.id)  GROUP BY Users_1.Name "] at
/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 465,
<GEN69> line 85.
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465)

[Thu Apr 16 19:11:29 2009] [warning]: RT::Handle=HASH(0x2b03abb5bfb0)
couldn't prepare the query 'SELECT COUNT(main.id) AS id, Users_2.Name AS
col1 FROM Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id =
main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
WHERE (main.Status != 'deleted') AND (main.Status = 'resolved' AND
main.Queue = '5') AND (main.Type = 'ticket') AND (main.EffectiveId =
main.id)  GROUP BY Users_1.Name 'ORA-00979: not a GROUP BY expression
(DBD ERROR: error possibly near <*> indicator at char 29 in 'SELECT
COUNT(main.id) AS id, <*>Users_2.Name AS col1 FROM Tickets main LEFT
JOIN Users Users_2  ON ( Users_2.id = main.Owner ) LEFT JOIN Users
Users_1  ON ( Users_1.id = main.Owner )  WHERE (main.Status !=
'deleted') AND (main.Status = 'resolved' AND main.Queue = '5') AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id)  GROUP BY
Users_1.Name ')
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:472)

Thanks again for your assistance.

-CK



-----Original Message-----
From: John Hascall [mailto:john at iastate.edu] 
Sent: Thursday, April 16, 2009 9:54 AM
To: Charles Kugelman
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] Report "Resolved by owner" fails to output,
generates error in log 



> SELECT COUNT(main.id) AS id,
>            Users_2.Name AS col1
> FROM Tickets main
> LEFT JOIN Users Users_2  ON ( Users_2.id = main.Owner )
> LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner ) 
> WHERE (main.Status != 'deleted')
>   AND (main.Status = 'resolved' AND main.Queue = '5')
>   AND (main.Type = 'ticket')
>   AND (main.EffectiveId = main.id) 
> GROUP BY Users_2.Name;

While we are looking at this query,
it seems to me that the first where clause is
made redundant by the second one:

> WHERE (main.Status != 'deleted')
>   AND (main.Status = 'resolved' AND main.Queue = '5')

If (main.Status = 'resolved' AND main.Queue = '5')
then it must be true that (main.Status != 'deleted')

And I don't see any use of the Tickets table.
Also, where is the 2nd left join used?
Thus:

> SELECT COUNT(main.id) AS id,
>            Users.Name AS col1
> FROM main
> LEFT JOIN Users ON ( Users.id = main.Owner )
> WHERE (main.Status = 'resolved')
>   AND (main.Queue = '5')
>   AND (main.Type = 'ticket')
>   AND (main.EffectiveId = main.id) 
> GROUP BY Users.Name;

would seem to be a lot simpler.
Or have I overlooked something obvious?

John