09/18/2024

MariaDB vs. MySQL 8: GROUP BY sorting wrong

I just fell over some quite funny behaviour. In my local development environment i used MariaDB 10.3. The server environment used MySQL 8.0. I had a query with a `GROUP BY` statement:

SELECT SUBSTR(short, 1, 1) AS `char` FROM tx_a21glossary_main GROUP BY `char`

The output with MariaDB 10.3 was

+------+
| char |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
| F    |
| G    |
| H    |
| I    |
| J    |
| K    |
| L    |
| M    |
| N    |
| O    |
| P    |
| Q    |
| R    |
| S    |
| T    |
| U    |
| V    |
| W    |
| X    |
| Z    |
+------+

The output with MySQL 8.0 was:

+------+
| char |
+------+
| B    |
| F    |
| W    |
| Z    |
| C    |
| E    |
| T    |
| A    |
| I    |
| G    |
| L    |
| M    |
| R    |
| O    |
| P    |
| S    |
| K    |
| N    |
| D    |
| U    |
| V    |
| X    |
| H    |
| J    |
| Q    |
+------+

It seems, that MariaDB applies some kind of default sorting, when using `GROUP BY`.

However, this is clearly a mistake in the application/query but funny anyway. So I just wanted to share it here.