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

[Charles Kugelman]

All

 

I ran this issue by one of our DBAs and this is his comment:

 

 

Charles,

 

The problem with the following query is that the summary statement
(GROUP BY) references the wrong column. The query is doing a summary
count by user name. The table.column_name in the SELECT clause has to
match the table.column_name in the GROUP BY clause.

 

Incorrect:

 

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

 

Correct:

 

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;

 

I executed the correct version as user RT3 and got these results:

 

        ID COL1

---------- ----------

         8 Nobody

         6 ---

         1 ---

         3 root

 

Regards,

John

 

 

The question is: where do I make the correction to the query?

 

Thanks in advance for the assist!

 

-CK

 

________________________________

From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Charles
Kugelman
Sent: Wednesday, April 15, 2009 8:43 AM
To: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] Report "Resolved by owner" fails to
output,generates error in log

 

Any thoughts on this? Does this appear to anyone to be a bug?

 

-CK

 

________________________________

From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Charles
Kugelman
Sent: Monday, April 13, 2009 12:25 PM
To: rt-users at lists.bestpractical.com
Subject: [rt-users] Report "Resolved by owner" fails to output,generates
error in log

 

All,

 

It's been pointed out that the "Resolved by owner" report isn't
outputting results. I looked in the RT error log and found the entries
below. Thoughts?

 

---Relevant Info---

- RT Version: 3.8.2

- HTTPD Package (RPM): httpd-2.2.3-22.el5

- Mod_Perl Package (RPM): mod_perl-2.0.4-6.el5

- Perl Package (RPM): perl-5.8.8-18.el5 

- OS: RedHat Enterprise Linux 5

- Oracle Client: 10.2, Instant Client

- Oracle Server (remote server): 11g

- Mail Package (RPM): postfix-2.3.3-2

 

---Errors---

[Mon Apr 13 16:08:14 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,
<GEN79> line 130.
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:465)

 

[Mon Apr 13 16:08:14 2009] [warning]: RT::Handle=HASH(0x2b9ff6d2c540)
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,

 

-CK

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090416/43ce13ee/attachment.htm>