Moodle

Sorting submitted assignments by status makes all assignments disappear

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.8.2
  • Fix Version/s: 1.7.3, 1.8.3, 1.9
  • Component/s: Assignment
  • Labels:
    None
  • Environment:
    Debian etch, but using moodle 1.8.2 from upstream moodle.org (not Debian package), other info on http://kno.ii.uni.wroc.pl/phpinfo.php if needed
  • Database:
    MySQL
  • Affected Branches:
    MOODLE_18_STABLE
  • Fixed Branches:
    MOODLE_17_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE

Description

When looking at the list of assignments (by "View ... submitted assignments"), press "status" to sort by status. All records disappear.

Reason: SQL query generated to sort by status is invalid. It looks like

SELECT u.id, u.firstname, u.lastname, u.picture, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid AND s.assignment = 1 WHERE u.id IN (3,4) ORDER BY status ASC, lastname ASC

So "status" is used in "order by", but it doesn't exist in returned set. It worked in moodle 1.6.x. I have a strong feeling that this bug is caused by incorrectly fixing #8164 ("MS SQL cannot return Boolean data type in query results, any code using this returns no results when using Sql 2005"). Looks like when fixing #8164, status field was removed from SQL clauses, but it's still needed there for sorting.

To fix this on our moodle installation we used the attached patch. Basically, it adds again the SQL code that was already in moodle 1.6. So careless applying it will probably break #8164 again... The patch is only safe for people that run on database that can handle such SQL, like MySQL. The correct solution is probably to make a better fix to #8164.

Issue Links

This issue will be resolved by:
MDL-8164 MS SQL cannot return Boolean data type in query results, any code using this returns no results when using Sql 2005 Critical Closed
This issue is duplicated by:
MDL-10577 Problem with submissions when sorted by status Minor Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Done, now it's using one cross-db clause to work properly.

Applied to 17_STABLE, 18_STABLE and HEAD.

Show
Eloy Lafuente (stronk7) added a comment - Done, now it's using one cross-db clause to work properly. Applied to 17_STABLE, 18_STABLE and HEAD.
Hide
Permalink
Teresa Gibbison added a comment - - edited

This issue is still happening with Moodle 1.9.13+ (Build: 20110803) as well as my dev Moodle 2.0.4+ site and demo.moodle.net Moodle 2.1.1+ (Build: 20110817).

The only way to view the submissions again is to alter the URL to ...&tsort=timemodified OR log out of Moodle and back in for another cookie.

[Edited to add postgre version & stack trace]
Note: PostgreSQL(libpq) Version 8.4.3

Debug info: ERROR: column "status" does not exist
LINE 4: ...ent = 20 WHERE u.id IN (49,50,52,54,46) ORDER BY status ASC...
^
SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email,
s.id AS submissionid, s.grade, s.submissioncomment,
s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid
AND s.assignment = 20 WHERE u.id IN (49,50,52,54,46) ORDER BY status ASC, lastname ASC
[array (
)]
Stack trace:
line 391 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 1330 of /mod/assignment/lib.php: call to pgsql_native_moodle_database->get_records_sql()
line 645 of /mod/assignment/lib.php: call to assignment_base->display_submissions()
line 369 of /mod/assignment/type/upload/assignment.class.php: call to assignment_base->submissions()
line 57 of /mod/assignment/submissions.php: call to assignment_upload->submissions()

Show
Teresa Gibbison added a comment - - edited This issue is still happening with Moodle 1.9.13+ (Build: 20110803) as well as my dev Moodle 2.0.4+ site and demo.moodle.net Moodle 2.1.1+ (Build: 20110817). The only way to view the submissions again is to alter the URL to ...&tsort=timemodified OR log out of Moodle and back in for another cookie. [Edited to add postgre version & stack trace] Note: PostgreSQL(libpq) Version 8.4.3 Debug info: ERROR: column "status" does not exist LINE 4: ...ent = 20 WHERE u.id IN (49,50,52,54,46) ORDER BY status ASC... ^ SELECT u.id,u.picture,u.firstname,u.lastname,u.imagealt,u.email, s.id AS submissionid, s.grade, s.submissioncomment, s.timemodified, s.timemarked FROM mdl_user u LEFT JOIN mdl_assignment_submissions s ON u.id = s.userid AND s.assignment = 20 WHERE u.id IN (49,50,52,54,46) ORDER BY status ASC, lastname ASC [array ( )] Stack trace: line 391 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 1330 of /mod/assignment/lib.php: call to pgsql_native_moodle_database->get_records_sql() line 645 of /mod/assignment/lib.php: call to assignment_base->display_submissions() line 369 of /mod/assignment/type/upload/assignment.class.php: call to assignment_base->submissions() line 57 of /mod/assignment/submissions.php: call to assignment_upload->submissions()
Hide
Permalink
Caroline Moore added a comment - - edited

Agreeing with Teresa: I just encountered this problem in my Moodle 1.9.12+ build. Can someone please re-open this issue?

Show
Caroline Moore added a comment - - edited Agreeing with Teresa: I just encountered this problem in my Moodle 1.9.12+ build. Can someone please re-open this issue?
Hide
Permalink
Michalis Kamburelis added a comment -

Teresa, Caroline: this bug report is "closed" (the problem was fixed and the fix was working, at some point in the past...), and Moodle tracker doesn't allow to reopen it. We have to submit new issue, which I did (after confirming that the bug indeed occurs again, at least with 1.9.13+ (Build: 20110921)).

See MDL-29928, you can find my simple patch there (although it may work only for MySQL).

Show
Michalis Kamburelis added a comment - Teresa, Caroline: this bug report is "closed" (the problem was fixed and the fix was working, at some point in the past...), and Moodle tracker doesn't allow to reopen it. We have to submit new issue, which I did (after confirming that the bug indeed occurs again, at least with 1.9.13+ (Build: 20110921)). See MDL-29928, you can find my simple patch there (although it may work only for MySQL).

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.