SQL error in the Adaptable theme on a SQLSRV driven Moodle site

SQL error in the Adaptable theme on a SQLSRV driven Moodle site

by Luis de Vasconcelos -
Number of replies: 1

I'm testing the Adaptable Theme version: $plugin->release = '1.7.1';

On a Moodle 3.5.2+ site:
$version  = 2018051702.11;
$release  = '3.5.2+ (Build: 20181027)';

Moodle runs on:

  • Windows Server 2012 R2 server OS
  • IIS v8.5.9600
  • PHP v7.2.9 NTS
  • Microsoft SQLSRV PHP Driver v5.3.0+11108
  • Microsoft SQL Server 2016 SP1
The problem seems to be that the $sql generated in \Moodle\theme\adaptable\classes\activity.php from line 809 is invalid SQL for MS SQL Server databaases.

See: https://bitbucket.org/covuni/moodle-theme_adaptable/src/85aac4e07c0c319ea70b4a65c185c5afbeb94eaa/classes/activity.php?at=master&fileviewer=file-view-default#activity.php-809

It generates this SQL code:

```
#!sql
SELECT a.id AS instanceid, st.*
FROM mdl_choice_answers st
JOIN mdl_choice a
ON a.id = st.choiceid
-- Get only the most recent submission.
JOIN (
    SELECT choiceid AS modid, MAX(id) AS maxattempt
    FROM mdl_choice_answers
    WHERE userid = '79448'
    GROUP BY modid
) AS smx
ON smx.modid = st.choiceid
AND smx.maxattempt = st.id
WHERE a.course = '997'
AND st.userid = '79448'
ORDER BY choiceid DESC, st.id DESC

That  SQL throws an error on my SQL Server Moodle site saying:

Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'modid'.

The PHPErrors.log file has this message:

[06-Nov-2018 15:22:24 Etc/GMT-2] Default exception handler: Error reading from database Debug: SQLState: 42S22<br>
Error Code: 207<br>
Message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'modid'.<br>

-- Snap sql
                SELECT a.id AS instanceid, st.*
                    FROM mdl_choice_answers st

                    JOIN mdl_choice a
                    ON a.id = st.choiceid

                    -- Get only the most recent submission.
                    JOIN (SELECT choiceid AS modid, MAX(id) AS maxattempt
                    FROM mdl_choice_answers
                    WHERE userid = '79448'
                    GROUP BY modid) AS smx
                    ON smx.modid = st.choiceid
                    AND smx.maxattempt = st.id

                    WHERE a.course = '997'
                    AND st.userid = '79448'
                    ORDER BY choiceid DESC, st.id DESC
[array (
  0 => '79448',
  1 => '997',
  2 => '79448',
)]
Error code: dmlreadexception
* line 486 of \lib\dml\moodle_database.php: dml_read_exception thrown
* line 324 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
* line 431 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
* line 896 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
* line 972 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
* line 826 of \theme\adaptable\classes\activity.php: call to sqlsrv_native_moodle_database->get_records_sql()
* line 135 of \theme\adaptable\classes\activity.php: call to theme_adaptable\activity::get_submission_row()
* line 251 of \theme\adaptable\classes\activity.php: call to theme_adaptable\activity::std_meta()
* line ? of unknownfile: call to theme_adaptable\activity::choice_meta()
* line 43 of \theme\adaptable\classes\activity.php: call to call_user_func()
* line 743 of \theme\adaptable\classes\output\core\course_renderer.php: call to theme_adaptable\activity::module_meta()
* line 703 of \theme\adaptable\classes\output\core\course_renderer.php: call to theme_adaptable\output\core\course_renderer->course_section_cm_get_meta()
* line 846 of \course\renderer.php: call to theme_adaptable\output\core\course_renderer->course_section_cm()
* line 1039 of \course\renderer.php: call to core_course_renderer->course_section_cm_list_item()
* line 914 of \course\format\renderer.php: call to core_course_renderer->course_section_cm_list()
* line 57 of \course\format\topics\format.php: call to format_section_renderer_base->print_multiple_section_page()
* line 280 of \course\view.php: call to require()

The problem seems to be the GROUP BY modid alias. SQL Server doesn't seem to like the modid alias used in that group by.

If I replace the modid alias with the actual choiceid column name it seems to be a valid SQL query in SQL Server:

SELECT a.id AS instanceid, st.*
FROM mdl_choice_answers st
JOIN mdl_choice a
ON a.id = st.choiceid
-- Get only the most recent submission.
JOIN (
	SELECT choiceid AS modid, MAX(id) AS maxattempt
	FROM mdl_choice_answers
	WHERE userid = '79448'
GROUP BY choiceid
) AS smx
ON smx.modid = st.choiceid
AND smx.maxattempt = st.id
WHERE a.course = '997'
AND st.userid = '79448' 
ORDER BY choiceid DESC, st.id DESC
But how do I update the code in \theme\adaptable\classes\activity.php so that it generates that valid SQL for SQL Server?

Average of ratings: -
In reply to Luis de Vasconcelos

Re: SQL error in the Adaptable theme on a SQLSRV driven Moodle site

by Luis de Vasconcelos -

It seems like I can get it to work by changing the hardcoded "modid" alias to the "$modfield" variable in \classes\activity.php:

@@ +812,7 -812,7 @@
                    JOIN (SELECT $modfield AS modid, MAX(id) AS maxattempt
                    FROM {".$submissiontable."}
                    WHERE userid = ?
+                     GROUP BY $modfield) AS smx
-                     GROUP BY modid) AS smx
                    ON smx.modid = st.$modfield
                    AND smx.maxattempt = st.id

Some initial testing seems to suggest that it works. Is this the right way to fix it, or will this cause other regressions?

Average of ratings: Useful (1)