Chart for transaction search results

RT Users
1

Hello,
I am trying to create some simple chart which is based on transaction search, for example to illustrate number of correspond transactions were done. When I setup my transaction search query and go for the Chart, I get an error “Your query exceeded the maximum run time and was stopped. Try modifying your query to improve the performance or contact your RT admin.”
Request tracker log shows me bunch of errors like
2025-05-05T08:00:42.806757+00:00 rsyslog rtir-apache2-error: [11902] [Mon May 5 08:00:42 2025] [warning]: DBD::Pg::st execute failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
2025-05-05T08:00:42.806795+00:00 rsyslog rtir-apache2-error: LINE 1: …EN NULL ELSE main.Created END < $29 ) ) ORDER BY main.Creat…
2025-05-05T08:00:42.806809+00:00 rsyslog rtir-apache2-error: ^ at /usr/local/share/perl/5.34.0/DBIx/SearchBuilder/Handle.pm line 634. (/usr/local/share/perl/5.34.0/DBIx/SearchBuilder/Handle.pm:634)

Wondering I stumbled on some RT bug, or it is my deployment related problem. Can anyone confirm that RT’s transaction charts works? I am using RT 5.0.7.

–
Marius

2

Hi. Mine is 5.0.8 and it works as expected. Didn’t try on 5.0.7.

3

Hi, again.

I could reproduce this issue in 6.0.3.

This error is shown in non admin users.

Tested with the same user marked now as “can do anything”, then, no error.

4

The SQL is:

SELECT DISTINCT main.id AS id FROM Transactions main JOIN Tickets Tickets_1 ON ( Tickets_1.id = main.ObjectId AND main.ObjectType=$1 ) LEFT JOIN Groups Groups_2 ON ( LOWER(Groups_2.Domain) = $2 ) AND ( Groups_2.Instance = Tickets_1.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.Disabled = $3 ) AND ( CachedGroupMembers_3.MemberId IN ($4, $5, $6, $7, $8, $9, $10, $11, $12) ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) WHERE ( ( Tickets_1.Queue IN ($13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN ($31) AND Tickets_1.Queue IN ($32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61) ) OR ( Tickets_1.Owner = $62 AND Tickets_1.Queue IN ($63, $64, $65) ) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN ($66) AND Tickets_1.Queue IN ($67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78) ) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN ($79) AND Tickets_1.Queue IN ($80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101) ) ) ) AND (LOWER(main.ObjectType) = $102 AND LOWER(Tickets_1.Type) = $103 AND ( CASE WHEN main.Created BETWEEN $104 AND $105 THEN NULL ELSE main.Created END > $106 ) ) ORDER BY main.Created ASC, main.id ASC

And the error log says:

2026-09-22 07:29:53.695 CEST [270226] rt@rt:270226:1:10.80.87.77 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 1482

The SELECT has only the column main.id but the ORDER BY has two columns: main.Created and main.id

This is happening to non admin users.