Uploaded image for project: 'Plugins'
  1. Plugins
  2. CONTRIB-6436

CSV export fails using Postgres

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 3.1.1
    • 3.1.2
    • Module: Questionnaire
    • None

    Description

      If you export all responses from questionnaire using Postgres, the operation fails.
      This appears to be due to the boolean response type. The SQL that is created, writes a "choice_id" field. For all response types but boolean, this is an integer. For "boolean" this is a character.
      The error is something like:

      Error reading from database
       
      More information about this error
      Debug info: ERROR: UNION types character varying and integer cannot be matched
      LINE 44: qrb.choice_id, null AS response, 0 AS ran...
      ^
       
      SELECT qr.id || '_' || 'radiobuttons' || '_' || qrs.id AS id,
      qr.submitted, qr.complete, qr.grade, qr.username, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as userid, qr.id AS rid, qrs.question_id,
      qrs.choice_id, qro.response, 0 AS rank
      FROM mdl_questionnaire_response qr
      JOIN mdl_questionnaire_resp_single qrs ON qrs.response_id = qr.id
       
      AND qr.survey_id = $1 AND qr.complete = $2
      LEFT JOIN mdl_questionnaire_response_other qro ON qro.response_id = qr.id AND qro.choice_id = qrs.choice_id
      LEFT JOIN mdl_user u ON u.id = CAST(qr.username AS INT)
      UNION ALL
      SELECT qr.id || '_' || 'checkboxes' || '_' || qrm.id AS id,
      qr.submitted, qr.complete, qr.grade, qr.username, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as userid, qr.id AS rid, qrm.question_id,
      qrm.choice_id, qro.response, 0 AS rank
      FROM mdl_questionnaire_response qr
      JOIN mdl_questionnaire_resp_multiple qrm ON qrm.response_id = qr.id
       
      AND qr.survey_id = $3 AND qr.complete = $4
      LEFT JOIN mdl_questionnaire_response_other qro ON qro.response_id = qr.id AND qro.choice_id = qrm.choice_id
      LEFT JOIN mdl_user u ON u.id = CAST(qr.username AS INT)
      UNION ALL
      SELECT qr.id || '_' || 'ratescale' || '_' || qrr.id AS id,
      qr.submitted, qr.complete, qr.grade, qr.username, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as userid, qr.id AS rid, qrr.question_id,
      qrr.choice_id, null AS response, qrr.rank
      FROM mdl_questionnaire_response qr
      JOIN mdl_questionnaire_response_rank qrr
      ON qrr.response_id = qr.id
       
      AND qr.survey_id = $5 AND qr.complete = $6
      LEFT JOIN mdl_user u ON u.id = CAST(qr.username AS INT)
      UNION ALL
      SELECT qr.id || '_' || 'essaybox' || '_' || qrt.id AS id,
      qr.submitted, qr.complete, qr.grade, qr.username, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as userid, qr.id AS rid, qrt.question_id,
      0 AS choice_id, qrt.response, 0 AS rank
      FROM mdl_questionnaire_response qr
      JOIN mdl_questionnaire_response_text qrt
      ON qrt.response_id = qr.id
       
      AND qr.survey_id = $7 AND qr.complete = $8
      LEFT JOIN mdl_user u ON u.id = CAST(qr.username AS INT)
      UNION ALL
      SELECT qr.id || '_' || 'yesno' || '_' || qrb.id AS id,
      qr.submitted, qr.complete, qr.grade, qr.username, u.firstnamephonetic, u.lastnamephonetic, u.middlename, u.alternatename, u.firstname, u.lastname, u.username, u.department, u.institution, u.id as userid, qr.id AS rid, qrb.question_id,
      qrb.choice_id, null AS response, 0 AS rank
      FROM mdl_questionnaire_response qr
      JOIN mdl_questionnaire_response_bool qrb
      ON qrb.response_id = qr.id
       
      AND qr.survey_id = $9 AND qr.complete = $10
      LEFT JOIN mdl_user u ON u.id = CAST(qr.username AS INT)
      ORDER BY userid, id
      [array (
      0 => '1',
      1 => 'y',
      2 => '1',
      3 => 'y',
      4 => '1',
      5 => 'y',
      6 => '1',
      7 => 'y',
      8 => '1',
      9 => 'y',
      )]
      Error code: dmlreadexception
      Stack trace:
       
          line 474 of /lib/dml/moodle_database.php: dml_read_exception thrown
          line 244 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
          line 753 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
          line 2512 of /mod/questionnaire/questionnaire.class.php: call to pgsql_native_moodle_database->get_recordset_sql()
          line 2697 of /mod/questionnaire/questionnaire.class.php: call to questionnaire->get_survey_all_responses()
          line 490 of /mod/questionnaire/report.php: call to questionnaire->generate_csv()
      

      Attachments

        Activity

          People

            mchurch Mike Churchward
            mchurch Mike Churchward
            Mike Churchward
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              12/Sep/16