From 403b1c119daa33e71cd09610eda1629b8b36da45 Mon Sep 17 00:00:00 2001
From: Seamus Lee <seamuslee001@gmail.com>
Date: Sat, 2 Sep 2017 14:45:46 +1000
Subject: [PATCH] Add test of sqlModes being set correctly

Revert to only running ONLY_FULL_GROUP_BY mode on mysql 5.7
---
 CRM/Contact/BAO/Query.php          |  2 +-
 CRM/Core/DAO.php                   |  7 +++++--
 tests/phpunit/CRM/Core/DAOTest.php | 13 +++++++++++++
 3 files changed, 19 insertions(+), 3 deletions(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index ab28ca3de9..35be41f176 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -4716,7 +4716,7 @@ civicrm_relationship.is_permission_a_b = 0
     $sqlMode = CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode');
 
     //return if ONLY_FULL_GROUP_BY is not enabled.
-    if (!version_compare($mysqlVersion, '5.7', '<') && !empty($sqlMode) && in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) {
+    if (CRM_Core_DAO::supportsFullGroupBy() && !empty($sqlMode) && in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) {
       $regexToExclude = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i';
       foreach ($selectClauses as $key => $val) {
         $aliasArray = preg_split('/ as /i', $val);
diff --git a/CRM/Core/DAO.php b/CRM/Core/DAO.php
index 1f16248a9d..4f3bb20633 100644
--- a/CRM/Core/DAO.php
+++ b/CRM/Core/DAO.php
@@ -121,10 +121,13 @@ class CRM_Core_DAO extends DB_DataObject {
     $currentSqlMode = CRM_Core_DAO::singleValueQuery("SELECT @@GLOBAL.sql_mode");
     $currentModes = explode(',', $currentSqlMode);
     if (CRM_Utils_Constant::value('CIVICRM_MYSQL_STRICT', CRM_Utils_System::isDevelopment())) {
+      if (self::supportsFullGroupBy() && !in_array('ONLY_FULL_GROUP_BY', $currentModes)) {
+        $currentModes[] = 'ONLY_FULL_GROUP_BY';
+      }
       if (!in_array('STRICT_TRANS_TABLES', $currentModes)) {
-        $modes = array_merge(array('STRICT_TRANS_TABLES'), $currentModes);
-        CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", array(1 => array(implode(',', $modes), 'String')));
+        $currentModes = array_merge(array('STRICT_TRANS_TABLES'), $currentModes);
       }
+      CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", array(1 => array(implode(',', $currentModes), 'String')));
     }
     CRM_Core_DAO::executeQuery('SET NAMES utf8');
     CRM_Core_DAO::executeQuery('SET @uniqueID = %1', array(1 => array(CRM_Utils_Request::id(), 'String')));
diff --git a/tests/phpunit/CRM/Core/DAOTest.php b/tests/phpunit/CRM/Core/DAOTest.php
index c5cf1c1998..49879fd0ff 100644
--- a/tests/phpunit/CRM/Core/DAOTest.php
+++ b/tests/phpunit/CRM/Core/DAOTest.php
@@ -380,4 +380,17 @@ class CRM_Core_DAOTest extends CiviUnitTestCase {
     $this->checkArrayEquals($contactsFetchedFromBufferedQuery, $contactsFetchedFromUnbufferedQuery);
   }
 
+  /**
+   * Test that known sql modes are present in session.
+   */
+  public function testSqlModePresent() {
+    $currentSqlModes = CRM_Core_DAO::singleValueQuery("SELECT @@sql_mode");
+    $sqlModes = explode(',', $currentSqlModes);
+    // assert we have strict trans
+    $this->assertContains('STRICT_TRANS_TABLES', $sqlModes);
+    if (CRM_Core_DAO::supportsFullGroupBy()) {
+      $this->assertContains('ONLY_FULL_GROUP_BY', $sqlModes);
+    }
+  }
+
 }
-- 
2.25.1