From 9a2444038619da241d33a1cc26f15ca6c1beb114 Mon Sep 17 00:00:00 2001
From: Paul Holden <paulh@moodle.com>
Date: Wed, 22 Jun 2022 23:09:03 +0100
Subject: [PATCH] MDL-72722 reportbuilder: Oracle support for distinct group
 concat.

Requirements for Oracle were increased to 19c for Moodle 4.1 - now
with support for DISTINCT keyword within the LISTAGG function:

https://livesql.oracle.com/apex/livesql/file/content_HT1O85E4BHSBWN93G1B3M8SI2.html
---
 .../classes/local/aggregation/groupconcatdistinct.php       | 5 ++++-
 .../tests/local/aggregation/groupconcatdistinct_test.php    | 6 +++---
 2 files changed, 7 insertions(+), 4 deletions(-)

diff --git a/reportbuilder/classes/local/aggregation/groupconcatdistinct.php b/reportbuilder/classes/local/aggregation/groupconcatdistinct.php
index eb4423cf81a..2009fcb69f8 100644
--- a/reportbuilder/classes/local/aggregation/groupconcatdistinct.php
+++ b/reportbuilder/classes/local/aggregation/groupconcatdistinct.php
@@ -51,6 +51,7 @@ class groupconcatdistinct extends groupconcat {
         $dbsupportedtype = in_array($DB->get_dbfamily(), [
             'mysql',
             'postgres',
+            'oracle',
         ]);
 
         return $dbsupportedtype && parent::compatible($columntype);
@@ -66,8 +67,10 @@ class groupconcatdistinct extends groupconcat {
     public static function get_field_sql(string $field, int $columntype): string {
         global $DB;
 
-        // DB limitations mean we only support MySQL and Postgres, and each handle it differently.
         $fieldsort = database::sql_group_concat_sort($field);
+
+        // Postgres handles group concatenation differently in that it requires the expression to be cast to char, so we can't
+        // simply pass "DISTINCT {$field}" to the {@see \moodle_database::sql_group_concat} method in all cases.
         if ($DB->get_dbfamily() === 'postgres') {
             $field = $DB->sql_cast_to_char($field);
             if ($fieldsort !== '') {
diff --git a/reportbuilder/tests/local/aggregation/groupconcatdistinct_test.php b/reportbuilder/tests/local/aggregation/groupconcatdistinct_test.php
index 74c5b2c12f5..8cdb079ccb9 100644
--- a/reportbuilder/tests/local/aggregation/groupconcatdistinct_test.php
+++ b/reportbuilder/tests/local/aggregation/groupconcatdistinct_test.php
@@ -20,6 +20,7 @@ namespace core_reportbuilder\local\aggregation;
 
 use core_reportbuilder_testcase;
 use core_reportbuilder_generator;
+use core_reportbuilder\local\report\column;
 use core_user\reportbuilder\datasource\users;
 
 defined('MOODLE_INTERNAL') || die();
@@ -44,9 +45,8 @@ class groupconcatdistinct_test extends core_reportbuilder_testcase {
     public function setUp(): void {
         global $DB;
 
-        $dbfamily = $DB->get_dbfamily();
-        if (!in_array($dbfamily, ['mysql', 'postgres'])) {
-            $this->markTestSkipped("Distinct group concatenation not supported in {$dbfamily}");
+        if (!groupconcatdistinct::compatible(column::TYPE_TEXT)) {
+            $this->markTestSkipped('Distinct group concatenation not supported in ' . $DB->get_dbfamily());
         }
     }
 
-- 
2.43.0