Moodle
  1. Moodle
  2. MDL-35717

Quiz cron not closing old attempts after quiz close date

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.3.2, 2.3.8
    • Fix Version/s: 2.3.4, 2.4
    • Component/s: Quiz
    • Labels:
    • Testing Instructions:
      Hide

      Really, we need to re-run QA tests MDLQA-4166 to MDLQA-4177, or at least the parts of them that involve cron.

      Testing instructions for the initial problem:
      1. Create a quiz with a close date enough in the future to run steps 2 and 3 first. Set the quiz to automatically submit attempts when time expires.
      2. Attempt the quiz as a student, but don't finish the attempt.
      3. Run the moodle cron.
      4. Verify that the student attempt hasn't been closed.
      5. Wait long enough that the quiz has been closed for at least quiz mingraceperiod (60 secs by default), and run the moodle cron again.
      6. Verify that the student attempt has closed. (Note: this check should be done as the instructor, since the student viewing his own attempt will close it on view.)

      (To replicate the problem in current moodle, note that the overdue attempt processor only runs a maximum of once an hour. This patch eliminates the need for that delay, as well as fixes the bug.)

      Show
      Really, we need to re-run QA tests MDLQA-4166 to MDLQA-4177, or at least the parts of them that involve cron. Testing instructions for the initial problem: 1. Create a quiz with a close date enough in the future to run steps 2 and 3 first. Set the quiz to automatically submit attempts when time expires. 2. Attempt the quiz as a student, but don't finish the attempt. 3. Run the moodle cron. 4. Verify that the student attempt hasn't been closed. 5. Wait long enough that the quiz has been closed for at least quiz mingraceperiod (60 secs by default), and run the moodle cron again. 6. Verify that the student attempt has closed. (Note: this check should be done as the instructor, since the student viewing his own attempt will close it on view.) (To replicate the problem in current moodle, note that the overdue attempt processor only runs a maximum of once an hour. This patch eliminates the need for that delay, as well as fixes the bug.)
    • Workaround:
      Hide

      Open mod/quiz/cronlib.php and find the chunk of code that looks like

                WHERE iquiza.state IN ('inprogress', 'overdue')
                  AND iquiza.timemodified >= :processfrom
                  AND iquiza.timemodified < :processto
      

      Delete the middle of those three lines, so that it looks like

                WHERE iquiza.state IN ('inprogress', 'overdue')
                  AND iquiza.timemodified < :processto
      

      If you have a small Moodle site, then you can probably do that without worrying.

      If the quiz_attempts table if you Moodle site has a lot of rows, then making this change may cause cron to run unacceptably slowly. (It would be useful to know how slow it is, if anyone tries it on a big site.)

      Show
      Open mod/quiz/cronlib.php and find the chunk of code that looks like WHERE iquiza.state IN ('inprogress', 'overdue') AND iquiza.timemodified >= :processfrom AND iquiza.timemodified < :processto Delete the middle of those three lines, so that it looks like WHERE iquiza.state IN ('inprogress', 'overdue') AND iquiza.timemodified < :processto If you have a small Moodle site, then you can probably do that without worrying. If the quiz_attempts table if you Moodle site has a lot of rows, then making this change may cause cron to run unacceptably slowly. (It would be useful to know how slow it is, if anyone tries it on a big site.)
    • Affected Branches:
      MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-35717-quiz-attempt-checkstate
    • Rank:
      44461

      Description

      We see lots of quiz attempts staying in the "In progress" state after quizzes close.
      Attempts modified within one hour of the close date are closed (in a way depending on quiz settings), but previous attempts are left open.

      There is an SQL WHERE clause in get_list_of_overdue_attempts() (mod/quiz/cronlib.php) that selects only recent quiz attempts:

                WHERE iquiza.state IN ('inprogress', 'overdue')
                  AND iquiza.timemodified >= :processfrom
                  AND iquiza.timemodified < :processto
      

      I think the middle line is causing this. Removing that line should fix the problem, although it removes the incremental processing aspect to the code. On our new 2.3 site with 20k attempts, the full query with processfrom=0 finishes in milliseconds, so this might not be an issue.

        Issue Links

          Activity

          Hide
          Tim Hunt added a comment -

          In terms of removing that line, please bear in mind that it is quite possible to have more than 1 million quiz_attempts. (and for other tables involved in the query, like user and group_members to be similarly huge).

          I don't understand why the query would be missing some attempts. The processfrom, processto logic is quite clear, and quiz_attempt.timemodified is only every going to increase, and to increase to the current time.

          So, before changing the query, I would really like to understand the set of circumstances that leads to an attempt getting stuck in the In progress state, when it should be moved to Never submitted or Finished.

          Can you work out what is actually happening? Thanks.

          Show
          Tim Hunt added a comment - In terms of removing that line, please bear in mind that it is quite possible to have more than 1 million quiz_attempts. (and for other tables involved in the query, like user and group_members to be similarly huge). I don't understand why the query would be missing some attempts. The processfrom, processto logic is quite clear, and quiz_attempt.timemodified is only every going to increase, and to increase to the current time. So, before changing the query, I would really like to understand the set of circumstances that leads to an attempt getting stuck in the In progress state, when it should be moved to Never submitted or Finished. Can you work out what is actually happening? Thanks.
          Hide
          Matt Petro added a comment -

          I understand the performance concerns. I was not really proposing a solution, so much as pointing out where the problem was.

          The big SQL looks like this:

          SELECT FROM ([A]) JOIN ([B]) ON attemptid

          [A] selects only "in progress" attempts in the last hour
          [B] selects attempts that should be closed based on "timenow > closetime"

          Because of [A], attempts are never processed by quiz cron more than an hour after being modified. If the last such processing is before the quiz closes, then they will never get closed. That's what we are seeing on our site.

          Show
          Matt Petro added a comment - I understand the performance concerns. I was not really proposing a solution, so much as pointing out where the problem was. The big SQL looks like this: SELECT FROM ( [A] ) JOIN ( [B] ) ON attemptid [A] selects only "in progress" attempts in the last hour [B] selects attempts that should be closed based on "timenow > closetime" Because of [A] , attempts are never processed by quiz cron more than an hour after being modified. If the last such processing is before the quiz closes, then they will never get closed. That's what we are seeing on our site.
          Hide
          Matt Petro added a comment -

          How about switching the incremental processing to quiz-based times rather than attempt based modify times?

          In pseudo SQL:

          SELECT (All quizzes WITH processfrom <= any quiz closetime (incl. overrides) < processto)
                 UNION
                 (All quizzes WITH timelimit set and an attempt with (timenow - attempt.timestart) > quiz_min_timelimit)
          
                 JOIN 
                 ([A']) ON quizid
                 JOIN
                 ([B]) ON attemptid
          

          Where:
          [A'] is just like [A] above but without the processfrom condition
          [B] is as above

          What do you think? I might be brave enough to tackle this

          Show
          Matt Petro added a comment - How about switching the incremental processing to quiz-based times rather than attempt based modify times? In pseudo SQL: SELECT (All quizzes WITH processfrom <= any quiz closetime (incl. overrides) < processto) UNION (All quizzes WITH timelimit set and an attempt with (timenow - attempt.timestart) > quiz_min_timelimit) JOIN ([A']) ON quizid JOIN ([B]) ON attemptid Where: [A'] is just like [A] above but without the processfrom condition [B] is as above What do you think? I might be brave enough to tackle this
          Hide
          Tim Hunt added a comment -

          I think you are wrong about [A].

          When processfrom = T1, then that means that a previous run of cron has correctly processed all quiz attempt with timemodified <= 0.

          Then, for this cron run, we have some processto = T2.

          [A] then finds all quiz attemets where lastmodified is between T1 and T2, and processes them.

          Finally, cron records that we are complete up to T2, so that the next cron run will start from processfrom = T2 - thus ensuring that nothing missed.

          Therefore, I don't understand why you say '[A] selects only "in progress" attempts in the last hour'.

          Show
          Tim Hunt added a comment - I think you are wrong about [A] . When processfrom = T1, then that means that a previous run of cron has correctly processed all quiz attempt with timemodified <= 0. Then, for this cron run, we have some processto = T2. [A] then finds all quiz attemets where lastmodified is between T1 and T2, and processes them. Finally, cron records that we are complete up to T2, so that the next cron run will start from processfrom = T2 - thus ensuring that nothing missed. Therefore, I don't understand why you say ' [A] selects only "in progress" attempts in the last hour'.
          Hide
          Matt Petro added a comment -

          It could be I'm not understanding something:

          T1 < T2 < T3 < T4

          Attempt A1 is last modified at T1 (and student doesn't interact thereafter, so it stays at T1 indefinitely.)
          Cron runs at T2, processes A1. Leaves it open
          Quiz closes at T3
          Cron runs at T4, doesn't process A1

          How does A1 get closed?

          Show
          Matt Petro added a comment - It could be I'm not understanding something: T1 < T2 < T3 < T4 Attempt A1 is last modified at T1 (and student doesn't interact thereafter, so it stays at T1 indefinitely.) Cron runs at T2, processes A1. Leaves it open Quiz closes at T3 Cron runs at T4, doesn't process A1 How does A1 get closed?
          Hide
          Tim Hunt added a comment -

          Ah! Drat! Yes. That is where my logic breaks down.

          Thanks you for explaining.

          So, given that, is there any way to stop the query having to inspect the whole of quiz_attempts?

          Or, is the easiest thing to just create a test database with 1 million quiz_attempts spread across a few thousand quizzes, 1 million groups_members and a fair smattering of user and group overrides, and see how the query performs without the iquiza.timemodified >= :processfrom condition?

          As a temporary work-around, if it performs OK on your server, just delete that test.

          Show
          Tim Hunt added a comment - Ah! Drat! Yes. That is where my logic breaks down. Thanks you for explaining. So, given that, is there any way to stop the query having to inspect the whole of quiz_attempts? Or, is the easiest thing to just create a test database with 1 million quiz_attempts spread across a few thousand quizzes, 1 million groups_members and a fair smattering of user and group overrides, and see how the query performs without the iquiza.timemodified >= :processfrom condition? As a temporary work-around, if it performs OK on your server, just delete that test.
          Hide
          Tim Hunt added a comment -

          I suppose the iquiza.state IN ('inprogress', 'overdue') condition is likely to help a lot, unless there are many quizzes without a close date or time limit.

          Show
          Tim Hunt added a comment - I suppose the iquiza.state IN ('inprogress', 'overdue') condition is likely to help a lot, unless there are many quizzes without a close date or time limit.
          Hide
          Matt Petro added a comment -

          What do you think about my previous comment on adding the JOIN to the quiz table, effectively just processing quizzes with events (like 'closing') that happened between processfrom and processto. I can try to write the sql if it seems like a good idea.

          Show
          Matt Petro added a comment - What do you think about my previous comment on adding the JOIN to the quiz table, effectively just processing quizzes with events (like 'closing') that happened between processfrom and processto. I can try to write the sql if it seems like a good idea.
          Hide
          Tim Hunt added a comment -

          I don't think it helps. For quizzes with a close date, attempts will rapidly be moved out of the ('inprogress', 'overdue') states. The problem is quizzes without a close date (either for the quiz, or any overridden group/user).

          Show
          Tim Hunt added a comment - I don't think it helps. For quizzes with a close date, attempts will rapidly be moved out of the ('inprogress', 'overdue') states. The problem is quizzes without a close date (either for the quiz, or any overridden group/user).
          Hide
          Matt Petro added a comment -

          Would a system-wide setting to close all quiz attempts after e.g. 30 days be feasible?

          Show
          Matt Petro added a comment - Would a system-wide setting to close all quiz attempts after e.g. 30 days be feasible?
          Hide
          Tim Hunt added a comment -

          Well, that might be a desirable feature, but introducing that feature just because we are not clever enough to optimise a cron process is cheating.

          I think the right way to tackle this is to add a new column quiz_attempts.checkstatetime (or a better name). That would initially start NULL for all attempts.

          When cron processes an attempt, if the attempt should remain In progress or Overdue, then that column will be set to min(usertimeclose, attempt.timestart + usertimelimit). (For attempts with neither close time nor time limit, we will just set it a long way in the future.)

          the big cron SQL can skip any attempts with checkstatetime > processto.

          Any edit action that affects the quiz attempt (editing quiz settings, editing group or user overrides, quiz attempt being modified) will just set checkstatetime back to NULL for the applicable attempts. (It could even try to set it to a better guess, but it does not need to.)

          Does that seem like reasonable logic?

          Show
          Tim Hunt added a comment - Well, that might be a desirable feature, but introducing that feature just because we are not clever enough to optimise a cron process is cheating. I think the right way to tackle this is to add a new column quiz_attempts.checkstatetime (or a better name). That would initially start NULL for all attempts. When cron processes an attempt, if the attempt should remain In progress or Overdue, then that column will be set to min(usertimeclose, attempt.timestart + usertimelimit). (For attempts with neither close time nor time limit, we will just set it a long way in the future.) the big cron SQL can skip any attempts with checkstatetime > processto. Any edit action that affects the quiz attempt (editing quiz settings, editing group or user overrides, quiz attempt being modified) will just set checkstatetime back to NULL for the applicable attempts. (It could even try to set it to a better guess, but it does not need to.) Does that seem like reasonable logic?
          Hide
          Matt Petro added a comment -

          I like the idea of the new column. It also handles the case of end dates being set retroactively on a quiz.

          I take it the processing of each attempt would set quiz_attempts.checkstatetime to the computed actual state change time in the future. So, wouldn't this be lightweight enough to run every time the moodle cron runs? (Since attempts would typically only be processed twice: once with checkstatetime=NULL and once when they expire). Waiting 1 hour for a quiz attempt to close is not optimal.

          This would also need to hook into group add/delete member events.

          Show
          Matt Petro added a comment - I like the idea of the new column. It also handles the case of end dates being set retroactively on a quiz. I take it the processing of each attempt would set quiz_attempts.checkstatetime to the computed actual state change time in the future. So, wouldn't this be lightweight enough to run every time the moodle cron runs? (Since attempts would typically only be processed twice: once with checkstatetime=NULL and once when they expire). Waiting 1 hour for a quiz attempt to close is not optimal. This would also need to hook into group add/delete member events.
          Hide
          Matt Petro added a comment -

          It might make sense to just set this field when the attempt is created.

          Show
          Matt Petro added a comment - It might make sense to just set this field when the attempt is created.
          Hide
          Tim Hunt added a comment -

          Where possible (meaning where it can be done without extra DB queries) we should set checkstatetime to the right value, rather than NULL.

          Indeed, we can probably write another monster query to do

          UPDATE

          {quiz_attempts}

          SET checkstatetime = ( ... some moster sub-query a bit like the one in cron ... ) WHERE ... some test ...

          We could put that in a function in locallib.php, can call it with from places like quiz edit, override edit, group member delete event handler, etc.

          Anyway, the real question is, who is going to try to code this? It is probably several day's work, and my schedule is not looking good. I have things to achieve in the next two weeks, then I go to Australia for 4 weeks. Is there any chance you could do it? (If you do, I will make time to peer-review.)

          Show
          Tim Hunt added a comment - Where possible (meaning where it can be done without extra DB queries) we should set checkstatetime to the right value, rather than NULL. Indeed, we can probably write another monster query to do UPDATE {quiz_attempts} SET checkstatetime = ( ... some moster sub-query a bit like the one in cron ... ) WHERE ... some test ... We could put that in a function in locallib.php, can call it with from places like quiz edit, override edit, group member delete event handler, etc. Anyway, the real question is, who is going to try to code this? It is probably several day's work, and my schedule is not looking good. I have things to achieve in the next two weeks, then I go to Australia for 4 weeks. Is there any chance you could do it? (If you do, I will make time to peer-review.)
          Hide
          Matt Petro added a comment -

          Sure, I'll try to find some time in the next couple weeks to implement this.

          Show
          Matt Petro added a comment - Sure, I'll try to find some time in the next couple weeks to implement this.
          Hide
          Tim Hunt added a comment -

          I just added a work-around which involves editing the code a bit. If you try it, please report back.

          Show
          Tim Hunt added a comment - I just added a work-around which involves editing the code a bit. If you try it, please report back.
          Hide
          Matt Petro added a comment -

          Here's how I implemented it:

          • The new quiz_attempts field is 'timecheckstate'. NULL means never check. Default is 0. (i.e. check on next cron) I went with NULL as "never check again" because it was easier to implement in the bulk update queries
          • timecheckstate is set everywhere an attempt is created and on quiz edit, override edit, group event, etc. The bulk updates are done in quiz_update_open_attempts(), which I tried to make as efficient as possible. I'll warn you though, it's really nasty SQL. I've only tested on MySQL.
          • The cron code is much more efficient and I switched it to run on every cron run. The 'processfrom' constraint is gone too, since by design every attempt < processto needs to be processed.
          • The previous logic involving group overrides and timelimit/closedate=0 was wrong. I fixed that as part of this patch. Previously timelimit=0 wouldn't override timelimit=10
          • The accessrule->time_left() function had two uses previously: Time left, and displayed time left. I split these into separate functions: time_left() and time_left_display(), so that now time_left() only returns false when there is no timelimit at all.
          Show
          Matt Petro added a comment - Here's how I implemented it: The new quiz_attempts field is 'timecheckstate'. NULL means never check. Default is 0. (i.e. check on next cron) I went with NULL as "never check again" because it was easier to implement in the bulk update queries timecheckstate is set everywhere an attempt is created and on quiz edit, override edit, group event, etc. The bulk updates are done in quiz_update_open_attempts(), which I tried to make as efficient as possible. I'll warn you though, it's really nasty SQL. I've only tested on MySQL. The cron code is much more efficient and I switched it to run on every cron run. The 'processfrom' constraint is gone too, since by design every attempt < processto needs to be processed. The previous logic involving group overrides and timelimit/closedate=0 was wrong. I fixed that as part of this patch. Previously timelimit=0 wouldn't override timelimit=10 The accessrule->time_left() function had two uses previously: Time left, and displayed time left. I split these into separate functions: time_left() and time_left_display(), so that now time_left() only returns false when there is no timelimit at all.
          Hide
          Matt Petro added a comment -

          GIT branch:
          https://github.com/mpetrowi/moodle/tree/MDL-35717-quiz-attempt-checkstate/

          (I'm new to GIT, so hopefully I did that part right.)

          Please peer-review and let me know what should be changed. Thanks.

          Show
          Matt Petro added a comment - GIT branch: https://github.com/mpetrowi/moodle/tree/MDL-35717-quiz-attempt-checkstate/ (I'm new to GIT, so hopefully I did that part right.) Please peer-review and let me know what should be changed. Thanks.
          Hide
          Matt Petro added a comment -

          One question I had:

          Are Instructor previews supposed to be limited by closedate and timelimit?

          Show
          Matt Petro added a comment - One question I had: Are Instructor previews supposed to be limited by closedate and timelimit?
          Hide
          Tim Hunt added a comment -

          Thank you very much for working on this.

          I have just got back from the pub, so it will be safer if I review this tomorrow.

          A better way to specify the place to look on github is with a URL like: https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate. I don't know the "proper" way to get there by clicking links, but someone once showed me the trick of typing URLs like that.

          Cron should not process teacher previews at all. When the teacher is previewing, then the countdown timer appears, and after the close date, the start attempt button goes away, but the teacher can still preview from the settings menu.

          Show
          Tim Hunt added a comment - Thank you very much for working on this. I have just got back from the pub, so it will be safer if I review this tomorrow. A better way to specify the place to look on github is with a URL like: https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate . I don't know the "proper" way to get there by clicking links, but someone once showed me the trick of typing URLs like that. Cron should not process teacher previews at all. When the teacher is previewing, then the countdown timer appears, and after the close date, the start attempt button goes away, but the teacher can still preview from the settings menu.
          Hide
          Matt Petro added a comment -

          Makes sense. For previews I changed

          quizaccess_

          {openclosedate,timelimit}

          ->time_left() to return false for a preview. This way no special treatment is required in the overdue code.

          The time_left_display() functions still return the real values, so the countdown timer still appears and will auto-close previews in the browser. This is the same behavior as before, but I'm unsure if it's correct.

          Show
          Matt Petro added a comment - Makes sense. For previews I changed quizaccess_ {openclosedate,timelimit} ->time_left() to return false for a preview. This way no special treatment is required in the overdue code. The time_left_display() functions still return the real values, so the countdown timer still appears and will auto-close previews in the browser. This is the same behavior as before, but I'm unsure if it's correct.
          Hide
          Matt Petro added a comment -

          Some changes:

          • I've added unit tests to check the two big SQL queries. As a side effect, get_list_of_overdue_attempts() is now a public function. I didn't see any way to directly test it as a protected function.
          • I fixed a bug in the monster SQL in quiz_update_open_attempts().
          • Some of the accessrule unit tests were failing because of the new time_limit_display() functions. I fixed these as well.
          Show
          Matt Petro added a comment - Some changes: I've added unit tests to check the two big SQL queries. As a side effect, get_list_of_overdue_attempts() is now a public function. I didn't see any way to directly test it as a protected function. I fixed a bug in the monster SQL in quiz_update_open_attempts(). Some of the accessrule unit tests were failing because of the new time_limit_display() functions. I fixed these as well.
          Hide
          Tim Hunt added a comment -

          Thanks Matt. Sorry I did not get to review this yesterday. Today is looking better.

          The 'pure' way to test a protected function is to make a subclass in your test code, something like

          class mod_quiz_testable_overdue_attempt_updater
                  extends mod_quiz_overdue_attempt_updater {
              public function get_list_of_overdue_attempts($processto) {
                  parent::get_list_of_overdue_attempts($processto);
              }
          }
          

          However, in this case, that is overkill, and just making the method public was more sensible. I just thought I would show you that technique because it is a useful technique to know.

          Nice to see that more unit tests helped expose and fix more bugs.

          Show
          Tim Hunt added a comment - Thanks Matt. Sorry I did not get to review this yesterday. Today is looking better. The 'pure' way to test a protected function is to make a subclass in your test code, something like class mod_quiz_testable_overdue_attempt_updater extends mod_quiz_overdue_attempt_updater { public function get_list_of_overdue_attempts($processto) { parent::get_list_of_overdue_attempts($processto); } } However, in this case, that is overkill, and just making the method public was more sensible. I just thought I would show you that technique because it is a useful technique to know. Nice to see that more unit tests helped expose and fix more bugs.
          Hide
          Tim Hunt added a comment -

          Great work! A lot of this is right, but I have the following comments / questions:

          1. Looking at how it is used in the remaining places, I think it would be easier to have the API get_end_time, rather than get_time_left. I know making the change will be a pain, particularly in the unit tests, but I think it is worth it.

          2. For timelimit/rule.php, I think the

          if ($attempt->preview) {
              return false;
          }
          

          bit should be in the _display method.

          3. The change to the access rule API should be documented in mod/quiz/accessrule/upgrade.txt

          4. Whitespace error:

          //  any other group override
          

          should be a single space after //, and have a . at the end.

          Wow! the multiple joins on qgo are really clever!

          5. Except that is it really correct that a 'No time limit' override beats all the others? Oh, yes. The rule is to aggregate the group overrides to take the most permissive, isn't it.

          6. NULL should be lower-case.

          7. Why are you using the non-standard LEAST function. Why not MIN which works everywhere?

          (I still need to review quiz_update_open_attempts in detail, I will wait until you have answered 7.)

          8. Subqueries like "groupid NOT IN ( SELECT id FROM

          {groups}

          WHERE courseid = :courseid)" are know to perform badly on MySQL. (It treats them a correlated subqueries even though they are not.) Better to do a LEFT JOIN and then WHERE groups.id IS NULL. Also, in quiz_update_open_attempts(array('quizid'=>array_values($records))), should you actually use array_unique(array_values($records))?

          9. These are obviously big scary changes. What testing instructions do we think are necessary? Do we need to re-run all the relevant MDLQA tests? (Even thought they failed to find this problem the first time around.)

          Show
          Tim Hunt added a comment - Great work! A lot of this is right, but I have the following comments / questions: 1. Looking at how it is used in the remaining places, I think it would be easier to have the API get_end_time, rather than get_time_left. I know making the change will be a pain, particularly in the unit tests, but I think it is worth it. 2. For timelimit/rule.php, I think the if ($attempt->preview) { return false ; } bit should be in the _display method. 3. The change to the access rule API should be documented in mod/quiz/accessrule/upgrade.txt 4. Whitespace error: // any other group override should be a single space after //, and have a . at the end. Wow! the multiple joins on qgo are really clever! 5. Except that is it really correct that a 'No time limit' override beats all the others? Oh, yes. The rule is to aggregate the group overrides to take the most permissive, isn't it. 6. NULL should be lower-case. 7. Why are you using the non-standard LEAST function. Why not MIN which works everywhere? (I still need to review quiz_update_open_attempts in detail, I will wait until you have answered 7.) 8. Subqueries like "groupid NOT IN ( SELECT id FROM {groups} WHERE courseid = :courseid)" are know to perform badly on MySQL. (It treats them a correlated subqueries even though they are not.) Better to do a LEFT JOIN and then WHERE groups.id IS NULL. Also, in quiz_update_open_attempts(array('quizid'=>array_values($records))), should you actually use array_unique(array_values($records))? 9. These are obviously big scary changes. What testing instructions do we think are necessary? Do we need to re-run all the relevant MDLQA tests? (Even thought they failed to find this problem the first time around.)
          Hide
          Matt Petro added a comment -

          Tim, Thanks for the comments. I'll fix them up later today.

          About the UNION query: MIN is just an aggregate for GROUP BY. What I really need is LEAST(col1, col2) to compute whether timeleft or timeclose triggers first.

          Show
          Matt Petro added a comment - Tim, Thanks for the comments. I'll fix them up later today. About the UNION query: MIN is just an aggregate for GROUP BY. What I really need is LEAST(col1, col2) to compute whether timeleft or timeclose triggers first.
          Hide
          Tim Hunt added a comment -

          Doh! sorry. I always forget that. The cross DB way to do "min of two things" is

          CASE WHEN x < y THEN x ELSE y END

          It may need to be a bit more complex if you need to allow for NULLs

          Show
          Tim Hunt added a comment - Doh! sorry. I always forget that. The cross DB way to do "min of two things" is CASE WHEN x < y THEN x ELSE y END It may need to be a bit more complex if you need to allow for NULLs
          Hide
          Matt Petro added a comment -

          Neat! I'll switch it to that.

          Show
          Matt Petro added a comment - Neat! I'll switch it to that.
          Hide
          Matt Petro added a comment -

          I think I've addressed all the comments. Changing to get_end_time() helped the logic considerably. The only place that time_left is needed is in the renderer, and that's calling get_time_left_display() anyways.

          The SQL improved a ton by using CASE statements. quiz_update_open_attempts() now correctly computes all timecheckstate s. (Before it would underestimate in some situations.)

          Actually, it still underestimates for attempts in 'overdue' state, but I consider that a minor problem and probably not worth making the sql even more complicated. Cron will fix up this case.

          I made some changes to preview processing:

          • The logic which excludes previews from timelimits/timecloses is now in the locallib functions (primarily in handle_if_time_expired()) rather than in the access rules.
          • The countdown timer now knows whether an attempt is a preview or not. For previews, the timer counts down and then stops without a submit. This mirrors the cron behavior of not submitting overdue previews.
          Show
          Matt Petro added a comment - I think I've addressed all the comments. Changing to get_end_time() helped the logic considerably. The only place that time_left is needed is in the renderer, and that's calling get_time_left_display() anyways. The SQL improved a ton by using CASE statements. quiz_update_open_attempts() now correctly computes all timecheckstate s. (Before it would underestimate in some situations.) Actually, it still underestimates for attempts in 'overdue' state, but I consider that a minor problem and probably not worth making the sql even more complicated. Cron will fix up this case. I made some changes to preview processing: The logic which excludes previews from timelimits/timecloses is now in the locallib functions (primarily in handle_if_time_expired()) rather than in the access rules. The countdown timer now knows whether an attempt is a preview or not. For previews, the timer counts down and then stops without a submit. This mirrors the cron behavior of not submitting overdue previews.
          Hide
          Matt Petro added a comment -

          I'm not that familiar with the MDLQA test. What would typically be required for a change like this?

          To start with, the attempt unit tests should be run on all supported DB's. I've only tested on MySQL.

          Show
          Matt Petro added a comment - I'm not that familiar with the MDLQA test. What would typically be required for a change like this? To start with, the attempt unit tests should be run on all supported DB's. I've only tested on MySQL.
          Hide
          Matt Petro added a comment - - edited

          Actually, it still underestimates for attempts in 'overdue' state,

          This was an easy change to make, so it now adds in the graceperiod when appropriate.

          Show
          Matt Petro added a comment - - edited Actually, it still underestimates for attempts in 'overdue' state, This was an easy change to make, so it now adds in the graceperiod when appropriate.
          Hide
          Tim Hunt added a comment -

          Sorry it took me so long to peer-review this again.

          It is looking really, really good now.

          Remaining comments:

          1. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L13R1310 I don't immediately see what case this is catching that was not already handled, but I am happy to assume that you have thought about this more deeply than I have.

          2. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L13R1398 $this->attempt->timecheckstate != $time there will be weird issues with 0 and null. !== might be safer than !=.

          3. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L22R98 I am not sure if this is a good change. I think it is useful for teachers to see what students see when time expires. On the other hand, it is there now. If people complain about this change, we can change it back.

          4. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L24R72 Doesn't this logic also get implemented in the access rules, so doing it here too is duplication. Still, as before, it does not hurt to do it here too.

          5. Those unit tests look brilliant!

          6. LOL, I just made a quiz generator in my fix for MDL-30545. We somehow need to merge the two generators, and to do that, we really need to know which order the integrators will process these issues. I think you can leave sorting that out as a problem for me (or the integrators).

          Show
          Tim Hunt added a comment - Sorry it took me so long to peer-review this again. It is looking really, really good now. Remaining comments: 1. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L13R1310 I don't immediately see what case this is catching that was not already handled, but I am happy to assume that you have thought about this more deeply than I have. 2. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L13R1398 $this->attempt->timecheckstate != $time there will be weird issues with 0 and null. !== might be safer than !=. 3. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L22R98 I am not sure if this is a good change. I think it is useful for teachers to see what students see when time expires. On the other hand, it is there now. If people complain about this change, we can change it back. 4. https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L24R72 Doesn't this logic also get implemented in the access rules, so doing it here too is duplication. Still, as before, it does not hurt to do it here too. 5. Those unit tests look brilliant! 6. LOL, I just made a quiz generator in my fix for MDL-30545 . We somehow need to merge the two generators, and to do that, we really need to know which order the integrators will process these issues. I think you can leave sorting that out as a problem for me (or the integrators).
          Hide
          Tim Hunt added a comment -

          So, in order for this to be integrated, we need:

          1. Re-base this down to on, or possibly a few, commits, with a good commit comment.

          2. Cherry-pick to the 2.3 stable branch.

          Given those two, changes, this can then be submitted for integration. If I am not online to do that, I Sam Hemelryk will (I just made him a watcher.)

          3. Sort out the generators thing, but either I will handle that, or the integrators will.

          Once again, thank you very much.

          Show
          Tim Hunt added a comment - So, in order for this to be integrated, we need: 1. Re-base this down to on, or possibly a few, commits, with a good commit comment. 2. Cherry-pick to the 2.3 stable branch. Given those two, changes, this can then be submitted for integration. If I am not online to do that, I Sam Hemelryk will (I just made him a watcher.) 3. Sort out the generators thing, but either I will handle that, or the integrators will. Once again, thank you very much.
          Hide
          Matt Petro added a comment -

          Hi Tim, I just got back in town.

          Thanks for the code review. Responding to the comments:

          1. It's just defining a default behavior, in case quiz->overduehandling isn't what we expect.

          2. Ah, yes. Fixed.

          3. I made the change for consistency. Previously the cron job wouldn't close open preview attempts (and when they are viewed after time expired they have no timelimit), but javascript would close the attempt if it's open in the browser. I changed it so that preview attempts never close. I see your point, and I can revert the change if you think that's better. Perhaps timelimits should close previews, but not quiz close dates? That makes the logic more complicated though, since the two are handled together.

          4. I think this is necessary to prevent processattempt.php from closing preview attempts. Based on your comments before, quizaccess_timelimit->end_time() calls don't have special handling for preview status. Instead, special preview handing happens outside the access rules.

          5. Thanks! I really wanted to write checks for the quiz state change handling, but that would require a quiz attempt generator. Moving the "start attempt" logic from startattempt.php to library functions would be a start in making such a generator, but that seemed like a deeper change than I wanted to take on.

          6. Nice. Very similar code, but yours has more default options. They should keep your generator and test it with my generator_test

          Show
          Matt Petro added a comment - Hi Tim, I just got back in town. Thanks for the code review. Responding to the comments: 1. It's just defining a default behavior, in case quiz->overduehandling isn't what we expect. 2. Ah, yes. Fixed. 3. I made the change for consistency. Previously the cron job wouldn't close open preview attempts (and when they are viewed after time expired they have no timelimit), but javascript would close the attempt if it's open in the browser. I changed it so that preview attempts never close. I see your point, and I can revert the change if you think that's better. Perhaps timelimits should close previews, but not quiz close dates? That makes the logic more complicated though, since the two are handled together. 4. I think this is necessary to prevent processattempt.php from closing preview attempts. Based on your comments before, quizaccess_timelimit->end_time() calls don't have special handling for preview status. Instead, special preview handing happens outside the access rules. 5. Thanks! I really wanted to write checks for the quiz state change handling, but that would require a quiz attempt generator. Moving the "start attempt" logic from startattempt.php to library functions would be a start in making such a generator, but that seemed like a deeper change than I wanted to take on. 6. Nice. Very similar code, but yours has more default options. They should keep your generator and test it with my generator_test
          Hide
          Matt Petro added a comment -

          I've rebased to one commit, and cherry-picked to 2.3. I think we can submit for integration. Thanks!

          Show
          Matt Petro added a comment - I've rebased to one commit, and cherry-picked to 2.3. I think we can submit for integration. Thanks!
          Hide
          Tim Hunt added a comment -

          Thanks Matt.

          Show
          Tim Hunt added a comment - Thanks Matt.
          Hide
          Dan Poltawski 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

          Show
          Dan Poltawski 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
          Hide
          Dan Poltawski added a comment -

          Sorry, I took this in but its bigger than I thought at first glance, so I don't want to rush this in today.

          Show
          Dan Poltawski added a comment - Sorry, I took this in but its bigger than I thought at first glance, so I don't want to rush this in today.
          Hide
          Claus A. Us. added a comment -

          Great to have this problem fixed. It caused some trouble in our system.

          I was wondering, why there is no button to close quiz attempts manually. In case any attempt falls through the cracks, teachers could than close this attempt on their own.

          What do you think?

          Show
          Claus A. Us. added a comment - Great to have this problem fixed. It caused some trouble in our system. I was wondering, why there is no button to close quiz attempts manually. In case any attempt falls through the cracks, teachers could than close this attempt on their own. What do you think?
          Hide
          Eloy Lafuente (stronk7) added a comment - - edited

          +1 to consider this for master only, then test/qa/re-re-review. And if everything goes ok in master, create sister issue about to backport it to 2.3

          But only if it has testing instructions able to reproduce the original problem (see "T1, T2, T3, T4" comment above) in order to verify it's fixed. Plus QAs redone, plus unit tests passing.

          Ciao

          Edited: To add conditions about needed testing instructions.

          Show
          Eloy Lafuente (stronk7) added a comment - - edited +1 to consider this for master only, then test/qa/re-re-review. And if everything goes ok in master, create sister issue about to backport it to 2.3 But only if it has testing instructions able to reproduce the original problem (see "T1, T2, T3, T4" comment above) in order to verify it's fixed. Plus QAs redone, plus unit tests passing. Ciao Edited: To add conditions about needed testing instructions.
          Hide
          Dan Poltawski added a comment -

          (Stalled waiting to see if we can get these testing instructions to verify the problem is fixed.

          Show
          Dan Poltawski added a comment - (Stalled waiting to see if we can get these testing instructions to verify the problem is fixed.
          Hide
          Jean-Michel Vedrine added a comment -

          Hello Eloy and Dan,
          I somewhat understand your reluctance to integrate this in 2.3 but you should also consider that surely a lot of Moodle administrators are tired of manually closing unclosed attempts and have already integrated this or the more brutal solution of commenting the AND iquiza.timemodified >= :processfrom line (see beginning of comments)
          So IMHO the sooner this can be integrated in 2.3 the better because having a lot of unclosed attempts is frustrating (I know we had this issue for years but now that 2.3 was supposed to be the end of it believe me it's frustrating )
          Also seeing the good work of Matt (thank a lot of Matt) with clear phpunit testing and a data generator (thanks again Matt I learned a lot looking at your generator and Tim's one) I am somewhat surprised by your comments : I see a lot of things going into Moodle with less testing units and less security.
          No doubt for me Matt code is better than actual situation.

          Show
          Jean-Michel Vedrine added a comment - Hello Eloy and Dan, I somewhat understand your reluctance to integrate this in 2.3 but you should also consider that surely a lot of Moodle administrators are tired of manually closing unclosed attempts and have already integrated this or the more brutal solution of commenting the AND iquiza.timemodified >= :processfrom line (see beginning of comments) So IMHO the sooner this can be integrated in 2.3 the better because having a lot of unclosed attempts is frustrating (I know we had this issue for years but now that 2.3 was supposed to be the end of it believe me it's frustrating ) Also seeing the good work of Matt (thank a lot of Matt) with clear phpunit testing and a data generator (thanks again Matt I learned a lot looking at your generator and Tim's one) I am somewhat surprised by your comments : I see a lot of things going into Moodle with less testing units and less security. No doubt for me Matt code is better than actual situation.
          Hide
          Matt Petro added a comment - - edited

          I've included testing instructions for the initial problem. Sorry I forgot to include those before.

          I'd also like to see the unit tests run on postgres, oracle, etc as they are testing two really ugly SQL calls. (Dealing with quiz overrides efficiently was challenging.) I've tested on MySQL only.

          Show
          Matt Petro added a comment - - edited I've included testing instructions for the initial problem. Sorry I forgot to include those before. I'd also like to see the unit tests run on postgres, oracle, etc as they are testing two really ugly SQL calls. (Dealing with quiz overrides efficiently was challenging.) I've tested on MySQL only.
          Hide
          Matt Petro added a comment -

          I was wondering, why there is no button to close quiz attempts manually. In case any attempt falls through the cracks, teachers could than close this attempt on their own.

          The goal of this issue is to close the cracks so that that doesn't happen

          Show
          Matt Petro added a comment - I was wondering, why there is no button to close quiz attempts manually. In case any attempt falls through the cracks, teachers could than close this attempt on their own. The goal of this issue is to close the cracks so that that doesn't happen
          Hide
          Dan Poltawski added a comment - - edited

          Wow, conflicting with a quiz generator created in another issue (slightly different but I think they are the same, resolving that conflict).

          I have found a bug in the existing generator (pluginname) from that conflict

          Show
          Dan Poltawski added a comment - - edited Wow, conflicting with a quiz generator created in another issue (slightly different but I think they are the same, resolving that conflict). I have found a bug in the existing generator (pluginname) from that conflict
          Hide
          Dan Poltawski added a comment - - edited
          Debug info: ERROR: syntax error at or near "JOIN"
          LINE 3: JOIN mdl_quiz quiz ON quiz.id = quiza.quiz
          ^
          
          UPDATE mdl_quiz_attempts quiza
          JOIN mdl_quiz quiz ON quiz.id = quiza.quiz
          JOIN ( 
          SELECT iquiza.id,
          COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
          COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
          FROM mdl_quiz_attempts iquiza
          JOIN mdl_quiz iquiz ON iquiz.id = iquiza.quiz
          LEFT JOIN mdl_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
          LEFT JOIN mdl_groups_members gm ON gm.userid = iquiza.userid
          LEFT JOIN mdl_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
          LEFT JOIN mdl_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
          LEFT JOIN mdl_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
          LEFT JOIN mdl_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
          GROUP BY iquiza.id
          ) quizauser ON quiza.id = quizauser.id
          
          SET quiza.timecheckstate = CASE
          WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
          WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
          WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
          WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
          ELSE quizauser.usertimeclose
          END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
          WHERE quiza.state IN ('inprogress', 'overdue')
          
          
          
          
          
          [array (
          )]
          Error code: dmlwriteexception
          Stack trace:
          line 427 of /lib/dml/moodle_database.php: dml_write_exception thrown
          line 243 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
          line 669 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
          line 847 of /mod/quiz/locallib.php: call to pgsql_native_moodle_database->execute()
          line 389 of /mod/quiz/db/upgrade.php: call to quiz_update_open_attempts()
          line 629 of /lib/upgradelib.php: call to xmldb_quiz_upgrade()
          line 360 of /lib/upgradelib.php: call to upgrade_plugins_modules()
          line 1528 of /lib/upgradelib.php: call to upgrade_plugins()
          line 348 of /admin/index.php: call to upgrade_noncore()
          
          Show
          Dan Poltawski added a comment - - edited Debug info: ERROR: syntax error at or near "JOIN" LINE 3: JOIN mdl_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE mdl_quiz_attempts quiza JOIN mdl_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM mdl_quiz_attempts iquiza JOIN mdl_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN mdl_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN mdl_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN mdl_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN mdl_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN mdl_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN mdl_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') [array ( )] Error code: dmlwriteexception Stack trace: line 427 of /lib/dml/moodle_database.php: dml_write_exception thrown line 243 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end() line 669 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end() line 847 of /mod/quiz/locallib.php: call to pgsql_native_moodle_database->execute() line 389 of /mod/quiz/db/upgrade.php: call to quiz_update_open_attempts() line 629 of /lib/upgradelib.php: call to xmldb_quiz_upgrade() line 360 of /lib/upgradelib.php: call to upgrade_plugins_modules() line 1528 of /lib/upgradelib.php: call to upgrade_plugins() line 348 of /admin/index.php: call to upgrade_noncore()
          Hide
          Dan Poltawski added a comment -

          Boom:

          There were 15 errors:
          
          1) conditionlib_testcase::test_section_is_available
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          
                          AND quiza.userid = $1
                          
                          AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2)
                      
          [array (
            0 => '3',
            1 => '1',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1547
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:111
          /Users/danp/git/integration/lib/tests/conditionlib_test.php:727
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit conditionlib_testcase lib/tests/conditionlib_test.php
          
          2) modinfolib_testcase::test_is_user_access_restricted_by_group
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          
                          AND quiza.userid = $1
                          
                          AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2)
                      
          [array (
            0 => '3',
            1 => '2',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1547
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:111
          /Users/danp/git/integration/lib/tests/modinfolib_test.php:103
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit modinfolib_testcase lib/tests/modinfolib_test.php
          
          3) core_course_external_testcase::test_delete_courses
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          
                          
                          
                      
          [array (
            0 => '2',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1587
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/group/lib.php:564
          /Users/danp/git/integration/lib/moodlelib.php:4766
          /Users/danp/git/integration/lib/moodlelib.php:4566
          /Users/danp/git/integration/course/externallib.php:671
          /Users/danp/git/integration/course/tests/externallib_test.php:449
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit core_course_external_testcase course/tests/externallib_test.php
          
          4) enrol_category_testcase::test_handler_sync
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '2',
            1 => '6',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/category/locallib.php:143
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/lib/accesslib.php:1753
          /Users/danp/git/integration/lib/accesslib.php:1696
          /Users/danp/git/integration/enrol/category/tests/sync_test.php:166
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_category_testcase enrol/category/tests/sync_test.php
          
          5) enrol_category_testcase::test_sync_course
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '3',
            1 => '3',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/lib/enrollib.php:1675
          /Users/danp/git/integration/enrol/category/locallib.php:196
          /Users/danp/git/integration/enrol/category/tests/sync_test.php:245
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_category_testcase enrol/category/tests/sync_test.php
          
          6) enrol_category_testcase::test_sync_full
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '4',
            1 => '3',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/category/locallib.php:380
          /Users/danp/git/integration/enrol/category/tests/sync_test.php:332
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_category_testcase enrol/category/tests/sync_test.php
          
          7) enrol_cohort_testcase::test_handler_sync
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '2',
            1 => '4',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/cohort/locallib.php:107
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/cohort/lib.php:158
          /Users/danp/git/integration/enrol/cohort/tests/sync_test.php:148
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_cohort_testcase enrol/cohort/tests/sync_test.php
          
          8) enrol_cohort_testcase::test_sync_course
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '2',
            1 => '3',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/cohort/locallib.php:231
          /Users/danp/git/integration/enrol/cohort/tests/sync_test.php:355
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_cohort_testcase enrol/cohort/tests/sync_test.php
          
          9) enrol_cohort_testcase::test_sync_all_courses
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '2',
            1 => '3',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/cohort/locallib.php:231
          /Users/danp/git/integration/enrol/cohort/tests/sync_test.php:526
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_cohort_testcase enrol/cohort/tests/sync_test.php
          
          10) enrol_database_testcase::test_sync_user_enrolments
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '2',
            1 => '3',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/database/lib.php:266
          /Users/danp/git/integration/enrol/database/tests/sync_test.php:297
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_database_testcase enrol/database/tests/sync_test.php
          
          11) enrol_manual_lib_testcase::test_expired
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '4',
            1 => '5',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/manual/lib.php:334
          /Users/danp/git/integration/enrol/manual/tests/lib_test.php:297
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_manual_lib_testcase enrol/manual/tests/lib_test.php
          
          12) enrol_self_testcase::test_longtimnosee
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '2',
            1 => '3',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/self/lib.php:410
          /Users/danp/git/integration/enrol/self/tests/self_test.php:138
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_self_testcase enrol/self/tests/self_test.php
          
          13) enrol_self_testcase::test_expired
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          AND quiz.course = $1
                          AND quiza.userid = $2
                          
                          
                      
          [array (
            0 => '4',
            1 => '4',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1589
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:521
          /Users/danp/git/integration/lib/enrollib.php:1438
          /Users/danp/git/integration/enrol/self/lib.php:457
          /Users/danp/git/integration/enrol/self/tests/self_test.php:255
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit enrol_self_testcase enrol/self/tests/self_test.php
          
          14) mod_quiz_attempt_overdue_testcase::test_bulk_update_functions
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          
                          AND quiza.userid = $1
                          
                          AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2)
                      
          [array (
            0 => '3',
            1 => '1',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1547
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:111
          /Users/danp/git/integration/mod/quiz/tests/attempts_test.php:62
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit mod_quiz_attempt_overdue_testcase mod/quiz/tests/attempts_test.php
          
          15) mod_quiz_attempt_overdue_testcase::test_group_event_handlers
          dml_write_exception: Error writing to database (ERROR:  syntax error at or near "JOIN"
          LINE 3:            JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                             ^
          
                   UPDATE phpb_quiz_attempts quiza
                     JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                     JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id
                  ) quizauser ON quiza.id = quizauser.id
          
                      SET quiza.timecheckstate = CASE
                     WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                     WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                     WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                     WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                     ELSE quizauser.usertimeclose
                      END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
          
                    WHERE quiza.state IN ('inprogress', 'overdue')
                          
                          AND quiza.userid = $1
                          
                          AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2)
                      
          [array (
            0 => '3',
            1 => '1',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:427
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243
          /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:847
          /Users/danp/git/integration/mod/quiz/locallib.php:1547
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:111
          /Users/danp/git/integration/mod/quiz/tests/attempts_test.php:340
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           phpunit mod_quiz_attempt_overdue_testcase mod/quiz/tests/attempts_test.php
          
          Show
          Dan Poltawski added a comment - Boom: There were 15 errors: 1) conditionlib_testcase::test_section_is_available dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiza.userid = $1 AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2) [array ( 0 => '3', 1 => '1', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1547 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:111 /Users/danp/git/integration/lib/tests/conditionlib_test.php:727 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit conditionlib_testcase lib/tests/conditionlib_test.php 2) modinfolib_testcase::test_is_user_access_restricted_by_group dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiza.userid = $1 AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2) [array ( 0 => '3', 1 => '2', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1547 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:111 /Users/danp/git/integration/lib/tests/modinfolib_test.php:103 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit modinfolib_testcase lib/tests/modinfolib_test.php 3) core_course_external_testcase::test_delete_courses dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 [array ( 0 => '2', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1587 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/group/lib.php:564 /Users/danp/git/integration/lib/moodlelib.php:4766 /Users/danp/git/integration/lib/moodlelib.php:4566 /Users/danp/git/integration/course/externallib.php:671 /Users/danp/git/integration/course/tests/externallib_test.php:449 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit core_course_external_testcase course/tests/externallib_test.php 4) enrol_category_testcase::test_handler_sync dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '2', 1 => '6', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/category/locallib.php:143 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/lib/accesslib.php:1753 /Users/danp/git/integration/lib/accesslib.php:1696 /Users/danp/git/integration/enrol/category/tests/sync_test.php:166 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_category_testcase enrol/category/tests/sync_test.php 5) enrol_category_testcase::test_sync_course dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '3', 1 => '3', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/lib/enrollib.php:1675 /Users/danp/git/integration/enrol/category/locallib.php:196 /Users/danp/git/integration/enrol/category/tests/sync_test.php:245 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_category_testcase enrol/category/tests/sync_test.php 6) enrol_category_testcase::test_sync_full dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '4', 1 => '3', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/category/locallib.php:380 /Users/danp/git/integration/enrol/category/tests/sync_test.php:332 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_category_testcase enrol/category/tests/sync_test.php 7) enrol_cohort_testcase::test_handler_sync dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '2', 1 => '4', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/cohort/locallib.php:107 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/cohort/lib.php:158 /Users/danp/git/integration/enrol/cohort/tests/sync_test.php:148 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_cohort_testcase enrol/cohort/tests/sync_test.php 8) enrol_cohort_testcase::test_sync_course dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '2', 1 => '3', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/cohort/locallib.php:231 /Users/danp/git/integration/enrol/cohort/tests/sync_test.php:355 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_cohort_testcase enrol/cohort/tests/sync_test.php 9) enrol_cohort_testcase::test_sync_all_courses dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '2', 1 => '3', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/cohort/locallib.php:231 /Users/danp/git/integration/enrol/cohort/tests/sync_test.php:526 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_cohort_testcase enrol/cohort/tests/sync_test.php 10) enrol_database_testcase::test_sync_user_enrolments dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '2', 1 => '3', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/database/lib.php:266 /Users/danp/git/integration/enrol/database/tests/sync_test.php:297 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_database_testcase enrol/database/tests/sync_test.php 11) enrol_manual_lib_testcase::test_expired dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '4', 1 => '5', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/manual/lib.php:334 /Users/danp/git/integration/enrol/manual/tests/lib_test.php:297 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_manual_lib_testcase enrol/manual/tests/lib_test.php 12) enrol_self_testcase::test_longtimnosee dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '2', 1 => '3', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/self/lib.php:410 /Users/danp/git/integration/enrol/self/tests/self_test.php:138 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_self_testcase enrol/self/tests/self_test.php 13) enrol_self_testcase::test_expired dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiz.course = $1 AND quiza.userid = $2 [array ( 0 => '4', 1 => '4', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1589 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:521 /Users/danp/git/integration/lib/enrollib.php:1438 /Users/danp/git/integration/enrol/self/lib.php:457 /Users/danp/git/integration/enrol/self/tests/self_test.php:255 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit enrol_self_testcase enrol/self/tests/self_test.php 14) mod_quiz_attempt_overdue_testcase::test_bulk_update_functions dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiza.userid = $1 AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2) [array ( 0 => '3', 1 => '1', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1547 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:111 /Users/danp/git/integration/mod/quiz/tests/attempts_test.php:62 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit mod_quiz_attempt_overdue_testcase mod/quiz/tests/attempts_test.php 15) mod_quiz_attempt_overdue_testcase::test_group_event_handlers dml_write_exception: Error writing to database (ERROR: syntax error at or near "JOIN" LINE 3: JOIN phpb_quiz quiz ON quiz.id = quiza.quiz ^ UPDATE phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(quo.timeclose, MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(quo.timelimit, MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id ) quizauser ON quiza.id = quizauser.id SET quiza.timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END WHERE quiza.state IN ('inprogress', 'overdue') AND quiza.userid = $1 AND quiz.id IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = $2) [array ( 0 => '3', 1 => '1', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:427 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:243 /Users/danp/git/integration/lib/dml/pgsql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:847 /Users/danp/git/integration/mod/quiz/locallib.php:1547 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:111 /Users/danp/git/integration/mod/quiz/tests/attempts_test.php:340 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: phpunit mod_quiz_attempt_overdue_testcase mod/quiz/tests/attempts_test.php
          Hide
          Dan Poltawski added a comment -

          Sorry, this does not look ready on postgres.

          Show
          Dan Poltawski added a comment - Sorry, this does not look ready on postgres.
          Hide
          Matt Petro added a comment -

          Gah! Thanks for testing it. I'll rewrite that to use a subquery.

          Show
          Matt Petro added a comment - Gah! Thanks for testing it. I'll rewrite that to use a subquery.
          Hide
          CiBoT added a comment -

          Moving this reopened issue out from current integration. Please, re-submit it for integration once ready.

          Show
          CiBoT added a comment - Moving this reopened issue out from current integration. Please, re-submit it for integration once ready.
          Hide
          Matt Petro added a comment -

          I fixed the two big SQL calls so that they work in postgres and verified that the phpunit tests pass. I learned a lot from doing this about writing portable sql.

          Tim, the changes are all in quiz_update_open_attempts() and get_list_of_overdue_attempts() functions, both of which are pretty well exercised by the unit tests. I don't think the latest changes will have introduced any new bugs.

          Show
          Matt Petro added a comment - I fixed the two big SQL calls so that they work in postgres and verified that the phpunit tests pass. I learned a lot from doing this about writing portable sql. Tim, the changes are all in quiz_update_open_attempts() and get_list_of_overdue_attempts() functions, both of which are pretty well exercised by the unit tests. I don't think the latest changes will have introduced any new bugs.
          Hide
          Matt Petro added a comment -

          It turns out that each DB handles UPDATE with inner joins differently. What a pain! I really wanted to keep the batch update as a single efficient SQL call since it gets called from group event handlers and when changing quiz settings, and the number of attempts may be huge. Trying to support all the types of dbs led to a nasty subquery that would likely be inefficient in all dbs (except maybe oracle). So, I ended up writing a separate update for each DB type. There is some precedent for this in moodle (e.g. accesslib.h merge_context_temp_table()).

          With the latest version, the unit tests pass in:

          Oracle 11g rev 2
          Mysql 5.5
          Postgres 9.1
          SQL Server Express 2008

          Show
          Matt Petro added a comment - It turns out that each DB handles UPDATE with inner joins differently. What a pain! I really wanted to keep the batch update as a single efficient SQL call since it gets called from group event handlers and when changing quiz settings, and the number of attempts may be huge. Trying to support all the types of dbs led to a nasty subquery that would likely be inefficient in all dbs (except maybe oracle). So, I ended up writing a separate update for each DB type. There is some precedent for this in moodle (e.g. accesslib.h merge_context_temp_table()). With the latest version, the unit tests pass in: Oracle 11g rev 2 Mysql 5.5 Postgres 9.1 SQL Server Express 2008
          Hide
          Tim Hunt added a comment -
          Show
          Tim Hunt added a comment - The key new bit of Matt's commit is https://github.com/mpetrowi/moodle/compare/master...MDL-35717-quiz-attempt-checkstate#L21R831 .
          Hide
          Tim Hunt added a comment -

          Putting it back into integration. The integrators need to consider this.

          Show
          Tim Hunt added a comment - Putting it back into integration. The integrators need to consider this.
          Hide
          Dan Poltawski added a comment -

          Bah, still got that generator conflict

          Show
          Dan Poltawski added a comment - Bah, still got that generator conflict
          Hide
          Dan Poltawski added a comment -

          This is not working on mssql:

          16) mod_quiz_attempt_overdue_testcase::test_bulk_update_functions
          dml_write_exception: Error writing to database (Invalid column name 'timecheckstate'.
          UPDATE quiza
                                   SET timecheckstate = 
                    CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
                         WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
                         WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
                         WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
                         ELSE quizauser.usertimeclose END +
                    CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END
                                  FROM phpb_quiz_attempts quiza
                                  JOIN phpb_quiz quiz ON quiz.id = quiza.quiz
                                  JOIN ( 
                    SELECT iquiza.id,
                     COALESCE(MAX(quo.timeclose), MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose,
                     COALESCE(MAX(quo.timelimit), MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit
          
                     FROM phpb_quiz_attempts iquiza
                     JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz
                LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid
                LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid
                LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0
                LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0
                LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0
                LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0
                 GROUP BY iquiza.id, iquiz.id, iquiz.timeclose, iquiz.timelimit ) quizauser ON quizauser.id = quiza.id
                                 WHERE  quiza.state IN ('inprogress', 'overdue')
                                 
                                 AND quiza.userid = ?
                                 
                                 AND quiza.quiz IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = ?)
          [array (
            0 => '3',
            1 => '1',
          )])
          
          /Users/danp/git/integration/lib/dml/moodle_database.php:429
          /Users/danp/git/integration/lib/dml/mssql_native_moodle_database.php:256
          /Users/danp/git/integration/lib/dml/mssql_native_moodle_database.php:669
          /Users/danp/git/integration/mod/quiz/locallib.php:873
          /Users/danp/git/integration/mod/quiz/locallib.php:1598
          /Users/danp/git/integration/lib/eventslib.php:299
          /Users/danp/git/integration/lib/eventslib.php:519
          /Users/danp/git/integration/group/lib.php:111
          /Users/danp/git/integration/mod/quiz/tests/attempts_test.php:62
          /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76
          
          To re-run:
           /usr/local/Cellar/php53/5.3.16/bin/phpunit mod_quiz_attempt_overdue_testcase mod/quiz/tests/attempts_test.php
          
          Show
          Dan Poltawski added a comment - This is not working on mssql: 16) mod_quiz_attempt_overdue_testcase::test_bulk_update_functions dml_write_exception: Error writing to database (Invalid column name 'timecheckstate'. UPDATE quiza SET timecheckstate = CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit ELSE quizauser.usertimeclose END + CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END FROM phpb_quiz_attempts quiza JOIN phpb_quiz quiz ON quiz.id = quiza.quiz JOIN ( SELECT iquiza.id, COALESCE(MAX(quo.timeclose), MAX(qgo1.timeclose), MAX(qgo2.timeclose), iquiz.timeclose) AS usertimeclose, COALESCE(MAX(quo.timelimit), MAX(qgo3.timelimit), MAX(qgo4.timelimit), iquiz.timelimit) AS usertimelimit FROM phpb_quiz_attempts iquiza JOIN phpb_quiz iquiz ON iquiz.id = iquiza.quiz LEFT JOIN phpb_quiz_overrides quo ON quo.quiz = iquiza.quiz AND quo.userid = iquiza.userid LEFT JOIN phpb_groups_members gm ON gm.userid = iquiza.userid LEFT JOIN phpb_quiz_overrides qgo1 ON qgo1.quiz = iquiza.quiz AND qgo1.groupid = gm.groupid AND qgo1.timeclose = 0 LEFT JOIN phpb_quiz_overrides qgo2 ON qgo2.quiz = iquiza.quiz AND qgo2.groupid = gm.groupid AND qgo2.timeclose > 0 LEFT JOIN phpb_quiz_overrides qgo3 ON qgo3.quiz = iquiza.quiz AND qgo3.groupid = gm.groupid AND qgo3.timelimit = 0 LEFT JOIN phpb_quiz_overrides qgo4 ON qgo4.quiz = iquiza.quiz AND qgo4.groupid = gm.groupid AND qgo4.timelimit > 0 GROUP BY iquiza.id, iquiz.id, iquiz.timeclose, iquiz.timelimit ) quizauser ON quizauser.id = quiza.id WHERE quiza.state IN ('inprogress', 'overdue') AND quiza.userid = ? AND quiza.quiz IN (SELECT qo.quiz FROM phpb_quiz_overrides qo WHERE qo.groupid = ?) [array ( 0 => '3', 1 => '1', )]) /Users/danp/git/integration/lib/dml/moodle_database.php:429 /Users/danp/git/integration/lib/dml/mssql_native_moodle_database.php:256 /Users/danp/git/integration/lib/dml/mssql_native_moodle_database.php:669 /Users/danp/git/integration/mod/quiz/locallib.php:873 /Users/danp/git/integration/mod/quiz/locallib.php:1598 /Users/danp/git/integration/lib/eventslib.php:299 /Users/danp/git/integration/lib/eventslib.php:519 /Users/danp/git/integration/group/lib.php:111 /Users/danp/git/integration/mod/quiz/tests/attempts_test.php:62 /Users/danp/git/integration/lib/phpunit/classes/advanced_testcase.php:76 To re-run: /usr/local/Cellar/php53/5.3.16/bin/phpunit mod_quiz_attempt_overdue_testcase mod/quiz/tests/attempts_test.php
          Hide
          Dan Poltawski added a comment -

          Actually, that sounds suspiciously like version number problem for me.

          Show
          Dan Poltawski added a comment - Actually, that sounds suspiciously like version number problem for me.
          Hide
          Dan Poltawski added a comment -

          Yep, somehow phpunit in a bad state (possibly from last time I tested this!). I reinitialised the environment and it worked fine.

          So thanks i've integrated this now!

          Show
          Dan Poltawski added a comment - Yep, somehow phpunit in a bad state (possibly from last time I tested this!). I reinitialised the environment and it worked fine. So thanks i've integrated this now!
          Hide
          Tim Hunt added a comment -

          Yay! finally!

          Thanks Dan.

          Will make a new ticket for back-porting this once it has been proved in master.

          Show
          Tim Hunt added a comment - Yay! finally! Thanks Dan. Will make a new ticket for back-porting this once it has been proved in master.
          Hide
          Tim Hunt added a comment -

          MDL-36842 created.

          Show
          Tim Hunt added a comment - MDL-36842 created.
          Hide
          Matt Petro added a comment -

          Yay, I finally got the sql right! Tim, thanks for the excellent suggestions on design, and Dan, thanks for the review and integration.

          Show
          Matt Petro added a comment - Yay, I finally got the sql right! Tim, thanks for the excellent suggestions on design, and Dan, thanks for the review and integration.
          Hide
          Dan Poltawski added a comment -

          Matt: thanks a lot for diagnosing and taking on such a painful issue!

          Show
          Dan Poltawski added a comment - Matt: thanks a lot for diagnosing and taking on such a painful issue!
          Hide
          David Monllaó added a comment -

          Hi,

          I can't find any QA test from MDLQA-4166 to MDLQA-4177, any info about that happened with them?

          Show
          David Monllaó added a comment - Hi, I can't find any QA test from MDLQA-4166 to MDLQA-4177, any info about that happened with them?
          Hide
          David Monllaó added a comment -

          Ok, I've found them with other ids

          Show
          David Monllaó added a comment - Ok, I've found them with other ids
          Hide
          David Monllaó added a comment -

          It passes, it was long (happy to see is only in master)

          Followed testing instructions and all the QA tests that involves cron execution:

          Show
          David Monllaó added a comment - It passes, it was long (happy to see is only in master) Followed testing instructions and all the QA tests that involves cron execution: MDLQA-4854 MDLQA-4856 MDLQA-4858 MDLQA-4860 MDLQA-4862 MDLQA-4864
          Hide
          Tim Hunt added a comment -

          Actually, those are only about half of them. There are some more tests involving the 'overdue' state.

          Show
          Tim Hunt added a comment - Actually, those are only about half of them. There are some more tests involving the 'overdue' state.
          Hide
          David Monllaó added a comment -

          Ok, I've followed the testing instructions and that other half was not there, I've to go home, tomorrow morning I'll finish them

          Show
          David Monllaó added a comment - Ok, I've followed the testing instructions and that other half was not there, I've to go home, tomorrow morning I'll finish them
          Hide
          Tim Hunt added a comment -

          Thanks David.

          Show
          Tim Hunt added a comment - Thanks David.
          Hide
          David Monllaó added a comment - - edited

          Hi,

          Adding:
          MDLQA-4853
          MDLQA-4855
          MDLQA-4857
          MDLQA-4859
          MDLQA-4861
          MDLQA-4863

          All working as expected

          Show
          David Monllaó added a comment - - edited Hi, Adding: MDLQA-4853 MDLQA-4855 MDLQA-4857 MDLQA-4859 MDLQA-4861 MDLQA-4863 All working as expected
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Y E S !

          Closing as fixed, many thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Y E S ! Closing as fixed, many thanks!
          Hide
          Lindy Klein added a comment -

          Hi folks,

          We are experiencing this issue on a client site (approx 400+ users). Applied the patch (the site is now up to version Moodle 2.3.8+ (Build: 20130815)), but we still have some quizzes (not all) where the attempts will not close for folks enrolled as students.

          They have no third party plugins (apart from a custom theme), so my belief is this is a core Moodle bug. I can supply videos and screenshots if it will help to get it resolved.

          All the best,

          Lindy

              • Issue summary ***

          Quiz previously had no close date, 89 student attempts made on it. Unlimited attempts, highest grade counts. Subsequently, close date was set for the quiz, regrade all attempts run, still 18 attempts returned as Never submitted. Changed duration of quiz to be 30 days, when time expires set to Open attempts submitted automatically. Still 18 attempts returned as Never submitted.

          Sent a prayer to Moodle gods for quiz relief...

          Show
          Lindy Klein added a comment - Hi folks, We are experiencing this issue on a client site (approx 400+ users). Applied the patch (the site is now up to version Moodle 2.3.8+ (Build: 20130815)), but we still have some quizzes (not all) where the attempts will not close for folks enrolled as students. They have no third party plugins (apart from a custom theme), so my belief is this is a core Moodle bug. I can supply videos and screenshots if it will help to get it resolved. All the best, Lindy Issue summary *** Quiz previously had no close date, 89 student attempts made on it. Unlimited attempts, highest grade counts. Subsequently, close date was set for the quiz, regrade all attempts run, still 18 attempts returned as Never submitted. Changed duration of quiz to be 30 days, when time expires set to Open attempts submitted automatically. Still 18 attempts returned as Never submitted. Sent a prayer to Moodle gods for quiz relief...

            People

            • Votes:
              9 Vote for this issue
              Watchers:
              16 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: