This query was originally written by Michael Gerszewski. You can use it to identify duplicate questions in a course's question bank that cause the following error when importing a course: "Failed to find an answer..." The core bug is MDL-83541 and will hopefully be fixed in Spring 2025. The likely manual resolution is to clean up the question ba…

duplicate-questions.sql

with qincourse as (
    SELECT 
        q.id AS question_id,
        q.name AS question_name,
        q.stamp as question_stamp,
        qc.id AS quiz_category_id,
        qc.name AS quiz_category_name,
        c.id AS course_id,
        c.fullname AS course_name
        , row_number() over (partition by q.stamp order by q.id) as row_num
    FROM 
        prefix_question q
    JOIN 
        prefix_question_versions qv ON q.id = qv.questionid
    JOIN 
        prefix_question_bank_entries qbe ON qv.questionbankentryid = qbe.id
    JOIN 
        prefix_question_categories qc ON qbe.questioncategoryid = qc.id
    JOIN 
        prefix_context ctx ON qc.contextid = ctx.id
    JOIN 
        prefix_course c ON ctx.instanceid = c.id
    WHERE 
        c.id = :courseid
)
SELECT 
    q1.question_id
    , q1.question_name
    , length(q1.question_name)
    , q1.row_num
    , q1.question_stamp
    , q1.quiz_category_id
    , q1.quiz_category_name
    , q1.course_id
    , q1.course_name
    , case when q1.row_num > 1 then concat('dup', row_num-1, '.', q1.question_stamp) end newstamp
    , case when q1.row_num > 1 then concat(q1.question_name, ' (duplicate ', row_num-1, ')') end newname
FROM qincourse q1
JOIN (
    SELECT question_stamp, COUNT(question_id) as count
    FROM qincourse
    GROUP BY question_stamp
    HAVING count(question_id) > 1
) q2 ON q1.question_stamp = q2.question_stamp
WHERE q1.row_num > 1
ORDER BY q1.question_name
, q1.question_stamp
, q1.row_num