From 7d3b1a9d9b795176a7fd82d9d29a0ba5aa77c40b Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Wed, 1 Nov 2017 10:22:31 +1100 Subject: [PATCH] CRM-21364 Non controversial fixes for ONLY_FULL_GROUP_BY and NO_ZERO_DATE sqlModes --- CRM/Activity/BAO/Activity.php | 14 +++++++++----- CRM/Mailing/Event/BAO/Opened.php | 5 ++++- tests/phpunit/CRM/Contact/BAO/GroupContactTest.php | 2 +- tests/phpunit/api/v3/CustomApiTest.php | 6 +++--- 4 files changed, 17 insertions(+), 10 deletions(-) diff --git a/CRM/Activity/BAO/Activity.php b/CRM/Activity/BAO/Activity.php index 05c3f6bb31..a591d350f7 100644 --- a/CRM/Activity/BAO/Activity.php +++ b/CRM/Activity/BAO/Activity.php @@ -909,7 +909,7 @@ class CRM_Activity_BAO_Activity extends CRM_Activity_DAO_Activity { ); $sql = "CREATE TEMPORARY TABLE {$activityTempTable} ( "; - $insertValueSQL = array(); + $insertValueSQL = $selectColumns = array(); // The activityTempTable contains the sorted rows // so in order to maintain the sort order as-is we add an auto_increment // field; we can sort by this later to ensure the sort order stays correct. @@ -917,6 +917,12 @@ class CRM_Activity_BAO_Activity extends CRM_Activity_DAO_Activity { foreach ($tableFields as $name => $desc) { $sql .= "$name $desc,\n"; $insertValueSQL[] = $name; + if ($name == 'source_contact_name' && CRM_Utils_SQL::supportsFullGroupBy()) { + $selectColumns[] = "ANY_VALUE(tbl.$name)"; + } + else { + $selectColumns[] = "tbl.$name"; + } } // add unique key on activity_id just to be sure @@ -929,7 +935,7 @@ class CRM_Activity_BAO_Activity extends CRM_Activity_DAO_Activity { CRM_Core_DAO::executeQuery($sql); - $insertSQL = "INSERT INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) "; + $insertSQL = "INSERT IGNORE INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) "; $order = $limit = $groupBy = ''; $groupBy = " GROUP BY tbl.activity_id, tbl.activity_type, tbl.case_id, tbl.case_subject "; @@ -961,9 +967,7 @@ class CRM_Activity_BAO_Activity extends CRM_Activity_DAO_Activity { $input['count'] = FALSE; list($sqlClause, $params) = self::deprecatedGetActivitySQLClause($input); - $query = "{$insertSQL} - SELECT DISTINCT tbl.* from ( {$sqlClause} ) -as tbl "; + $query = sprintf("{$insertSQL} \n SELECT DISTINCT %s from ( %s ) \n as tbl ", implode(', ', $selectColumns), $sqlClause); // Filter case activities - CRM-5761. $components = self::activityComponents(); diff --git a/CRM/Mailing/Event/BAO/Opened.php b/CRM/Mailing/Event/BAO/Opened.php index 11b99fadd5..a1f63b4ec8 100644 --- a/CRM/Mailing/Event/BAO/Opened.php +++ b/CRM/Mailing/Event/BAO/Opened.php @@ -283,7 +283,10 @@ class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened { $query .= $groupBy; - $orderBy = "sort_name ASC, {$open}.time_stamp DESC"; + $orderBy = "sort_name ASC"; + if (!$is_distinct) { + $orderBy .= ", {$open}.time_stamp DESC"; + } if ($sort) { if (is_string($sort)) { $sort = CRM_Utils_Type::escape($sort, 'String'); diff --git a/tests/phpunit/CRM/Contact/BAO/GroupContactTest.php b/tests/phpunit/CRM/Contact/BAO/GroupContactTest.php index 3de4a6e187..2fd075ec99 100644 --- a/tests/phpunit/CRM/Contact/BAO/GroupContactTest.php +++ b/tests/phpunit/CRM/Contact/BAO/GroupContactTest.php @@ -329,7 +329,7 @@ class CRM_Contact_BAO_GroupContactTest extends CiviUnitTestCase { foreach ($useCases as $case) { $query = new CRM_Contact_BAO_Query(CRM_Contact_BAO_Query::convertFormValues($case['form_value'])); list($select, $from, $where, $having) = $query->query(); - $groupContacts = CRM_Core_DAO::executeQuery("SELECT DISTINCT contact_a.id $from $where ORDER BY contact_a.first_name")->fetchAll(); + $groupContacts = CRM_Core_DAO::executeQuery("SELECT DISTINCT contact_a.id, contact_a.first_name $from $where ORDER BY contact_a.first_name")->fetchAll(); foreach ($groupContacts as $key => $value) { $groupContacts[$key] = $value['id']; } diff --git a/tests/phpunit/api/v3/CustomApiTest.php b/tests/phpunit/api/v3/CustomApiTest.php index 715cc2a73d..2b27d146d9 100644 --- a/tests/phpunit/api/v3/CustomApiTest.php +++ b/tests/phpunit/api/v3/CustomApiTest.php @@ -60,8 +60,8 @@ class api_v3_CustomApiTest extends CiviUnitTestCase { $this->callAPISuccess('Mailing', 'create', array('name' => 'CiviMail', 'hash' => 'abx')); $result = $this->callAPISuccess('MailingProviderData', 'get', array('return' => array('mailing_identifier.name', 'contact_identifier', 'mailing_identifier'))); $this->assertEquals(1, $result['count']); - $this->assertEquals('xyzabx0000-00-00 00:00:00', $result['id']); - $this->assertEquals('xyzabx0000-00-00 00:00:00', $result['id']); + $this->assertEquals('xyzabx2017-01-01 00:00:00', $result['id']); + $this->assertEquals('xyzabx2017-01-01 00:00:00', $result['id']); $this->assertEquals(array( 'contact_identifier' => 'xyz', 'mailing_identifier' => 'abx', @@ -97,7 +97,7 @@ class api_v3_CustomApiTest extends CiviUnitTestCase { `mailing_identifier` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `event_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', - `recipient_action_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `recipient_action_datetime` timestamp NOT NULL DEFAULT '2017-01-01 00:00:00', `contact_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `is_civicrm_updated` TINYINT(4) DEFAULT '0', PRIMARY KEY (`contact_identifier`,`recipient_action_datetime`,`event_type`), -- 2.25.1