[rt-users] Problems with RT 3.8.2 - SQL bugs

[Rafael Martinez]

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, <r.m.guerrero at usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/