use strict;
use warnings;
-use Test::More tests => 35;
+use Test::More tests => 44;
use_ok('RT');
RT::LoadConfig();
RT::Init();
my $cflabel3 = "CustomField-".$cf3->id;
+# There was a bug involving a missing join to ObjectCustomFields that
+# caused spurious results on negative searches if another custom field
+# with the same name existed on a different queue. Hence, we make
+# duplicate CFs on a different queue here
+my $dup = RT::Queue->new($RT::SystemUser);
+$dup->Create(Name => $queue . "-Copy");
+ok ($dup->id, "Created the duplicate queue");
+my $dupcf = RT::CustomField->new($RT::SystemUser);
+$dupcf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
+ok($dupcf->id, "Created the duplicate SearchTest CF");
+$dupcf = RT::CustomField->new($RT::SystemUser);
+$dupcf->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
+ok($dupcf->id, "Created the SearchTest2 CF");
+$dupcf = RT::CustomField->new($RT::SystemUser);
+$dupcf->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
+ok($dupcf->id, "Created the SearchTest3 CF");
+
+
# setup some tickets
# we'll need a small pile of them, to test various combinations and nulls.
# there's probably a way to think harder and do this with fewer
# we have tickets. start searching
my $tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue'");
-is($tix->Count, 7, "found all the tickets");
+is($tix->Count, 7, "found all the tickets")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
# very simple searches. both CF and normal
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo1'");
-is($tix->Count, 1, "matched identical subject");
+is($tix->Count, 1, "matched identical subject")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo1'");
-is($tix->Count, 1, "matched LIKE subject");
+is($tix->Count, 1, "matched LIKE subject")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo'");
-is($tix->Count, 0, "IS a regexp match");
+is($tix->Count, 0, "IS a regexp match")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo'");
-is($tix->Count, 5, "matched LIKE subject");
+is($tix->Count, 5, "matched LIKE subject")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL");
-
- is($tix->Count, 2, "IS null CF");
+is($tix->Count, 2, "IS null CF")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search1'");
-is($tix->Count, 1, "LIKE requestor");
+is($tix->Count, 1, "LIKE requestor")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Requestors = 'search1\@example.com'");
-is($tix->Count, 1, "IS requestor");
+is($tix->Count, 1, "IS requestor")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search'");
-is($tix->Count, 6, "LIKE requestor");
+is($tix->Count, 6, "LIKE requestor")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
-TODO: {
-
- local $TODO = "Can't search for 'no requestor";
- $tix = RT::Tickets->new($RT::SystemUser);
- $tix->FromSQL("Queue = '$queue' AND Requestors IS NULL");
- is($tix->Count, 1, "Search for no requestor");
-
-};
+$tix = RT::Tickets->new($RT::SystemUser);
+$tix->FromSQL("Queue = '$queue' AND Requestors IS NULL");
+is($tix->Count, 1, "Search for no requestor")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Subject = 'SearchTest1'");
-is($tix->Count, 1, "IS subject");
+is($tix->Count, 1, "IS subject")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest1'");
-is($tix->Count, 1, "LIKE subject");
+is($tix->Count, 1, "LIKE subject")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Subject = ''");
-is($tix->Count, 1, "found one ticket");
+is($tix->Count, 1, "found one ticket")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest'");
-is($tix->Count, 6, "found two ticket");
+is($tix->Count, 6, "found two ticket")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND Subject LIKE 'qwerty'");
-is($tix->Count, 0, "found zero ticket");
+is($tix->Count, 0, "found zero ticket")
+ or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("CF.SearchTest IS NULL AND CF.SearchTest2 = 'bar2'");
-
- is($tix->Count, 1, "null cf and is cf");
+is($tix->Count, 1, "null cf and is cf");
$tix = RT::Tickets->new($RT::SystemUser);
$tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL AND CF.SearchTest2 IS NULL");
+is($tix->Count, 1, "null cf and null cf");
- is($tix->Count, 1, "null cf and null cf");
+# tests with the same CF listed twice
+$tix = RT::Tickets->new($RT::SystemUser);
+$tix->FromSQL("CF.{SearchTest} = 'foo1'");
+is($tix->Count, 1, "is cf.{name} format");
+
+$tix = RT::Tickets->new($RT::SystemUser);
+$tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3'");
+is($tix->Count, 2, "is cf1 or is cf1");
+
+$tix = RT::Tickets->new($RT::SystemUser);
+$tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest IS NULL");
+is($tix->Count, 3, "is cf1 or null cf1");
+
+$tix = RT::Tickets->new($RT::SystemUser);
+$tix->FromSQL("(CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3') AND (CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2')");
+is($tix->Count, 1, "(is cf1 or is cf1) and (is cf2 or is cf2)");
+
+$tix = RT::Tickets->new($RT::SystemUser);
+$tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3' OR CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2'");
+is($tix->Count, 3, "is cf1 or is cf1 or is cf2 or is cf2");