Different ResultSet in MySQL and MariaDB Using GROUP BY and ORDER BY

Question

I have a table: voj_submission with following structure:

CREATE TABLE IF NOT EXISTS `voj_submissions` (
    `submission_id` bigint(20) NOT NULL,
    `problem_id` bigint(20) NOT NULL,
    `uid` bigint(20) NOT NULL,
    `submission_judge_result` varchar(8) DEFAULT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8;

And the data in the table may like this:

SubmissionID ProblemID UserID JudgeResult
1000         1000      User1   AC
1001         1000      User1   WA
1002         1000      User2   CE
1003         1001      User1   AC

And following SQL wants to get the latest judge result of problems:

SELECT submission_id, problem_id, submission_judge_result 
FROM (
    SELECT * FROM 
    voj_submissions 
    ORDER BY submission_id DESC 
) s 
WHERE uid = 'User1'
AND problem_id >= 1000
AND problem_id < 1010
GROUP BY problem_id

In MySQL 5.6, it works properly and returns following result set:

SubmissionID ProblemID JudgeResult
1001         1000      WA
1003         1001      AC

But in MariaDB 10.0.14, it returns:

SubmissionID ProblemID JudgeResult
1000         1000      AC
1003         1001      AC

That means the ORDER BY DESC didn't execute in MariaDB. How can I solve this problem?

Reference:

Where a GROUP BY clause is used in conjunction with a selected column that is neither aggregated nor referenced in the GROUP BY, the result is indeterminate, and cannot be relied upon That said, I've never seen this hack not work before, so - a refreshing change. Anyway... now to answer your question... — Strawberry, May 05 '15 at 09:10
The two "best" solutions are found in [my groupwise-max blog](http://mysql.rjweb.org/doc.php/groupwise_max). — Rick James, May 06 '15 at 01:05

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

As @bluefeet said in this post:

Using an ORDER BY in a subquery is not the best solution to this problem.

The best solution to get the max(post_date) by author is to use a subquery to return the max date and then join that to your table on both the post_author and the max date.

So the solution should be:

SELECT submission_id, s1.problem_id, submission_judge_result
FROM voj_submissions  s1
INNER JOIN (
    SELECT MAX(submission_id) AS max_submission_id, problem_id
    FROM voj_submissions
    WHERE uid = 1000
    AND problem_id >= 1000
    AND problem_id < 1010
    GROUP BY problem_id
) s2 
ON s1.problem_id = s2.problem_id
AND s1.submission_id = s2.max_submission_id

And this works in both MariaDB and MySQL.

This assumes that submission_id is PRIMARY (which it should be) — Strawberry, May 05 '15 at 09:15

Different ResultSet in MySQL and MariaDB Using GROUP BY and ORDER BY

1 Answers1