Errors in SQL queries

[tonyjbutler]

We use PostgreSQL and the following error is being logged frequently:

column "count_feedback" does not exist at character 1453

which refers to the query:

SELECT cs.id AS submissionid, COUNT(f.id) AS count_feedback
FROM mdl_coursework_submissions cs LEFT JOIN
mdl_coursework_feedbacks f ON cs.id = f.submissionid
, mdl_coursework_allocation_pairs cap
WHERE cs.finalised = 1
AND cs.courseworkid = $1
AND (f.assessorid != $2 OR f.assessorid IS NULL)
AND cap.courseworkid = cs.courseworkid
AND cap.allocatableid = cs.allocatableid
AND cap.allocatabletype = cs.allocatabletype
AND cap.assessorid = $3
AND cs.id NOT IN (SELECT sub.id FROM
mdl_coursework_feedbacks feed JOIN
mdl_coursework_submissions sub ON sub.id = feed.submissionid
WHERE assessorid = $4 AND sub.courseworkid= $5)
GROUP BY cs.id
HAVING (count_feedback < $6)

Less frequently, we're also seeing this error:

syntax error at or near "`" at character 39

which refers to:

update mdl_coursework_submissions set `allocatableuser`=0, `allocatablegroup`=0

I suspect PostgreSQL isn't the only database that doesn't support the use of backticks in SQL queries. If these are needed for e.g. MySQL it's worth bearing in mind that Moodle's database abstraction library will insert them automatically where needed, so they shouldn't be included in plugin code.

Thanks,
Tony

[tonyjbutler]

There should also be no hardcoded database prefixes (not everyone will be using the default 'mdl_'). I've just spotted some in the query at

open-mod_coursework/classes/models/outstanding_marking.php

Line 252 in dd308a5

LEFT JOIN `mdl_coursework_sample_set_mbrs` ssm

(which incidentally also contains backticks).