sql_mode=only_full_group_by

Question

Im trying to help someone to implement new function on his code; but I have this error

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'br_interne.questionnaires_reponses.re_int' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT `ch_ref` AS id, `ch_date_prevue` AS date, `ch_prestataire_terrassement_branchement` AS prestataire, `re_int` FROM (`questionnaires_contacts`) JOIN `chantiers_osr` ON `ch_ref`=`qc_chantier_id` JOIN `communes` ON `ch_insee`=`co_insee` JOIN `departements` ON `co_departement`=`de_id` LEFT JOIN `questionnaires_reponses` ON `re_questionnaire_contact` = `qc_id` AND re_question = 7 WHERE `de_id` = '78' AND `ch_date_prevue` >= '0000-00-00' AND `qc_chantier_type` = 'osr' AND `qc_invitation_envoyee` = '1' GROUP BY `qc_chantier_id`

Unfortunately if change the SQL_mode the request doesn't work. ( really strange because this code works on the server ).

If you have any idea. Even if with the documentation of SQL I can find the solution to modify this request.

Thanks a lot in advance.

Gynecoid

You mean you get this error when the SQL_mode is set to only_full_group_by? Per the manual, MySQL rejects queries for which the select list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. This is not related to PHP, nor codeigniter. – Sloganeer

What version(s) of mysql? – Inset

This helped me solve a similar problem recently. dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Inset

With this sql_mode on, mysql won't randomly select one from joined results. A quick hack is to add the columns into the GROUP BY clause if it won't affect the results you're looking for. In your case GROUP BY qc_chantier_id, re_int – Pejoration

Answer 1

Remove ONLY_FULL_GROUP_BY from MySql console

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

OR

Fire below query in PHPMyAdmin

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

Landing

In case using drupal 8 check this out https://mcmap.net/q/28657/-disable-only_full_group_by – Tweeny

Answer 2

This error is seen because the columns in Group By are not the same as in select. GROUP BY will create summary columns, so what value to pick for the other columns in select is not clear.

Eg: for a table with columns UserId | ModuleId where userId and ModuleId have 1:N mapping.

Say you run select UserId, ModuleId from UserModule group by userId;

Now, which moduleId should be picked for a userId?

Hence, this issue. So for other columns, you have below choices:

1. Delete other columns in select,

2. Use aggregate functions like ANY_VALUE for other columns

This enforcement from MySQL is to improve semantically incorrect queries, rather than simply have queries which can get an output somehow.

If you still want to disable the mode, you can do as explained in other answer.

Acid