| Subject: | SearchBuilder seems to inappropriately quote operands for numerical operators? |
| Date: | Mon, 3 Aug 2009 00:00:40 -0400 |
| To: | rt-bugs [...] bestpractical.com, bug-DBIx-SearchBuilder [...] rt.cpan.org |
| From: | Jerrad Pierce <jpierce [...] cambridgeenergyalliance.org> |
I am trying to search a custom field for values between 4 and 50...
simple enough:
'CF.{X}' > 4 AND 'CF.{X}' < 50.
However, the results returned do not match what is expected. When I perform the
equivalent query on the database
SELECT ObjectId, Content from ObjectCustomFieldValues where
CustomField=30 AND Content < 50 AND Content > 4;
I get (adding ORDER BY Content DESC for clarity):
+----------+---------------------+
| ObjectId | Content |
+----------+---------------------+
| 281 | 6 |
| 223 | 6 |
| 3060 | 6 |
| 1475 | 5 |
| 786 | 45 |
| 3204 | 41 |
| 1929 | 40 (entire complex) |
| 2494 | 15 |
| 361 | 11 |
| 367 | 10 |
| 2429 | 10 |
+----------+---------------------+
The difference is not permissions or tickets status, as I get the same
4 paltry results when
I execute the search as root. The tickets that match are 786, 1475,
1929 & 3204. It seems
somewhere along the line the criteria turn into a funky sort of string
match i.e; RT is finding
tickets where the custom field starts with 4 or 5 (like the criteria)
The query SearchBuilder uses has quoted numbers (even though the
RT-SQL does not):
SELECT DISTINCT main.* FROM Tickets main JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_1 ON ( ObjectCustomFieldValues_1.CustomField
= '30' ) AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' )
AND ( ObjectCustomFieldValues_1.Disabled = '0' ) AND (
ObjectCustomFieldValues_1.ObjectId = main.id ) WHERE (main.Status !=
'deleted') AND ( ( ( ( ObjectCustomFieldValues_1.Content > '4' OR (
( ObjectCustomFieldValues_1.Content = '' OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent > '4' ) ) ) ) AND ( ( (
ObjectCustomFieldValues_1.Content < '50' OR ( (
ObjectCustomFieldValues_1.Content = '' OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent < '50' ) ) ) ) ) AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY
main.id ASC
If I use the earlier query directly on the database, but with quoted numbers,
I also get only 4 tickets. This is on MySQL 4-something with
Searchbuilder 1.54 & 1.56
--
Cambridge Energy Alliance: Save money. Save the planet.