嵌套子查询中的MySQL变量

Question

Currently I'm working on kind of e-learning platform which contain courses and exams module. The idea is that course is followed by exam. User can solve exam few times (depending on setting in exam table). I have a module where I need to determine whether user should be redirected to exam or statistics page. If user hasn't use all his attempts he should be redirected to exam, otherwise to stats.

So here is my query (little simplified, since all conditions and joins of outer query doesn't matter here) which should determine where to go.

SELECT 
 @course_id := courses.id as id,
 IF(
      (SELECT X.attempts_count FROM
           (SELECT
           COUNT(exams_attempts.id) as attempts_count,
           @max_attempts := exams.max_attempts
           FROM exams
           LEFT JOIN exams_attempts ON exams.id = exams_attempts.quiz_id
           JOIN users ON exams_attempts.user_id = users.id
           WHERE exams_attempts.user_id = 12
           AND exams_attempts.course_id = @course_id
           HAVING attempts_count >= @max_attempts) as X
      ),
      'stats',
      'exam'
 ) as redirect 
FROM courses
WHERE courses.id = 1

For testing reasons I limited courses to a static value, but in actual usage it's big amount of courses to look up.

And finally - I discovered that this works on localhost, but not on server, despite the same MySql version. I would like to know if there is some setting that prevents my query from being executed properly. Also I would like to know what do you suggest, maybe my idea is not good and I could rebuild that query?

Answer 1

This can be made to work.

The deal is with the following concept from the manual User-Defined Variables:

In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

And with variable cleansing. See the blog from Baron that is Obligatory Reading. Consider nested if statements and the use of least(), greatest(), and coalesce() it such efforts for safe variable handling.

Many of these mysql-variables questions can take some time to write safely. Like an hour, or half a day. Just getting the answer right once is not equivalent to Production Ready code. I created a tag a while ago to house them as I restumble into them or write a few.