[Rt-devel] Problematic Search/Display Query

[Curtis Bruneau]

Greetings everyone,

We're having an issue with several versions of RT that are all fully 
functional aside from one aspect of operation.   The brief description 
of the problem is that when you go to /Ticket/Display.html?id=<id> from 
a search page (/Search/Build.html or /Search/Simple.html  ) what appears 
to be happening is the search is saved in the session (normal) but it's 
getting queried on Display along with the other queries.  This  appears 
to be an extra query of no use with a limitless result set. This is 
problematic because however many records your search returns the display 
will try to load all the records into the web server potentially causing 
the process to run out of memory.

Packaged OS Details:
-Debian Etch - kernel 2.6.18-6-amd64 x86_64 GNU/Linux
-apache2 2.2.3-4+etch4
-libapache2-mod-perl2 2.0.2-2.4
-mysql-server-5.0 5.0.51a-3~bpo40+1)
-perl v5.8.8

 From source:
-libdbix-searchbuilder-perl_1.53-1
-libmodule-versions-report-perl_1.03-2
-libtext-quoted-perl-2.05-2

RT Versions Tested and Affected: 3.6.6, 3.7.85, 3.7.86, 3.8.0rc1
'make testdeps' in each RT version: PASSED

Steps to reproduce this problem:

1) Start with a clean installation and clean database.
2) Create a few tickets in the default "General" queue as root.
3) Enable mysql query logging

log             = /var/log/mysql/mysql.log

Note: Without query logging on the database server you may not notice it 
unless your search returned thousands of records causing major slowness.

4)  Next, load the search page in your browser by adding the search 
criteria and then selecting "Update format and Search" ( 
Search/Build.html with a listing of the result set of your search). For 
this example I created 2 test tickets with similar subjects and made my 
search the subject for the text 'e' which will match both of my tickets. 
There idea here is to get a common result with multiple records to 
observe the general query on the db side.

5) Select one of the result tickets (/Ticket/Display.html?id=...)

6) **BUG LOADED HERE** -- the search clause, which appears to be stored 
in the session are used on the following query. The actual query used to 
display the data is separate and is limited by the Ticket ID.

Watch the query log for:

Query       SELECT main.* FROM Tickets main  WHERE (main.Status != 
'deleted') AND (main.Subject LIKE '%e%') AND (main.EffectiveId = 
main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC

As you can see the query will return as many records as it matches, 
potentially thousands or more. The same issue occurs regardless of the 
search criteria and will always have no limit - it's basically the same 
query as select count(*) when building the search results paging list, 
the displayed results also have the same but with a limit/offset. I can 
not seem to figure out why this is happening, the only reason I can 
think of as to why this query is needed is possibly some sort of count 
but the count is missing.

This problem does not occur when you go to the link directly with no 
referring URL or a non search page, it only seems to happen when a 
search is saved in the session. Could someone please test this to see if 
this has been overlooked as most installations wouldn't show any symptom 
of the problem until there were a large enough result set to cause 
system memory exhaustion. If you aren't observing this problem I 
wouldn't mind knowing as well.

Thank you in advance,