Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-29815

When I choose grading from navigation bar, I got "Debug info: ORA-00904: "SUMMARYSTATE": invalid identifier"

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1, 2.2
    • Fix Version/s: 2.1.3
    • Component/s: Quiz
    • Labels:
    • Environment:
      Oracle database
    • Database:
      Oracle
    • Testing Instructions:
      Hide

      1.Use Oracle as backend database.
      2.Create a quiz use some manually grading questions.
      3.From navigation menu, choose "Grading" in this quiz.
      4.Run database functional tests against the 5 drivers. Ignoring others, test that there is not errors related to test_get_records_sql_complicated tests.

      Also quickly check that it is not broken on at least one other DB.

      Show
      1.Use Oracle as backend database. 2.Create a quiz use some manually grading questions. 3.From navigation menu, choose "Grading" in this quiz. 4.Run database functional tests against the 5 drivers. Ignoring others, test that there is not errors related to test_get_records_sql_complicated tests. Also quickly check that it is not broken on at least one other DB.
    • Workaround:
      Hide

      !grade_err.png!

      Show
      !grade_err.png!
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      I created a quiz, all questions are graded manually. When I click the "Grading" menu from navigation bar, I get errors like this:

      Debug info: ORA-00904: "SUMMARYSTATE": invalid identifier
       
      SELECT
      qa.slot,
      qa.questionid,
      q.name,
      CASE qas.state
      WHEN 'notstarted' THEN 'inprogress'
      WHEN 'unprocessed' THEN 'inprogress'
      WHEN 'todo' THEN 'inprogress'
      WHEN 'invalid' THEN 'inprogress'
      WHEN 'complete' THEN 'inprogress'
      WHEN 'needsgrading' THEN 'needsgrading'
      WHEN 'finished' THEN 'autograded'
      WHEN 'gaveup' THEN 'autograded'
      WHEN 'gradedwrong' THEN 'autograded'
      WHEN 'gradedpartial' THEN 'autograded'
      WHEN 'gradedright' THEN 'autograded'
      WHEN 'manfinished' THEN 'manuallygraded'
      WHEN 'mangaveup' THEN 'manuallygraded'
      WHEN 'mangrwrong' THEN 'manuallygraded'
      WHEN 'mangrpartial' THEN 'manuallygraded'
      WHEN 'mangrright' THEN 'manuallygraded'
       
      END AS summarystate,
      COUNT(1) AS numattempts
       
      FROM m_quiz_attempts quiza
      JOIN m_question_attempts qa ON qa.questionusageid = quiza.uniqueid
      JOIN m_question_attempt_steps qas ON
      qas.id = (
      SELECT MAX(id)
      FROM m_question_attempt_steps
      WHERE questionattemptid = qa.id
      )
      JOIN m_question q ON q.id = qa.questionid
       
      WHERE
      quiza.quiz = :o_mangrquizid AND
      quiza.preview = 0 AND
      quiza.timefinish <> 0 AND
      qa.slot IN (:o_slot15,:o_slot16,:o_slot17,:o_slot18,:o_slot19)
       
      GROUP BY
      qa.slot,
      qa.questionid,
      q.name,
      q.id,
      summarystate
       
      ORDER BY
      qa.slot,
      qa.questionid,
      q.name,
      q.id
       
      [array (
      'o_mangrquizid' => '148',
      'o_slot15' => 2,
      'o_slot16' => 3,
      'o_slot17' => 4,
      'o_slot18' => 5,
      'o_slot19' => 6,
      )]

      The problem is Oracle don't support alias in group by statement, and it'll report "SUMMARYSTATE" as invalid identifier, because "SUMMARYSTATE" is alias. I changed some code in question/engine/datalib.php:

      question/engine/datalib.php line347-377

      SELECT
          qa.slot,
          qa.questionid,
          q.name,
          CASE qas.state
              {$this->full_states_to_summary_state_sql()}
          END AS summarystate,
          COUNT(1) AS numattempts
       
      FROM {$qubaids->from_question_attempts('qa')}
      JOIN {question_attempt_steps} qas ON
              qas.id = {$this->latest_step_for_qa_subquery()}
      JOIN {question} q ON q.id = qa.questionid
       
      WHERE
          {$qubaids->where()} AND
          qa.slot $slottest
       
      GROUP BY
          qa.slot,
          qa.questionid,
          q.name,
          q.id,
          CASE qas.state                                                                                                                            
          WHEN 'notstarted' THEN 'inprogress'                                                                                                       
          WHEN 'unprocessed' THEN 'inprogress'                                                                                                      
          WHEN 'todo' THEN 'inprogress'                                                                                                             
          WHEN 'invalid' THEN 'inprogress'                                                                                                          
          WHEN 'complete' THEN 'inprogress'                                                                                                         
          WHEN 'needsgrading' THEN 'needsgrading'                                                                                                   
          WHEN 'finished' THEN 'autograded'                                                                                                         
          WHEN 'gaveup' THEN 'autograded'                                                                                                           
          WHEN 'gradedwrong' THEN 'autograded'                                                                                                      
          WHEN 'gradedpartial' THEN 'autograded'                                                                                                    
          WHEN 'gradedright' THEN 'autograded'                                                                                                      
          WHEN 'manfinished' THEN 'manuallygraded'                                                                                                  
          WHEN 'mangaveup' THEN 'manuallygraded'                                                                                                    
          WHEN 'mangrwrong' THEN 'manuallygraded'                                                                                                   
          WHEN 'mangrpartial' THEN 'manuallygraded'                                                                                                 
          WHEN 'mangrright' THEN 'manuallygraded'                                                                                                   
          END      
       
      ORDER BY
          qa.slot,
          qa.questionid,
          q.name,
          q.id
              ", $params + $qubaids->from_where_params());

      Things go wright now. But I still waiting Tim provide an elegant solution.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

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

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    28/Nov/11