MySQL >= 5.7 and MariaDB issues

[bonny]

Some issues arise when using MySQL 5.7 and newer or MariaDB 10.5.

There seems to be related to changes how right join work.
In MySQL 5.5 the group by groups by the latest/highest id of all events with same occasions id, but in more recent DBs the group by uses the lowest id in each occasions id group.

It's probably not ONLY_FULL_GROUP_BY because the queries are not rejected. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Some threads with possibly related issues:

• https://stackoverflow.com/questions/66349624/mysql-group-by-difference-in-mysql-5-to-mysql-8 (sounds like the same problem)
• https://stackoverflow.com/questions/27314820/different-result-query-when-use-mysql-and-mariadb
• https://stackoverflow.com/questions/46467782/different-results-for-having-with-subquery-between-mysql-5-7-and-5-6
• https://stackoverflow.com/questions/37010318/mysql-group-by-functionality-in-different-version
• https://stackoverflow.com/questions/54843627/how-to-select-the-first-of-duplicate-rows-in-mysql-but-where-duplicate-data-is
• https://stackoverflow.com/questions/13151928/show-first-instance-of-records-with-duplicate-values-in-colum
• https://stackoverflow.com/questions/1379565/how-to-fetch-the-first-and-last-record-of-a-grouped-record-in-a-mysql-query-with
• https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql
• https://stackoverflow.com/questions/24913290/mysql-wrong-results-with-group-by-and-order-by
• https://stackoverflow.com/questions/24913290/mysql-wrong-results-with-group-by-and-order-by
• https://stackoverflow.com/questions/49398077/mariadb-and-mysql5-6-returns-different-results
• https://stackoverflow.com/questions/38719922/same-query-returns-different-results-mysql-group-by
• https://stackoverflow.com/questions/28244866/why-does-group-by-with-left-join-change-the-result-of-this-mysql-query
• https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc
• https://stackoverflow.com/questions/35882816/how-to-disable-only-full-group-by-in-mysql-or-sequelize

Related GH issues:

• Date filter does not work on MariaDB #405
• Sometimes always "1 new event" #397
• Optimize log query #312

Could we use https://www.db-fiddle.com/ to created small example to easy test with?