Add-ons
  1. Add-ons
  2. CONTRIB-2711

postgreSQL error when viewing Rate question type responses

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical 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
    • Rank:
      23441

      Description

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

        Activity

        Hide
        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
        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
        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
        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
        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
        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
        Joseph Rézeau added a comment -

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

        Show
        Joseph Rézeau added a comment - Hi Mike, Thanks for that explanation. I'll have a look and report. Joseph
        Hide
        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
        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
        Mike Churchward added a comment -

        Reapplied SQL changes to fix for Postgres.

        Show
        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:

              Development