-
Notifications
You must be signed in to change notification settings - Fork 171
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Multiple correct submissions cause SQL exception #41
Comments
Easier repro steps: |
Tracked down the bug. It is in this code: $challenges = db_query_fetch_all('
SELECT
c.id,
c.title,
c.description,
c.available_from,
c.available_until,
c.points,
c.num_attempts_allowed,
c.min_seconds_between_submissions,
c.automark,
c.relies_on,
IF(c.automark = 1, 0, (SELECT ss.id FROM submissions AS ss WHERE ss.challenge = c.id AND ss.user_id = :user_id_1 AND ss.marked = 0)) AS unmarked, -- a submission is waiting to be marked
(SELECT ss.added FROM submissions AS ss WHERE ss.challenge = c.id AND ss.user_id = :user_id_2 AND ss.correct = 1) AS correct_submission_added, -- a correct submission has been made
(SELECT COUNT(*) FROM submissions AS ss WHERE ss.challenge = c.id AND ss.user_id = :user_id_3) AS num_submissions, -- number of submissions made
(SELECT max(ss.added) FROM submissions AS ss WHERE ss.challenge = c.id AND ss.user_id = :user_id_4) AS latest_submission_added
FROM challenges AS c
WHERE c.category = :category
ORDER BY c.points ASC, c.id ASC',
array(
'user_id_1'=>$_SESSION['id'],
'user_id_2'=>$_SESSION['id'],
'user_id_3'=>$_SESSION['id'],
'user_id_4'=>$_SESSION['id'],
'category'=>$current_category['id']
) The problem is that if there are multiple correct submissions, a subquery returns more than one row where MySQL requires just one return row, and so it causes an error. This cascades into an unrelated error message which is a really annoying "feature" of MySQL. Here is the problem subquery: (SELECT ss.added FROM submissions AS ss WHERE ss.challenge = c.id AND ss.user_id = :user_id_2 AND ss.correct = 1) Here is the fix: (SELECT ss.added FROM submissions AS ss WHERE ss.challenge = c.id AND ss.user_id = :user_id_2 AND ss.correct = 1 LIMIT 1) I'll submit a pull request. |
Put LIMIT 1 into the subquery, so that only one correct submission is returned from the subquery, so that the main query doesn't fail when there is more than one correct submission.
Hi! Thanks for your work. I think this hides the underlying problem. There needs to be a more reliable way to stop duplicate correct entries. I'd suggest implementing:
|
If there are multiple correct submissions to the same challenge by the same user, a persistent misleading SQL exception occurs. This does not happen for multiple incorrect submissions. The exception occurs while rendering the Challenges page, while attempting to render the challenge that has multiple correct submissions. The exception causes the user to be unable to view challenges in the same category that happen after the one with multiple correct submissions.
Exception text:
Exception Trace:
Repro:
There are multiple ways that this can occur.
Fix:
Not sure yet. It should be easy to reproduce but I haven't looked at the code yet.
The text was updated successfully, but these errors were encountered: