为何不同JOIN语句会对ORDER BY子句产生不同影响？

我正在SQLZOO上学习SQL，第13道练习题一直卡着我，直到试出了解决方案。一开始我怀疑是ORDER BY子句写法错误或者数据类型不对，后来找到了可行的解决方案——只修改了JOIN语句并移除ORDER BY子句就匹配了要求，但至今还是搞不懂最初的ORDER BY为什么没法正确对日期排序。

相关习题集来自SQLZOO的《JOIN操作》章节。

我的初始代码 #

SELECT mdate,
  team1,
  SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
  team2,
  SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
FROM game JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2
ORDER BY mdate, matchid, team1, team2

解决方案代码 #

SELECT mdate, 
   team1,
   SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1,
   team2,
   SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score2
FROM game LEFT JOIN goal ON matchid = id 
GROUP BY mdate, matchid, team1, team2

内容的提问来源于stack exchange，提问作者professor.luther.ratigan