Hi,

We had upgrade issues upgrading from 3.5.15 to 3.9.12 failing on oracle based database because CLOB type is not compatible with distinct clause.

Column "mdfiles.source" is a CLOB in Oracle Moodle database

But the real problem is the misuse of "distinct f.*" in the sql command below.

As MDFILES table has a primary key, it will never have a duplicated line in a selection of all its columns.

Our upgrade concluded with no issues after we removed the DISTINCT from the command in the file "/lib/db/upgrade.php"

The command remais the same in the current master branch.

We suggest simply removing the DISTINCT clause from the command.

Thank you.

Luiz Felipe Rivabem

The errors we faced, with the problematic command in bold:

{}Default exception handler: Erro ao ler a base de dados Debug: ORA-00932: inconsistent datatypes: expected - got CLOB
SELECT DISTINCT f.*
                  FROM mdfiles f
             LEFT JOIN mdcontext c ON f.contextid = c.id
                 WHERE f.component = :o_component
                   AND f.filearea = :o_filearea
                   AND c.id IS NULL
[array (
  'o_component' => 'core_competency',
  'o_filearea' => 'userevidence',
)]
Error code: dmlreadexception
* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
* line 1156 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
* line 2541 of /lib/db/upgrade.php: call to oci_native_moodle_database->get_records_sql()
* line 1857 of /lib/upgradelib.php: call to xmldb_main_upgrade()
* line 187 of /admin/cli/upgrade.php: call to upgrade_core()