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

postgreSQL error when viewing Rate question type responses

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 1.9.10, 2.0.1
    • Component/s: Module: Questionnaire
    • Labels:
      None
    • Database:
      PostgreSQL
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE

      Description

      postgreSQL error when viewing Rate question type responses
      more details at:
      http://moodle.org/mod/forum/discuss.php?d=165652&parent=731216

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            mchurch Mike Churchward added a comment - - edited

            Joseph. I fixed these issues a long time ago in the 1.9 version (CONTRIB-1692) with new database statements. It appears that you undid them in subsequent commits, particularly on Dec. 8, 2010 (CONTRIB-2200) where you changed the SQL around line 540 to an older version which doesn't work on Postgres. Was this intentional? If so, why?

            Show
            mchurch Mike Churchward added a comment - - edited Joseph. I fixed these issues a long time ago in the 1.9 version ( CONTRIB-1692 ) with new database statements. It appears that you undid them in subsequent commits, particularly on Dec. 8, 2010 ( CONTRIB-2200 ) where you changed the SQL around line 540 to an older version which doesn't work on Postgres. Was this intentional? If so, why?
            Hide
            rezeau Joseph Rézeau added a comment -

            Mike,
            This is my comment for the changes to questionnaire/questiontypes/questiontypes.class.php on Dec. 8th, 2010:

            Removed obnoxious strtolower function causing problems with non ASCII characters :: CONTRIB-1692 :: CONTRIB-2194 :: CONTRIB-2375

            I'm afraid this is a long-standing problem which has never been properly resolved. I maintain that the strtolower function is causing problems with non ascii characters (French and other languages accents, etc.). I thought we had agreed to remove it entirely from Questionnaire files. I am still in the dark as to how removing that function affects PostgreSQL installations.

            Unfortunately I have no PostgreSQL installation to test things out.

            Joseph

            Show
            rezeau Joseph Rézeau added a comment - Mike, This is my comment for the changes to questionnaire/questiontypes/questiontypes.class.php on Dec. 8th, 2010: Removed obnoxious strtolower function causing problems with non ASCII characters :: CONTRIB-1692 :: CONTRIB-2194 :: CONTRIB-2375 I'm afraid this is a long-standing problem which has never been properly resolved. I maintain that the strtolower function is causing problems with non ascii characters (French and other languages accents, etc.). I thought we had agreed to remove it entirely from Questionnaire files. I am still in the dark as to how removing that function affects PostgreSQL installations. Unfortunately I have no PostgreSQL installation to test things out. Joseph
            Hide
            mchurch Mike Churchward added a comment - - edited

            Hi Joseph -

            The problem isn't with the strtolower function... When you did this change on January 3rd, 2011 (not the date I originally reported), you changed the SQL statements from:

            $sql = "SELECT c.id, c.content, a.average, a.num
                    FROM {$CFG->prefix}questionnaire_quest_choice c 
                    INNER JOIN 
                         (SELECT c2.id, AVG(a2.rank+1) AS average, COUNT(a2.response_id) AS num 
                          FROM {$CFG->prefix}questionnaire_quest_choice c2, {$CFG->prefix}questionnaire_{$this->response_table} a2 
                          WHERE c2.question_id = {$this->id} AND a2.question_id = {$this->id} AND a2.choice_id = c2.id AND a2.rank >= 0{$ridstr}
                          GROUP BY c2.id) a ON a.id = c.id"

            to:

            $sql = 'SELECT C.content, AVG(A.rank+1) AS average, COUNT(A.response_id) AS num '.
                   'FROM '.$CFG->prefix.'questionnaire_quest_choice C, '.
                           $CFG->prefix.'questionnaire_'.$this->response_table.' A '.
                   'WHERE C.question_id = '.$this->id.' AND A.question_id = '.$this->id.' AND '.
                         'A.choice_id = C.id AND A.rank >= 0'.$ridstr.' '.
                   'GROUP BY C.id';

            What you changed it to was what it was before CONTRIB-1692. I don't believe the fix you were doing in CONTRIB-2200 should have changed the SQL statement. Is it possible you used an older version of the questionnaire at that point?
            Regardless, what seems to have happened is that it got put back, and those changes then made it into 2.0.

            So, was the SQL change accidental?

            Show
            mchurch Mike Churchward added a comment - - edited Hi Joseph - The problem isn't with the strtolower function... When you did this change on January 3rd, 2011 (not the date I originally reported), you changed the SQL statements from: $sql = "SELECT c.id, c.content, a.average, a.num FROM {$CFG->prefix}questionnaire_quest_choice c INNER JOIN (SELECT c2.id, AVG(a2.rank+1) AS average, COUNT(a2.response_id) AS num FROM {$CFG->prefix}questionnaire_quest_choice c2, {$CFG->prefix}questionnaire_{$this->response_table} a2 WHERE c2.question_id = {$this->id} AND a2.question_id = {$this->id} AND a2.choice_id = c2.id AND a2.rank >= 0{$ridstr} GROUP BY c2.id) a ON a.id = c.id" to: $sql = 'SELECT C.content, AVG(A.rank+1) AS average, COUNT(A.response_id) AS num '. 'FROM '.$CFG->prefix.'questionnaire_quest_choice C, '. $CFG->prefix.'questionnaire_'.$this->response_table.' A '. 'WHERE C.question_id = '.$this->id.' AND A.question_id = '.$this->id.' AND '. 'A.choice_id = C.id AND A.rank >= 0'.$ridstr.' '. 'GROUP BY C.id'; What you changed it to was what it was before CONTRIB-1692 . I don't believe the fix you were doing in CONTRIB-2200 should have changed the SQL statement. Is it possible you used an older version of the questionnaire at that point? Regardless, what seems to have happened is that it got put back, and those changes then made it into 2.0. So, was the SQL change accidental?
            Hide
            rezeau Joseph Rézeau added a comment -

            Hi Mike,
            Thanks for that explanation. I'll have a look and report.
            Joseph

            Show
            rezeau Joseph Rézeau added a comment - Hi Mike, Thanks for that explanation. I'll have a look and report. Joseph
            Hide
            rezeau Joseph Rézeau added a comment -

            I've tested (in moodle 2.0) the sql request that existed prior to my changes

             
            "SELECT c.id, c.content, a.average, a.num
                    FROM {$CFG->prefix}questionnaire_quest_choice c 
                    INNER JOIN 
            etc.

            and it works fine.

            However, I do not know if the sql request that follows is OK for postgreSQL :

             $sql = 'SELECT C.content, SUM(A.rank+1) AS sum, COUNT(A.response_id) AS num '.
                                   'FROM '.$CFG->prefix.'questionnaire_quest_choice C, '.
                                           $CFG->prefix.'questionnaire_'.$this->response_table.' A '.
                                   'WHERE C.question_id = '.$this->id.' AND A.question_id = '.$this->id.' AND '.
                                         'A.choice_id = C.id AND A.rank >= 0'.$ridstr.' '.
                                   'GROUP BY C.id';

            Can you please check and make the necessary changes? Can you please also test in moodle 1.9?
            TIA
            Joseph
            PS.- Maybe you're right I mixed up some versions.

            Show
            rezeau Joseph Rézeau added a comment - I've tested (in moodle 2.0) the sql request that existed prior to my changes "SELECT c.id, c.content, a.average, a.num FROM {$CFG->prefix}questionnaire_quest_choice c INNER JOIN etc. and it works fine. However, I do not know if the sql request that follows is OK for postgreSQL : $sql = 'SELECT C.content, SUM(A.rank+1) AS sum, COUNT(A.response_id) AS num '. 'FROM '.$CFG->prefix.'questionnaire_quest_choice C, '. $CFG->prefix.'questionnaire_'.$this->response_table.' A '. 'WHERE C.question_id = '.$this->id.' AND A.question_id = '.$this->id.' AND '. 'A.choice_id = C.id AND A.rank >= 0'.$ridstr.' '. 'GROUP BY C.id'; Can you please check and make the necessary changes? Can you please also test in moodle 1.9? TIA Joseph PS.- Maybe you're right I mixed up some versions.
            Hide
            mchurch Mike Churchward added a comment -

            Reapplied SQL changes to fix for Postgres.

            Show
            mchurch Mike Churchward added a comment - Reapplied SQL changes to fix for Postgres.

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  25/Oct/10