Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.5, 2.1.2, 2.2
    • Fix Version/s: 2.0.6, 2.1.3
    • Component/s: Gradebook
    • Labels:
    • Environment:
      Windows 2003, IIS6, PHP 5.3.0, SQL Server 2008, Moodle 2.0.3+
    • Database:
      PostgreSQL, Microsoft SQL
    • Testing Instructions:
      Hide

      Requires testing on postgres or SQL Server

      As an admin go into a course with at least one student and at least one gradeable activity. Go into the gradebook and via the gradebook nav drop down go to the settings and ensure that average column in the user report is set to "show"

      From course admin > Users - Enrolled users
      Click on the picture located next to Student ie their profile pic to get to their profile
      in left hand menu, select Login as - continue
      select Grades

      grade averages should be displayed.

      Show
      Requires testing on postgres or SQL Server As an admin go into a course with at least one student and at least one gradeable activity. Go into the gradebook and via the gradebook nav drop down go to the settings and ensure that average column in the user report is set to "show" From course admin > Users - Enrolled users Click on the picture located next to Student ie their profile pic to get to their profile in left hand menu, select Login as - continue select Grades grade averages should be displayed.
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE
    • Pull Master Branch:
      MDL-27703_user_report_sql
    • Rank:
      17361

      Description

      Moodle throws an exception when accessing the user report page within the grades.

      The following error is thrown from the page https://shymoodle.upmc.com/grade/report/user/index.php?id=6:

      Default exception handler: Error reading from database Debug: Incorrect syntax near the keyword 'WHERE'. SELECT gi.id, COUNT(u.id) AS count FROM moodle_grade_items gi JOIN moodle_user u JOIN (SELECT DISTINCT eu2_u.id FROM moodle_user eu2_u JOIN moodle_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id JOIN moodle_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = ?) WHERE eu2_u.deleted = 0 AND eu2_u.id <> ?) je ON je.id = u.id JOIN ( SELECT DISTINCT ra.userid FROM moodle_role_assignments ra WHERE ra.roleid IN (?,?,?,?,?,?,?) AND ra.contextid IN (205,3,1) ) rainner ON rainner.userid = u.id LEFT JOIN moodle_grade_grades gg ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0) WHERE gi.courseid = ? AND u.deleted = 0 AND gg.finalgrade IS NULL GROUP BY gi.id [array ( 0 => '6', 1 => '1', 2 => '6', 3 => '4', 4 => '2', 5 => '5', 6 => '7', 7 => '3', 8 => '1', 9 => 6, )] * line 391 of \lib\dml\moodle_database.php: dml_read_exception thrown * line 255 of \lib\dml\mssql_native_moodle_database.php: call to moodle_database->query_end() * line 710 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end() * line 739 of \lib\dml\mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql() * line 670 of \grade\report\user\lib.php: call to mssql_native_moodle_database->get_records_sql() * line 217 of \grade\report\user\lib.php: call to grade_report_user->calculate_averages() * line 121 of \grade\report\user\index.php: call to grade_report_user->__construct() 
      

      Replication instructions:

      1. Login to moodle dev
      2. under MY Courses, select "A New Start" course
      3. under Settings (top left hand side of page) select Grades (logged in as a student, this is where the error occurs, for error as admin, please continue)
      4. Select user report (Top left of page) (error will appear)

      or

      1. login as a student:
      2. In the new start course
      3. select Users - Enrolled users (menu on left)
      4. Click on the picture located next to StudentSON
      5. on left hand menu, select Login as - continue
      6. select Grades, (Error message)

        Issue Links

          Activity

          Hide
          Michael de Raadt added a comment -

          Thanks for reporting this.

          I tried to replicate this on 2.0 stable and the latest master, but I did not encounter the same problem. Perhaps this is something specific to your instance, or perhaps to MS SQL. Can you try this on a fresh install to see if the same problem arises? To find the actual problem, your exploration of the issue with help us greatly.

          Michael;

          Show
          Michael de Raadt added a comment - Thanks for reporting this. I tried to replicate this on 2.0 stable and the latest master, but I did not encounter the same problem. Perhaps this is something specific to your instance, or perhaps to MS SQL. Can you try this on a fresh install to see if the same problem arises? To find the actual problem, your exploration of the issue with help us greatly. Michael;
          Hide
          Neal Holtz added a comment -

          I can duplicate this using MOODLE_21_STABLE, Postgres 8.4, Ubuntu 11.04. It occurs when the user report is set to display the average. To duplicate:

          Course / Grades / Course Grade Settings under User Report

          if Show Average is set to 'Show'

          you cannot display a user report due to an SQL syntax error (below). If set to 'Hide' its OK.

          ERROR: syntax error at or near "WHERE"
          LINE 18: WHERE gi.courseid = $4
          ^
          SELECT gi.id, COUNT(u.id) AS count
          FROM tmdl_grade_items gi
          JOIN tmdl_user u
          JOIN (SELECT DISTINCT eu2_u.id
          FROM tmdl_user eu2_u
          JOIN tmdl_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id
          JOIN tmdl_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = $1)
          WHERE eu2_u.deleted = 0 AND eu2_u.id <> $2) je ON je.id = u.id
          JOIN (
          SELECT DISTINCT ra.userid
          FROM tmdl_role_assignments ra
          WHERE ra.roleid = $3
          AND ra.contextid IN (14,3,1)
          ) rainner ON rainner.userid = u.id
          LEFT JOIN tmdl_grade_grades gg
          ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0)

          WHERE gi.courseid = $4
          AND u.deleted = 0
          AND gg.finalgrade IS NULL

          GROUP BY gi.id
          [array (
          0 => '2',
          1 => '1',
          2 => '5',
          3 => 2,
          )]
          Stack trace:
          line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
          line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
          line 678 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
          line 670 of /grade/report/user/lib.php: call to pgsql_native_moodle_database->get_records_sql()
          line 217 of /grade/report/user/lib.php: call to grade_report_user->calculate_averages()
          line 121 of /grade/report/user/index.php: call to grade_report_user->__construct()

          Show
          Neal Holtz added a comment - I can duplicate this using MOODLE_21_STABLE, Postgres 8.4, Ubuntu 11.04. It occurs when the user report is set to display the average. To duplicate: Course / Grades / Course Grade Settings under User Report if Show Average is set to 'Show' you cannot display a user report due to an SQL syntax error (below). If set to 'Hide' its OK. ERROR: syntax error at or near "WHERE" LINE 18: WHERE gi.courseid = $4 ^ SELECT gi.id, COUNT(u.id) AS count FROM tmdl_grade_items gi JOIN tmdl_user u JOIN (SELECT DISTINCT eu2_u.id FROM tmdl_user eu2_u JOIN tmdl_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id JOIN tmdl_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = $1) WHERE eu2_u.deleted = 0 AND eu2_u.id <> $2) je ON je.id = u.id JOIN ( SELECT DISTINCT ra.userid FROM tmdl_role_assignments ra WHERE ra.roleid = $3 AND ra.contextid IN (14,3,1) ) rainner ON rainner.userid = u.id LEFT JOIN tmdl_grade_grades gg ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0) WHERE gi.courseid = $4 AND u.deleted = 0 AND gg.finalgrade IS NULL GROUP BY gi.id [array ( 0 => '2', 1 => '1', 2 => '5', 3 => 2, )] Stack trace: line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end() line 678 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end() line 670 of /grade/report/user/lib.php: call to pgsql_native_moodle_database->get_records_sql() line 217 of /grade/report/user/lib.php: call to grade_report_user->calculate_averages() line 121 of /grade/report/user/index.php: call to grade_report_user->__construct()
          Hide
          Michael de Raadt added a comment -

          Thanks for the additional details, Neal.

          Show
          Michael de Raadt added a comment - Thanks for the additional details, Neal.
          Hide
          David Boyer added a comment -

          I'm seeing the same thing on 2.1.2-20111019. If I try to view a User Grade Report (/grade/report/user/index.php?id=327), I get a "Error reading from database" error, with an error similar to what Neal posted above in my log. I have Windows Server 2008 R2, patched to date, SQL 2008 R2, patched to date. PHP 5.3.6 NTS with FastCGI on IIS 7.5.

          It would appear to be syntactically incorrect TSQL, although I get lost a couple joins into it, so that's a guess on my part. Maybe a missing paren or something?

          [26-Oct-2011 16:09:38] Default exception handler: Error reading from database Debug: SQLState: 42000<br>
          Error Code: 156<br>
          Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.<br>

          SELECT gi.id, COUNT(u.id) AS count
          FROM mdl_grade_items gi
          JOIN mdl_user u
          JOIN (SELECT DISTINCT eu2_u.id
          FROM mdl_user eu2_u
          JOIN mdl_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id
          JOIN mdl_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = '327')
          WHERE eu2_u.deleted = 0 AND eu2_u.id <> '1') je ON je.id = u.id
          JOIN (
          SELECT DISTINCT ra.userid
          FROM mdl_role_assignments ra
          WHERE ra.roleid = '5'
          AND ra.contextid IN (2004,683,1)
          ) rainner ON rainner.userid = u.id
          LEFT JOIN mdl_grade_grades gg
          ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0)

          WHERE gi.courseid = '327'
          AND u.deleted = 0
          AND gg.finalgrade IS NULL

          GROUP BY gi.id
          [array (
          0 => '327',
          1 => '1',
          2 => '5',
          3 => 327,
          )]

          Show
          David Boyer added a comment - I'm seeing the same thing on 2.1.2-20111019. If I try to view a User Grade Report (/grade/report/user/index.php?id=327), I get a "Error reading from database" error, with an error similar to what Neal posted above in my log. I have Windows Server 2008 R2, patched to date, SQL 2008 R2, patched to date. PHP 5.3.6 NTS with FastCGI on IIS 7.5. It would appear to be syntactically incorrect TSQL, although I get lost a couple joins into it, so that's a guess on my part. Maybe a missing paren or something? [26-Oct-2011 16:09:38] Default exception handler: Error reading from database Debug: SQLState: 42000<br> Error Code: 156<br> Message: [Microsoft] [SQL Server Native Client 10.0] [SQL Server] Incorrect syntax near the keyword 'WHERE'.<br> SELECT gi.id, COUNT(u.id) AS count FROM mdl_grade_items gi JOIN mdl_user u JOIN (SELECT DISTINCT eu2_u.id FROM mdl_user eu2_u JOIN mdl_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id JOIN mdl_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = '327') WHERE eu2_u.deleted = 0 AND eu2_u.id <> '1') je ON je.id = u.id JOIN ( SELECT DISTINCT ra.userid FROM mdl_role_assignments ra WHERE ra.roleid = '5' AND ra.contextid IN (2004,683,1) ) rainner ON rainner.userid = u.id LEFT JOIN mdl_grade_grades gg ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0) WHERE gi.courseid = '327' AND u.deleted = 0 AND gg.finalgrade IS NULL GROUP BY gi.id [array ( 0 => '327', 1 => '1', 2 => '5', 3 => 327, )]
          Hide
          Mike Kelly added a comment - - edited

          I'm also seeing this on a CentOS 5.6 box, with PHP 5.3.6, Apache 2.2.3 and PostgreSQL 9.0.3, with Moodle 2.1.2. Error attached below.

          One thing I noticed is that the error is happening on the line "WHERE gi.courseid = $4", and if you look at the 4th element of the array, the course id is being passed as an integer (no quotes). Whereas the 1st element of the array is also a coursed, but it is contained within single quotes.

          eg:

          [array (
          0 => '768',
          1 => '1',
          2 => '5',
          3 => 768,
          )]

          Not sure if that's helpful or not as I'm not all that familiar with the code - but both courseid fields in mdl_enrol and mdl_grade_items are stored as bigints - so why are they referred to differently in these array elements?

          Here's our error dump:

          Default exception handler: Error reading from database Debug:

          ERROR: syntax error at or near "WHERE" at character 984
          SELECT gi.id, COUNT(u.id) AS count
          FROM mdl_grade_items gi
          JOIN mdl_user u
          JOIN (SELECT DISTINCT eu2_u.id
          FROM mdl_user eu2_u
          JOIN mdl_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id
          JOIN mdl_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = $1)
          WHERE eu2_u.deleted = 0 AND eu2_u.id <> $2) je ON je.id = u.id
          JOIN (
          SELECT DISTINCT ra.userid
          FROM mdl_role_assignments ra
          WHERE ra.roleid = $3
          AND ra.contextid IN (41383,10184,15,1)
          ) rainner ON rainner.userid = u.id
          LEFT JOIN mdl_grade_grades gg
          ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0)

          WHERE gi.courseid = $4
          AND u.deleted = 0
          AND gg.finalgrade IS NULL

          GROUP BY gi.id

          [array (
          0 => '768',
          1 => '1',
          2 => '5',
          3 => 768,
          )]

          • line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
          • line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
          • line 678 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
          • line 670 of /grade/report/user/lib.php: call to pgsql_native_moodle_database->get_records_sql()
          • line 217 of /grade/report/user/lib.php: call to grade_report_user->calculate_averages()
          • line 142 of /grade/report/user/index.php: call to grade_report_user->__construct()
          Show
          Mike Kelly added a comment - - edited I'm also seeing this on a CentOS 5.6 box, with PHP 5.3.6, Apache 2.2.3 and PostgreSQL 9.0.3, with Moodle 2.1.2. Error attached below. One thing I noticed is that the error is happening on the line "WHERE gi.courseid = $4", and if you look at the 4th element of the array, the course id is being passed as an integer (no quotes). Whereas the 1st element of the array is also a coursed, but it is contained within single quotes. eg: [array ( 0 => '768', 1 => '1', 2 => '5', 3 => 768, )] Not sure if that's helpful or not as I'm not all that familiar with the code - but both courseid fields in mdl_enrol and mdl_grade_items are stored as bigints - so why are they referred to differently in these array elements? Here's our error dump: Default exception handler: Error reading from database Debug: ERROR: syntax error at or near "WHERE" at character 984 SELECT gi.id, COUNT(u.id) AS count FROM mdl_grade_items gi JOIN mdl_user u JOIN (SELECT DISTINCT eu2_u.id FROM mdl_user eu2_u JOIN mdl_user_enrolments eu2_ue ON eu2_ue.userid = eu2_u.id JOIN mdl_enrol eu2_e ON (eu2_e.id = eu2_ue.enrolid AND eu2_e.courseid = $1) WHERE eu2_u.deleted = 0 AND eu2_u.id <> $2) je ON je.id = u.id JOIN ( SELECT DISTINCT ra.userid FROM mdl_role_assignments ra WHERE ra.roleid = $3 AND ra.contextid IN (41383,10184,15,1) ) rainner ON rainner.userid = u.id LEFT JOIN mdl_grade_grades gg ON (gg.itemid = gi.id AND gg.userid = u.id AND gg.finalgrade IS NOT NULL AND gg.hidden = 0) WHERE gi.courseid = $4 AND u.deleted = 0 AND gg.finalgrade IS NULL GROUP BY gi.id [array ( 0 => '768', 1 => '1', 2 => '5', 3 => 768, )] line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown line 232 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end() line 678 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end() line 670 of /grade/report/user/lib.php: call to pgsql_native_moodle_database->get_records_sql() line 217 of /grade/report/user/lib.php: call to grade_report_user->calculate_averages() line 142 of /grade/report/user/index.php: call to grade_report_user->__construct()
          Hide
          Andrew Davis added a comment - - edited

          It seems that this error does not occur in MySQL. MySQL is a bit more forgiving with SQL so thats not terribly surprising.

          After doing some experimenting with an online Sql validator (http://developer.mimer.com/validator/parser99/index.tml#parser) I believe I've isolated the problem. Here is a cut down version of the query in question that still demonstrates the problem.

          SELECT gi.id, COUNT(u.id) AS count
            FROM mdl_grade_items gi
            JOIN mdl_user u
          
           WHERE gi.courseid = 4
                 AND u.deleted = 0
          GROUP BY gi.id
          

          It looks like its the absence of an "ON" clause after the join with the user table that is causing the problem. Adding the below bogus on clause makes the error go away but I'm 99% sure this isn't the right way to fix this. Note that we do actually want a cartesian product (ie cross join) here. Theres a comment above the query at line 650 of /grade/report/user/lib.php that explains more.

          SELECT gi.id, COUNT(u.id) AS count
            FROM mdl_grade_items gi
            JOIN mdl_user u ON 1 = 1
          
           WHERE gi.courseid = 4
                 AND u.deleted = 0
          GROUP BY gi.id
          

          Actually, shifting the u.deleted condition from the where into an on clause should resolve this...

          SELECT gi.id, COUNT(u.id) AS count
            FROM mdl_grade_items gi
            JOIN mdl_user u ON u.deleted = 0
           WHERE gi.courseid = 4
                 GROUP BY gi.id
          
          Show
          Andrew Davis added a comment - - edited It seems that this error does not occur in MySQL. MySQL is a bit more forgiving with SQL so thats not terribly surprising. After doing some experimenting with an online Sql validator ( http://developer.mimer.com/validator/parser99/index.tml#parser ) I believe I've isolated the problem. Here is a cut down version of the query in question that still demonstrates the problem. SELECT gi.id, COUNT(u.id) AS count FROM mdl_grade_items gi JOIN mdl_user u WHERE gi.courseid = 4 AND u.deleted = 0 GROUP BY gi.id It looks like its the absence of an "ON" clause after the join with the user table that is causing the problem. Adding the below bogus on clause makes the error go away but I'm 99% sure this isn't the right way to fix this. Note that we do actually want a cartesian product (ie cross join) here. Theres a comment above the query at line 650 of /grade/report/user/lib.php that explains more. SELECT gi.id, COUNT(u.id) AS count FROM mdl_grade_items gi JOIN mdl_user u ON 1 = 1 WHERE gi.courseid = 4 AND u.deleted = 0 GROUP BY gi.id Actually, shifting the u.deleted condition from the where into an on clause should resolve this... SELECT gi.id, COUNT(u.id) AS count FROM mdl_grade_items gi JOIN mdl_user u ON u.deleted = 0 WHERE gi.courseid = 4 GROUP BY gi.id
          Hide
          Andrew Davis added a comment -

          Added a diff URL for master for peer review. Can someone who has experienced the problem or who is running SQL Server or Postgres verify that this does in fact resolve the issue? It should but would like to be 100% sure

          Show
          Andrew Davis added a comment - Added a diff URL for master for peer review. Can someone who has experienced the problem or who is running SQL Server or Postgres verify that this does in fact resolve the issue? It should but would like to be 100% sure
          Hide
          Andrew Davis added a comment -

          Added testing instructions. Will add versions for the other branches after peer review.

          Show
          Andrew Davis added a comment - Added testing instructions. Will add versions for the other branches after peer review.
          Hide
          Jason Fowler added a comment -

          Code looks good, makes sense, and extra whitespace added for readability.

          Show
          Jason Fowler added a comment - Code looks good, makes sense, and extra whitespace added for readability.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

          TIA and ciao

          PS: Note this is the last message of this type that you will receive along the whole November month, because we are running continuous integration this weeks while QA tests for release of Moodle 2.2 (Dec 1st) are being performed.

          Show
          Eloy Lafuente (stronk7) added a comment - The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week. TIA and ciao PS: Note this is the last message of this type that you will receive along the whole November month, because we are running continuous integration this weeks while QA tests for release of Moodle 2.2 (Dec 1st) are being performed.
          Hide
          Aparup Banerjee added a comment -

          Thanks, this has been integrated. (works for me under postgres too) - up for testing/verification.

          Show
          Aparup Banerjee added a comment - Thanks, this has been integrated. (works for me under postgres too) - up for testing/verification.
          Hide
          SAMUEL RAMIREZ TORRES added a comment -

          Big solution, so thx.

          Show
          SAMUEL RAMIREZ TORRES added a comment - Big solution, so thx.
          Hide
          Sam Hemelryk added a comment -

          Thanks guys passing this test now

          Show
          Sam Hemelryk added a comment - Thanks guys passing this test now
          Hide
          Eloy Lafuente (stronk7) added a comment -

          And this has landed upstream, just on time for the upcoming new releases week. Thanks for it!

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - And this has landed upstream, just on time for the upcoming new releases week. Thanks for it! Ciao

            People

            • Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: