MySQL version change [duplicate]

Question

MySQL version change [duplicate]

Navigation

#1 by (1 votes)

0

I have this table in MySQL called logou:

id | idconta | day

This query in Mysql always fucnionou on my server:

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

But now that the system has been 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 annoying in talking details of their server), this query displays 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 correct?

mysql

asked by anonymous 23.03.2018 / 21:17

1 answer

2 Arrays in foreach from a form How to put quotation marks in a message

score 1 · Accepted Answer

As of MySQL 5.7 the default behavior of MySQL has been modified and ONLY_FULL_GROUP_BY mode is entered by default. If you do:

mysql> SET sql_mode = '';

And you will be able to run the query without errors (but you will need to do this 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, curiously, in MariaDB version 10.1.30-0ubuntu0.17.10.1 this problem does not even occur.