3

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.

Sylv Ain
  • 121
  • 1
  • 2
  • 8
  • 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. – user3783243 Dec 30 '19 at 21:21
  • What version(s) of mysql? – ryantxr Dec 30 '19 at 21:48
  • This helped me solve a similar problem recently. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – ryantxr Dec 30 '19 at 21:51
  • 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` – Kubwimana Adrien Dec 30 '19 at 22:23

2 Answers2

8

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',''));
Avinash Dalvi
  • 8,551
  • 7
  • 27
  • 53
Nikhil Vagadiya
  • 169
  • 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.

uzumas
  • 632
  • 1
  • 8
  • 23