[rt-devel] Missing brackets in LEFT JOIN, Searchbuilder 0.81

[Warnke, Andreas]

Hello Jesse,

we are using your RequestTracker, version 2, together with version
0.81 of DBIx::SearchBuilder and a MySQL 4.01. We heavily - at least
heavier than it seems to have ever been expected - use keywords.
Among other things, we search for
different keywords of the same keywordselect. A resulting SQL
statement (gained from logging the mysqld) is this:
   SELECT
   DISTINCT
   main.*
   FROM Tickets main
   LEFT JOIN ObjectKeywords as ObjectKeywords_1 ON
   (ObjectKeywords_1.KeywordSelect = '25')OR(ObjectKeywords_1.KeywordSelect = 
'25') AND main.id = ObjectKeywords_1.ObjectId
   WHERE ((ObjectKeywords_1.Keyword = '817')OR(ObjectKeywords_1.Keyword = 
'508')) AND ((ObjectKeywords_1.ObjectType = 
'Ticket')AND(ObjectKeywords_1.ObjectType = 'Ticket')) AND ((main.EffectiveId 
= main.id))
   ORDER BY main.id ASC LIMIT 50

Please note that there are no extra brackets around the OR-terms
after "LEFT JOIN xyz ON". This makes the select time intensive
(executed via MyPHPAdmin: 6 minutes on a slow PC, 1300 tickets). With
brackets it's so fast that I cannot measure the time.

I assume a little change in Searchbuilder.pm would cause a miracle.
But I'm not the wizard ...  ;-)

Regards,
Horst Gwinner

-- 
Horst Gwinner, Mobile Applications
3SOFT GmbH, Frauenweiherstraße 14, 91058 Erlangen, Germany
Telefon: +49/9131/7701-185         mailto:Gwinner at 3SOFT.de
Telefax: +49/9131/7701-333             http://www.3SOFT.de