MS SQL issue with Absentee report

[luisdev]

I installed the Attendance module and block on my Moodle 3.9.17 test server.

After I captured some test data and played with this very cool plugin, I went to the Absentee report on https://mymoodle/mod/attendance/absentee.php. I immediately got a "Error reading from database" crash:

The debug output is:

Debug info: SQLState: 42000<br>
Error Code: 156<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'percent'.<br>
SQLState: 42000<br>
Error Code: 156<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'percent'.<br>
SQLState: 42000<br>
Error Code: 102<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'stm'.<br>

SELECT DISTINCT CAST(cmid AS NVARCHAR(255)) + CAST(userid AS NVARCHAR(255)) as id, firstnamephonetic,lastnamephonetic,middlename,alternatename,firstname,lastname,idnumber, department, institution, country, aid, cmid, courseid, aname, coursename, userid,
numtakensessions, percent, MAX(timesent) as timesent
FROM (SELECT CAST(cm.id AS NVARCHAR(255)) + CAST(atl.studentid AS NVARCHAR(255)) + CAST(n.id AS NVARCHAR(255)) as uniqueid, a.id as aid, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,u.idnumber, u.department, u.institution, u.country, a.name as aname, cm.id as cmid, c.id as courseid,
c.fullname as coursename, atl.studentid AS userid, n.id as notifyid, n.warningpercent, n.emailsubject,
n.emailcontent, n.emailcontentformat, n.emailuser, n.thirdpartyemails, n.warnafter, n.maxwarn,
COUNT(DISTINCT ats.id) AS numtakensessions, SUM(stg.grade) AS points, SUM(stm.maxgrade) AS maxpoints,
COUNT(DISTINCT ns.id) as nscount, MAX(ns.timesent) as timesent,
SUM(stg.grade) / SUM(stm.maxgrade) AS percent
FROM mdl_attendance_sessions ats
JOIN mdl_attendance a ON a.id = ats.attendanceid
JOIN mdl_course_modules cm ON cm.instance = a.id
JOIN mdl_course c on c.id = cm.course
JOIN mdl_modules md ON md.id = cm.module AND md.name = 'attendance'
JOIN mdl_attendance_log atl ON (atl.sessionid = ats.id)
JOIN mdl_user u ON (u.id = atl.studentid)
JOIN mdl_attendance_statuses stg ON (stg.id = atl.statusid AND stg.deleted = 0 AND stg.visible = 1)
JOIN mdl_attendance_warning n ON n.idnumber = a.id
LEFT JOIN mdl_attendance_warning_done ns ON ns.notifyid = n.id AND ns.userid = atl.studentid
JOIN (SELECT attendanceid, setnumber, MAX(grade) AS maxgrade
FROM mdl_attendance_statuses
WHERE deleted = 0
AND visible = 1
GROUP BY attendanceid, setnumber) stm
ON (stm.setnumber = ats.statusset AND stm.attendanceid = ats.attendanceid)
LEFT JOIN mdl_groups_members gm ON (gm.userid = atl.studentid AND gm.groupid = ats.groupid)
WHERE ats.absenteereport = 1 AND (ats.groupid = 0 or gm.id is NOT NULL)
GROUP BY uniqueid, a.id, a.name, a.course, c.fullname, atl.studentid, n.id, n.warningpercent,
n.emailsubject, n.emailcontent, n.emailcontentformat, n.warnafter, n.maxwarn,
n.emailuser, n.thirdpartyemails, cm.id, c.id, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,u.idnumber, u.department, u.institution, u.country, ns.userid
HAVING n.warnafter <= COUNT(DISTINCT ats.id) AND n.warningpercent > ((SUM(stg.grade) / SUM(stm.maxgrade)) * 100)

ORDER BY timesent DESC) as m
GROUP BY id, aid, cmid, courseid, aname, userid, numtakensessions,
percent, firstnamephonetic,lastnamephonetic,middlename,alternatename,firstname,lastname,idnumber, department, institution, country, coursename ORDER BY timesent DESC
[array (
)]
Error code: dmlreadexception
Stack trace:

    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 900 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
    line 976 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
    line 1067 of \mod\attendance\locallib.php: call to sqlsrv_native_moodle_database->get_records_sql()
    line 134 of \mod\attendance\absentee.php: call to attendance_get_users_to_notify()

My Moodle server and version info:

Moodle version: 3.9.17+ (Build: 20221011)
PHP version: 7.4.32 NTS
PHP mode: FastCGI
OS: Windows Server 2016
Web Server: IIS 10.0.17763.1
Database: Microsoft SQL Server 2016 - 13.0.7024.30 on Windows Server 2019

Mod Attendance version:
$plugin->component = 'mod_attendance';
$plugin->version = 2021050702;
$plugin->release = '3.9.3';

Block Attendance version:
$plugin->component = 'block_attendance';
$plugin->version = 2022112500;
$plugin->release = '3.2.6';

When I run that sql code in SQL Server Management Studio it throws these errors:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'percent'.

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'percent'.

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'stm'.

It seems like "percent" is a reserved word in SQL Server, so it shouldn't be used as a column alias in a sql query. Can that column alias be renamed?

I'm not sure what the "Incorrect syntax near 'stm'" error is.

[danmarsden]

Hey @luisdev - thanks for the report. I don't actively support MS Sql but happy to receive a pull request with a fix :-)

[arborrow]

@danmarsden I have attendance on a production server currently using MSSQL. I will try to test and confirm the issue and see if I can figure out a solution. I recently had an MSSQL issue on a plugin that Tim Hunt maintains. I'm hoping to eventually migrate the site to MySQL but until then it gives me motivation to look at issues like this one ;) Peace - Anthony