Report "Resolved by owner" fails to output, generates error in log

RT Users
1

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,
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

2

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

-CK

[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Charles
KugelmanSent: Monday, April 13, 2009 12:25 PM
To: rt-users@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,
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

3

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

[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Charles
KugelmanSent: Wednesday, April 15, 2009 8:43 AM
To: rt-users@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@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Charles
Kugelman
Sent: Monday, April 13, 2009 12:25 PM
To: rt-users@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,
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

4

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

5

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,
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,
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,
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,
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.

-CKFrom: John Hascall [mailto:john@iastate.edu]
Sent: Thursday, April 16, 2009 9:54 AM
To: Charles Kugelman
Cc: rt-users@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

6

I believe this issue has been fixed in 3.8.HEAD, please test first RC
when it will be available.On Mon, Apr 13, 2009 at 8:25 PM, Charles Kugelman Charles.Kugelman@kaplan.com wrote:

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,
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

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.

7

Thanks for the info Ruslan.

-CK