From 1809f3cf21bfcc5eef4be8e5b305a39e236cee03 Mon Sep 17 00:00:00 2001
From: eileen <emcnaughton@wikimedia.org>
Date: Wed, 8 Feb 2017 07:39:34 +1300
Subject: [PATCH]  CRM-19980, CRM-19881 - Fix slow queries due to LOWER on
 contact name fields

---
 CRM/Contact/BAO/Query.php                   |  3 +-
 tests/phpunit/CRM/Contact/BAO/QueryTest.php | 32 +++++++++++++++------
 2 files changed, 25 insertions(+), 10 deletions(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index d7e47b6520..b0e360a680 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -2292,8 +2292,7 @@ class CRM_Contact_BAO_Query {
       }
       else {
         if ($tableName == 'civicrm_contact') {
-          // LOWER roughly translates to 'hurt my database without deriving any benefit' See CRM-19811.
-          $fieldName = "LOWER(contact_a.{$fieldName})";
+          $fieldName = "contact_a.{$fieldName}";
         }
         else {
           if ($op != 'IN' && !is_numeric($value) && !is_array($value)) {
diff --git a/tests/phpunit/CRM/Contact/BAO/QueryTest.php b/tests/phpunit/CRM/Contact/BAO/QueryTest.php
index 8111339be4..2f05beed84 100644
--- a/tests/phpunit/CRM/Contact/BAO/QueryTest.php
+++ b/tests/phpunit/CRM/Contact/BAO/QueryTest.php
@@ -254,14 +254,30 @@ class CRM_Contact_BAO_QueryTest extends CiviUnitTestCase {
    */
   public function testCaseInsensitive() {
     $orgID = $this->organizationCreate(array('organization_name' => 'BOb'));
-    $this->callAPISuccess('Contact', 'create', array('display_name' => 'Minnie Mouse', 'employer_id' => $orgID, 'contact_type' => 'Individual'));
-    $searchParams = array(array('current_employer', '=', 'bob', 0, 1));
-    $query = new CRM_Contact_BAO_Query($searchParams);
-    $result = $query->apiQuery($searchParams);
-    $this->assertEquals(1, count($result[0]));
-    $contact = reset($result[0]);
-    $this->assertEquals('Minnie Mouse', $contact['display_name']);
-    $this->assertEquals('BOb', $contact['current_employer']);
+    $params = array(
+      'display_name' => 'Minnie Mouse',
+      'first_name' => 'Minnie',
+      'last_name' => 'Mouse',
+      'employer_id' => $orgID,
+      'contact_type' => 'Individual',
+      'nick_name' => 'Mins',
+    );
+    $this->callAPISuccess('Contact', 'create', $params);
+    unset($params['contact_type']);
+    foreach ($params as $key => $value) {
+      if ($key == 'employer_id') {
+        $searchParams = array(array('current_employer', '=', 'bob', 0, 1));
+      }
+      else {
+        $searchParams = array(array($key, '=', strtolower($value), 0, 1));
+      }
+      $query = new CRM_Contact_BAO_Query($searchParams);
+      $result = $query->apiQuery($searchParams);
+      $this->assertEquals(1, count($result[0]), 'search for ' . $key);
+      $contact = reset($result[0]);
+      $this->assertEquals('Minnie Mouse', $contact['display_name']);
+      $this->assertEquals('BOb', $contact['current_employer']);
+    }
   }
 
   /**
-- 
2.25.1