[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'LIMIT'

[arndesmet]

After checking answers of a question, the following error occurs:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'LIMIT'

System:

• Moodle 3.9.7+ (Build: 20210617)
• Microsoft SQL Server
• StudentQuiz v4.5.0 (2021071300)

Full Debug Info:

Debug info: SQLState: 42000<br>
Error Code: 169<br>
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique.<br>

SELECT c.*
FROM mdl_studentquiz_comment c

WHERE c.parentid = '0' AND c.questionid = '274695'
ORDER BY c.created asc, c.created DESC

[array (
0 => 0,
1 => '274695',
)]
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 397 of \mod\studentquiz\classes\commentarea\container.php: call to sqlsrv_native_moodle_database->get_records_sql()
line 291 of \mod\studentquiz\classes\commentarea\container.php: call to mod_studentquiz\commentarea\container->query_comments()
line 280 of \mod\studentquiz\classes\commentarea\container.php: call to mod_studentquiz\commentarea\container->fetch()
line 1979 of \mod\studentquiz\renderer.php: call to mod_studentquiz\commentarea\container->fetch_all()
line 1535 of \mod\studentquiz\renderer.php: call to mod_studentquiz_comment_renderer->render_comment_area()
line 240 of \mod\studentquiz\attempt.php: call to mod_studentquiz_attempt_renderer->render_comment()

[dionysius]

Hi @arndesmet, as per StudentQuiz 4.5.0 we introduced experimental MSSQL support. As per README:

SQL Server support is only experimental, as we have no environment to run automated tests on it yet. It can break on any minor update and we rely on feedback and input from the community.

I'd happily review a PR, I have no MSSQL server at hand to do anything.

[timhunt]

It is possible to get github actions (or is the key thing moodle-plugin-ci) to run the automated test on MSSQL?

[dionysius]

I haven't looked into whether it is possible in github actions at all, but i forwarded the request to moodle-plugin-ci a while ago: moodlehq/moodle-plugin-ci#92

I think all plugins would benefit from mssql testing, so best would be starting in a common place, requiring githib actions can run mssql at all.