'sql_mode=only_full_group_by

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.

phpsqlcodeigniter


Solution 1:[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',''));

Solution 2:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 aviboy2006
Solution 2 agyeya

Related Questions