MYSQL, GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Question

The following CodeIgniter query gives an error telling;

Expression #22 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hw3.1.hw_homework.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT *, `studentid`, COUNT(studentid),
`be_user_profiles`.`first_name`, `be_user_profiles`.`last_name`
FROM `be_user_profiles` 
JOIN `be_users` ON `be_users`.`id`=`be_user_profiles`.`user_id` 
JOIN `hw_homework` ON `be_user_profiles`.`user_id`=`hw_homework`.`studentid` 
WHERE `be_user_profiles`.`advisor` = '20' 
AND `hw_homework`.`date` < '2018-06-15 00:00:00' 
AND `hw_homework`.`date` > '2017-08-24 00:00:00'
AND `active` = 1 
GROUP BY `be_user_profiles`.`user_id` 
ORDER BY COUNT(studentid) DESC

Filename: modules/organization/models/Mhomework.php

Line Number: 226

$this->db->select('*,studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');
$this->db->from('be_user_profiles');
$this->db->join('be_users','be_users.id=be_user_profiles.user_id');
$this->db->join('hw_homework','be_user_profiles.user_id=hw_homework.studentid');
$this->db->where('be_user_profiles.advisor',$id);
$this->db->where('hw_homework.date <',$to);
$this->db->where('hw_homework.date >',$from);
$this->db->where('active',1);
$this->db->group_by('be_user_profiles.user_id');
$this->db->order_by('COUNT(studentid)','DESC');
$query = $this->db->get();

I removed studentid or added group_by studentid etc but none of them worked.

I know that I can set global SQL mode but I think it is not a solution for me.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

I want to fix the code not the way around.

Answer 1

In standard SQL it's very difficult to use SELECT * in a query with GROUP BY. Why? Standard SQL requires SELECTEed columns to also appear in the GROUP BY clause, with a few exceptions for columns with values that are functionally dependent on ones mentioned in the GROUP BY.

To write an aggregate query it's easiest to enumerate the columns you want in your SELECT and again in your GROUP BY. MySQL's notorious nonstandard extension to GROUP BY lets you ask for columns that aren't mentioned in the GROUP BY clause, and proceeds to return some unpredictable value for those columns.

To fix this "right" as opposed to hack around it, you need to get rid of the *. Change

 $this->db->select('*,studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');

to

$this->db->select('studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');

and, for best results, change

$this->db->group_by('be_user_profiles.user_id');

to

$this->db->group_by('studentid,be_user_profiles.user_id');