Changing the Mysql version

Question

I have this table in Mysql called logou:

id | idconta | dia

This query in Mysql always worked on my server:

SELECT dia FROM logou GROUP BY YEAR(dia), MONTH(dia) ORDER BY dia DESC

But now that the system has gone to the client server, and everything indicates that it is another version of Mysql or a SQL Server (can it be? Client is from Belgium, and are VERY boring in talking details of their server), this query presents this error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'eusoucomp-br-qa.logou.dia' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What can it be? And how to fix?

Answer 1

Starting with Mysql 5.7 the default behavior of Mysql has been modified and the mode ONLY_FULL_GROUP_BY inserted by default. If you do:

mysql> SET sql_mode = '';

And it will be possible to run the query without errors (but you will need to do it every session).

The best solution is to do something like this using the ANY_VALUE function():

SELECT ANY_VALUE(dia) as dia FROM logou group by year(dia), month(dia) ORDER BY dia DESC;

I did the tests here using version 5.7.21-0ubuntu0.17.10.1 and, interestingly, in version 10.1.30-0ubuntu0.17.10.1 of Mariadb this problem does not even occur.