14. SQL JOINs - Error Code: 1055 (only_full_group_by)

Hi Thomas!

Thanks for reaching out.

Depending on your operating system and version of MySQL, you will be working with different SQL settings.

To make sure you can take some of the remaining lectures of the course without unnecessary interruption, we strongly advise you to execute the following query now.

set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');

If you’re interested in the reason we are asking you to do this, you can read the technical description below. Please be aware that it may sound a bit too abstract at this stage. However, you can always refer back to it once you have completed the lectures about the different types of SQL variables found in the Advanced SQL Tools section of the course.

Technical description:

The GROUP BY clause will be used in several queries by the end of the course. Different versions of the SQL language and MySQL, in particular, can set different limits on how the GROUP BY clause can be applied.

In some of the lectures in the JOINs section, we have opted for queries where multiple table columns that are not included in the GROUP BY clause will be listed in the SELECT statement. We have decided this is the correct approach to teaching the use of the GROUP BY clause at this stage. At the same time, we are aware some versions of MySQL may not allow such queries, or at least not by default.

To adjust the relevant default settings, there is a system variable, called ‘sql_mode’, which needs to be reconfigured.

In order to view the current value of this variable in your case, you have to execute the following command.

select @@global.sql_mode;

An expression containing a few values, separated by commas, will appear in the result grid. They correspond to various MySQL settings that influence the way in which MySQL will behave in different situations.

One of these values, ‘only_full_group_by’, blocks certain type of group statements and that can potentially lead to Error Code 1055. The latter signifies the problem of listing fields in the SELECT statement that are not included in the GROUP BY clause.

Naturally, we want to avoid that. Hence, to prevent it from happening, we must execute the following statement.

set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');

REPLACE() is the function that will remove the “only_full_group_by” value from the expression here. Thus, error 1055 will not show up in the future.

Finally, if for some reason you’d like to disallow this behavior you can always execute the following command which will do exactly the opposite: it will add the “only_full_group_by” value to the expression.

set @@global.sql_mode := concat('ONLY_FULL_GROUP_BY,', @@global.sql_mode);

That said, we must also add that there is a reason behind this functionality. If you think about it, it is not logical to allow a column value to be listed in the output alongside a value that has been included in the GROUP BY clause of the query. We just cannot be sure that the not-grouped value that has been retrieved is going to be correct. But for the sake of our exercises, and for the purpose of making them clearer, we have allowed such syntax.

Hope this helps.
Best,
Martin

Martin, I got the same error message as Thomas. I executed the query set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''); and I still receive Error 1055. Is there another method?

Hi Emma!

Thanks for reaching out.

In that case, since you may have been using a different setting, did you try the opposite command - set @@global.sql_mode := concat('ONLY_FULL_GROUP_BY,', @@global.sql_mode);
Please retry with this command and let us know if you need further assistance. Thank

Hope this helps.
Best,
Martin

Hi Martin, I had also experienced the same error and tried the solution as you sugested. But it didn't work out and error 1055 is still there. But when I put distinct function, I got the same result as you (20 records). I am not sure whether it is the correct way to do it or not. Here is my query:

SELECT
    DISTINCT m.dept_no, m.emp_no, d.dept_name
FROM
    dept_manager_dup m
        JOIN
    departments_dup d ON m.dept_no = d.dept_no
ORDER BY dept_no;

Thanks.

I'm getting error 1055 as well trying out the solution, the reverse, then the solution again. I'm using Ubuntu 20.04. However, I found a solution just after one search:

From: https://stackoverflow.com/questions/36207042/error-code-1055-incompatible-with-sql-mode-only-full-group-by
execute this query:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Hope it helps.

Hi Zayar and Duncan!
Thanks for reaching out!
Thank you bot for sharing solutions with the Community!
@Zayar: Yes, in this case, the solution is correct, taking into account that you are working with the same settings (only full group by switched on). Therefore, @Duncan: Thank you for providing an alternative solution! Hopefully, it will work for others, too!
Kind regards,
Martin

Hi, i noticed the query was referring to

employees.m.dept_no

which is not required in this example, but i guess it was stored in memory from the previous exercise (172,173). So i decided to restart MySQL server and that worked for me.

Hi Lazar!

Thanks for reaching out and sharing your solution with the Community!
Restarting the server may have set the correct settings into place as well. Alternatively, some manipulations you have made throughout the session to a table whose alias you have designed to be m, should not have been applied after the restart.
Hope this clarifies the situation one step further.

Kind regards,
Martin