Hello
We have found two bugs in the SQL statements sent to the database
(RT-3.8.2 / postgreSQL 8.3.7).
- The first one gives us the wrong result when generating the
statistics graph for a queue (grouped by Status).
The problem is that it used DISTINCT outside the aggregate function
count(). This way the SQL does not ‘throw away’ identical rows as is
intended.
Insteed of using:
SELECT
DISTINCT COUNT(main.id) AS id,
main.STATUS AS STATUS
FROM Tickets main
it should use:
SELECT
COUNT(DISTINCT main.id) AS id,
main.STATUS AS STATUS
FROM Tickets main
to return the correct result.
The SQL query is:
SELECT DISTINCT COUNT(main.id) AS id, main.Status AS status FROM Tickets
main JOIN Groups Groups_1 ON ( Groups_1.Domain = ‘RT::Ticket-Role’ )
AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = ‘8064’ ) AND
( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (main.Status !=
‘deleted’) AND (main.Queue = ‘6’ AND ( main.Status = ‘open’ OR
main.Status = ‘new’ ) AND ( ( main.Queue = ‘8’ OR main.Queue = ‘1’ OR
main.Queue = ‘26’ OR main.Queue = ‘13’ OR main.Queue = ‘16’ OR
main.Queue = ‘17’ OR main.Queue = ‘20’ OR main.Queue = ‘18’ OR
main.Queue = ‘15’ OR main.Queue = ‘14’ OR main.Queue = ‘19’ OR
main.Queue = ‘21’ OR main.Queue = ‘22’ OR main.Queue = ‘12’ OR
main.Queue = ‘39’ OR main.Queue = ‘31’ OR main.Queue = ‘37’ OR
main.Queue = ‘29’ OR main.Queue = ‘28’ OR main.Queue = ‘3’ OR main.Queue
= ‘41’ OR main.Queue = ‘27’ OR main.Queue = ‘23’ OR main.Queue = ‘25’ OR
main.Queue = ‘30’ OR main.Queue = ‘38’ OR main.Queue = ‘35’ OR
main.Queue = ‘4’ OR main.Queue = ‘40’ OR main.Queue = ‘6’ OR main.Queue
= ‘33’ OR main.Queue = ‘11’ OR main.Queue = ‘34’ OR main.Queue = ‘10’ OR
main.Queue = ‘42’ OR main.Queue = ‘43’ OR main.Queue = ‘9’ OR main.Queue
= ‘44’ OR main.Queue = ‘45’ OR main.Queue = ‘47’ OR main.Queue = ‘48’ OR
main.Queue = ‘49’ OR main.Queue = ‘50’ OR main.Queue = ‘51’ OR
main.Queue = ‘56’ OR main.Queue = ‘57’ OR main.Queue = ‘58’ OR
main.Queue = ‘66’ OR main.Queue = ‘68’ OR main.Queue = ‘67’ OR
main.Queue = ‘71’ OR main.Queue = ‘80’ OR main.Queue = ‘83’ OR
main.Queue = ‘92’ OR main.Queue = ‘94’ OR main.Queue = ‘93’ OR
main.Queue = ‘97’ OR main.Queue = ‘103’ OR main.Queue = ‘104’ OR
main.Queue = ‘106’ OR main.Queue = ‘112’ OR main.Queue = ‘115’ OR
main.Queue = ‘116’ OR main.Queue = ‘126’ OR main.Queue = ‘127’ OR
main.Queue = ‘53’ OR main.Queue = ‘138’ OR main.Queue = ‘96’ OR
main.Queue = ‘150’ OR main.Queue = ‘161’ OR main.Queue = ‘162’ OR
main.Queue = ‘163’ ) OR ( CachedGroupMembers_2.MemberId IS NOT NULL
AND Groups_1.Type = ‘Requestor’ ) OR ( CachedGroupMembers_2.MemberId
IS NOT NULL AND Groups_1.Type = ‘Cc’ ) OR (
CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Type = ‘AdminCc’
) ) ) AND (main.Type = ‘ticket’) AND (main.EffectiveId = main.id)
GROUP BY main.Status
- The second one throws this error when executed:
“ERROR: column “users_2.name” must appear in the GROUP BY clause or be
used in an aggregate function”
Not difficult to understand when it uses:
SELECT DISTINCT COUNT(main.id) AS id, Users_2.Name AS col1
…
…
GROUP BY Users_1.Name
Insteed, it should use “GROUP BY Users_2.Name” or “Users_1.Name AS
col1”. You should know the correct fix, I have not analyze what the SQL
tries to do.
The SQL query is:
SELECT DISTINCT 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 ) JOIN Groups Groups_3 ON (
Groups_3.Domain = ‘RT::Ticket-Role’ ) AND ( Groups_3.Instance = main.id
) LEFT JOIN CachedGroupMembers CachedGroupMembers_4 ON (
CachedGroupMembers_4.MemberId = ‘336890’ ) AND (
CachedGroupMembers_4.GroupId = Groups_3.id ) WHERE (main.Status !=
‘deleted’) AND (main.Queue = ‘40’ AND ( main.Status = ‘open’ OR
main.Status = ‘new’ OR main.Status = ‘stalled’ ) AND ( ( main.Queue =
‘8’ OR main.Queue = ‘1’ OR main.Queue = ‘26’ OR main.Queue = ‘13’ OR
main.Queue = ‘16’ OR main.Queue = ‘17’ OR main.Queue = ‘20’ OR
main.Queue = ‘18’ OR main.Queue = ‘15’ OR main.Queue = ‘14’ OR
main.Queue = ‘19’ OR main.Queue = ‘21’ OR main.Queue = ‘22’ OR
main.Queue = ‘12’ OR main.Queue = ‘39’ OR main.Queue = ‘31’ OR
main.Queue = ‘37’ OR main.Queue = ‘29’ OR main.Queue = ‘28’ OR
main.Queue = ‘3’ OR main.Queue = ‘41’ OR main.Queue = ‘27’ OR main.Queue
= ‘23’ OR main.Queue = ‘25’ OR main.Queue = ‘30’ OR main.Queue = ‘38’ OR
main.Queue = ‘35’ OR main.Queue = ‘4’ OR main.Queue = ‘40’ OR main.Queue
= ‘6’ OR main.Queue = ‘33’ OR main.Queue = ‘11’ OR main.Queue = ‘34’ OR
main.Queue = ‘10’ OR main.Queue = ‘42’ OR main.Queue = ‘43’ OR
main.Queue = ‘44’ OR main.Queue = ‘45’ OR main.Queue = ‘47’ OR
main.Queue = ‘48’ OR main.Queue = ‘49’ OR main.Queue = ‘50’ OR
main.Queue = ‘51’ OR main.Queue = ‘56’ OR main.Queue = ‘57’ OR
main.Queue = ‘58’ OR main.Queue = ‘66’ OR main.Queue = ‘68’ OR
main.Queue = ‘67’ OR main.Queue = ‘71’ OR main.Queue = ‘80’ OR
main.Queue = ‘83’ OR main.Queue = ‘92’ OR main.Queue = ‘94’ OR
main.Queue = ‘93’ OR main.Queue = ‘97’ OR main.Queue = ‘103’ OR
main.Queue = ‘104’ OR main.Queue = ‘106’ OR main.Queue = ‘112’ OR
main.Queue = ‘115’ OR main.Queue = ‘116’ OR main.Queue = ‘126’ OR
main.Queue = ‘127’ OR main.Queue = ‘53’ OR main.Queue = ‘138’ OR
main.Queue = ‘96’ OR main.Queue = ‘150’ OR main.Queue = ‘161’ OR
main.Queue = ‘162’ OR main.Queue = ‘163’ ) OR (
CachedGroupMembers_4.MemberId IS NOT NULL AND Groups_3.Type =
‘Requestor’ ) OR ( CachedGroupMembers_4.MemberId IS NOT NULL AND
Groups_3.Type = ‘Cc’ ) OR ( CachedGroupMembers_4.MemberId IS NOT NULL
AND Groups_3.Type = ‘AdminCc’ ) ) ) AND (main.Type = ‘ticket’) AND
(main.EffectiveId = main.id) GROUP BY Users_1.Name
Do you need more information to fix them?
regards,
Rafael Martinez, r.m.guerrero@usit.uio.no
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/