From c24ccde07f1e4036788ccd9480327fc7d59c6089 Mon Sep 17 00:00:00 2001
From: "Eloy Lafuente (stronk7)" <stronk7@moodle.org>
Date: Sun, 28 Aug 2011 22:12:40 +0200
Subject: [PATCH] MDL-26819 quiz statistics - use cross-db GROUP BY expression

---
 lib/dml/simpletest/testdml.php        | 17 +++++++++++++++++
 mod/quiz/report/statistics/report.php |  2 +-
 2 files changed, 18 insertions(+), 1 deletion(-)

diff --git a/lib/dml/simpletest/testdml.php b/lib/dml/simpletest/testdml.php
index 7bec4a99bfc..40ba8557a28 100644
--- a/lib/dml/simpletest/testdml.php
+++ b/lib/dml/simpletest/testdml.php
@@ -3497,6 +3497,23 @@ class dml_test extends UnitTestCase {
         $DB->insert_record($tablename, array('course' => 5, 'content' => 'hello', 'name'=>'def'));
         $DB->insert_record($tablename, array('course' => 2, 'content' => 'universe', 'name'=>'abc'));
 
+        // test grouping by expressions in the query. MDL-26819. Note that there are 4 ways:
+        // - By column position (GROUP by 1) - Not supported by mssql & oracle
+        // - By column name (GROUP by course) - Supported by all, but leading to wrong results
+        // - By column alias (GROUP by casecol) - Not supported by mssql & oracle
+        // - By complete expression (GROUP BY CASE ...) - 100% cross-db, this test checks it
+        $sql = "SELECT (CASE WHEN course = 3 THEN 1 ELSE 0 END) AS casecol,
+                       COUNT(1) AS countrecs,
+                       MAX(name) AS maxname
+                  FROM {{$tablename}}
+              GROUP BY CASE WHEN course = 3 THEN 1 ELSE 0 END
+              ORDER BY casecol DESC";
+        $result = array(
+                1 => (object)array('casecol' => 1, 'countrecs' => 2, 'maxname' => 'xyz'),
+                0 => (object)array('casecol' => 0, 'countrecs' => 2, 'maxname' => 'def'));
+        $records = $DB->get_records_sql($sql, null);
+        $this->assertEqual($result, $records);
+
         // test limits in queries with DISTINCT/ALL clauses and multiple whitespace. MDL-25268
         $sql = "SELECT   DISTINCT   course
                   FROM {{$tablename}}
diff --git a/mod/quiz/report/statistics/report.php b/mod/quiz/report/statistics/report.php
index d7875e3dea8..93192ce8335 100644
--- a/mod/quiz/report/statistics/report.php
+++ b/mod/quiz/report/statistics/report.php
@@ -620,7 +620,7 @@ class quiz_statistics_report extends quiz_default_report {
                     SUM(sumgrades) AS total
                 FROM $fromqa
                 WHERE $whereqa
-                GROUP BY attempt = 1", $qaparams);
+                GROUP BY CASE WHEN attempt = 1 THEN 1 ELSE 0 END", $qaparams);
 
         if (!$attempttotals) {
             return $this->get_emtpy_stats($questions);
-- 
2.11.4.GIT