Hello We have found two bugs in the SQL statements sent to the database (RT-3.8.2 / postgreSQL 8.3.7).
1) 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 -------------------------------------------------------------------- 2) 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, <[email protected]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
