[rt-users] Using 'today' in an Advanced search gives different results than using explicit date.

[Robert Blackwell]

On Fri, Aug 24, 2012 at 12:59 PM, Thomas Sibley <trs at bestpractical.com> wrote:
> On 08/24/2012 09:35 AM, Robert Blackwell wrote:
>> For example I would expect these to return the same tickets but they don't:
>>
>> This returns 7 tickets.
>> Status != 'resolved' AND CF.{FollowUp} < '2012-08-25'
>>
>> This returns 513.
>> Status != 'resolved' AND CF.{FollowUp} < 'tomorrow'
>
> Capture the SQL that gets run for those queries, and we can see the
> difference between parsing.  It _shouldn't_ be different, but I'm
> guessing an implicit time part might be sneaking in.
>

Here is the SQL and stack trace of two queries obtained by using
"Configuration » Tools » SQL Queries".


    SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON (
ObjectCustomFieldValues_1.CustomField = '49' ) AND (
ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_1.Disabled = '0' ) AND (
ObjectCustomFieldValues_1.ObjectId = main.id ) LEFT JOIN
CustomFieldValues CustomFieldValues_2 ON (
CustomFieldValues_2.CustomField =
ObjectCustomFieldValues_1.CustomField ) AND ( CustomFieldValues_2.Name
= ObjectCustomFieldValues_1.Content ) WHERE (main.Status != 'deleted')
AND (main.Queue = '74' AND ( ( ( ObjectCustomFieldValues_1.Content <
'today' OR ( ( ObjectCustomFieldValues_1.Content = '' OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent < 'today' ) ) ) ) ) AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id) Toggle stack
trace

Stack:
  [/opt/rt4/sbin/../lib/RT/Handle.pm:1180]
  [/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm:589]
  [/usr/local/share/perl5/DBIx/SearchBuilder.pm:291]
  [/opt/rt4/sbin/../lib/RT/SearchBuilder.pm:331]
  [/opt/rt4/sbin/../lib/RT/Tickets.pm:2961]
  [/usr/local/share/perl5/DBIx/SearchBuilder.pm:1461]
  [/opt/rt4/sbin/../lib/RT/Tickets.pm:2871]
  [/opt/rt4/share/html/Search/Results.html:132]
  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:548]
  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:295]
  [/opt/rt4/share/html/autohandler:53]




         SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON (
ObjectCustomFieldValues_1.CustomField = '49' ) AND (
ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_1.Disabled = '0' ) AND (
ObjectCustomFieldValues_1.ObjectId = main.id ) LEFT JOIN
CustomFieldValues CustomFieldValues_2 ON (
CustomFieldValues_2.CustomField =
ObjectCustomFieldValues_1.CustomField ) AND ( CustomFieldValues_2.Name
= ObjectCustomFieldValues_1.Content ) WHERE (main.Status != 'deleted')
AND (main.Queue = '74' AND ( ( ( ObjectCustomFieldValues_1.Content <
'2012-08-27' OR ( ( ObjectCustomFieldValues_1.Content = '' OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent < '2012-08-27' ) ) ) ) ) AND
(main.Type = 'ticket') AND (main.EffectiveId = main.id) Toggle stack
trace

Stack:
  [/opt/rt4/sbin/../lib/RT/Handle.pm:1180]
  [/usr/local/share/perl5/DBIx/SearchBuilder/Handle.pm:589]
  [/usr/local/share/perl5/DBIx/SearchBuilder.pm:291]
  [/opt/rt4/sbin/../lib/RT/SearchBuilder.pm:331]
  [/opt/rt4/sbin/../lib/RT/Tickets.pm:2961]
  [/usr/local/share/perl5/DBIx/SearchBuilder.pm:1461]
  [/opt/rt4/sbin/../lib/RT/Tickets.pm:2871]
  [/opt/rt4/share/html/Search/Results.html:132]
  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:548]
  [/opt/rt4/sbin/../lib/RT/Interface/Web.pm:295]
  [/opt/rt4/share/html/autohandler:53]

Thanks
Robert