Skip Menu |
 

This queue is for tickets about the DBIx-SearchBuilder CPAN distribution.

Report information
The Basics
Id: 48382
Status: stalled
Worked: 10 min
Priority: 0/
Queue: DBIx-SearchBuilder

People
Owner: RUZ [...] cpan.org
Requestors: jpierce [...] cambridgeenergyalliance.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: 1.56
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 2.3k
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.
Subject: Re: [rt.cpan.org #48382] AutoReply: SearchBuilder seems to inappropriately quote operands for numerical operators?
Date: Mon, 3 Aug 2009 00:18:35 -0400
To: bug-DBIx-SearchBuilder [...] rt.cpan.org
From: Jerrad Pierce <jpierce [...] cambridgeenergyalliance.org>
Download (untitled) / with headers
text/plain 345b
I know the POD says "Should always be set and will always be quoted." but it makes an exception for IS, and seeing as quoting can lead to bad results as above, please find attached a patch to remedy this. Afterall, the POD doesn't even recognize these as being legitimate operators. -- Cambridge Energy Alliance: Save money. Save the planet.
Download nomathquote.patch
text/x-diff 636b

Message body is not shown because sender requested not to inline it.

Subject: Re: [rt.cpan.org #48382] AutoReply: SearchBuilder seems to inappropriately quote operands for numerical operators?
Date: Tue, 4 Aug 2009 12:31:44 -0400
To: bug-DBIx-SearchBuilder [...] rt.cpan.org
From: Jerrad Pierce <jpierce [...] cambridgeenergyalliance.org>
Download (untitled) / with headers
text/plain 239b
Doh! That patch broke date searches, this one fixes them. It does not quote operands for IS (pre-existing) and less or greater than iff the operand is an integer or float point. -- Cambridge Energy Alliance: Save money. Save the planet.
Download nomathquote2.patch
text/x-diff 860b

Message body is not shown because sender requested not to inline it.

Hello Jerrad, Thanks for the patch. However the second one still broken, the following line should be changed: $args{'QUOTEVALUE'} =~ /^[-+]?[0-9]*\.?[0-9]+$/ QUOTEVALUE is a boolean argument when you should check VALUE instead. Patch can not be applied and released. It probably changes semantic for TEXT (char, varchar ...) columns and either slows down searches (auto casting from text to number) or errors (I suspect recent Pg versions where you should cast explicit). You can use this patch as long as it works for you, but for public release we need something more mature and covered with tests. Marking ticket as whishlist and stall. May be it even should be rejected as we have QUOTEVALUE option and caller can tell what he wants to do. On Tue Aug 04 12:38:00 2009, jpierce@cambridgeenergyalliance.org wrote: Show quoted text
> Doh! That patch broke date searches, this one fixes them. > It does not quote operands for IS (pre-existing) and less or > greater than iff the operand is an integer or float point. >
-- Best regards, Ruslan.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.