mysql error caused by the newer version

[ajoo]

Hi all !

The following query gave no error earlier in version 5.6

SELECT mr.xid,
fd.fname, fd.lname, fd.cell, fd.email,
a.country_id, a.state_id, a.city_id, a.pin_id,
sd.center_No,
count(sd.fid) total,
sum(case when sd.member_center_status='R' then 1 else 0 end) Acticount        
FROM master_recruitment as mr
JOIN user_details as fd ON fd.user_id = mr.xid
JOIN addresses as a ON a.fid = mr.xid
JOIN member_details as sd ON sd.fid = mr.xid
WHERE mr.mid = 6
GROUP by sd.fid, sd.center_No
LIMIT 1, 10;

gives the error : Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mytestdb.a.country_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by; in version 5.7 of mysql.

I have googled but could not really understand the reason the problem is occuring.

Please help

Thanks.

 

 

[Barand]

Suppose you had the following

+----------+---------------+
| user_id  |   username    |
+----------+---------------+
|    1     |  peter123     |
|    2     |  paul987      |
|    3     |  mary456      |            
+----------+---------------+  
     |
     |
     +-----------------------------+
                                   |
                                   |
           +------------------+----------+--------------+
           |  transact_id     | user_id  |  amount      |
           +------------------+----------+--------------+
           |      123         |    1     |   10.00      |
           |      124         |    2     |    5.00      |
           |      125         |    2     |    5.00      |
           |      126         |    1     |   15.00      |                             
           |      127         |    3     |   12.00      |                             
           |      128         |    3     |   22.00      |                             
           +------------------+----------+--------------+                             
                                                                                

with this query

SELECT u.user_id
     , u.username                        -- functionally dependant on user_id (OK)
     , t.transact_id                     -- not dependant                     (error)
     , SUM(t.amount) as total
FROM user u 
       INNER JOIN
     transaction t USING (user_id)
GROUP BY u.user_id;

The username is functionally dependant on the GROUP BY column user_id. That means for each record in the id group the username will be the same. The transact_id hovever is not. The value in the result could come from any arbitrary record belonging to the group.

For this reason, standard SQL does not allow you to select columns that are not in the group by clause or not aggregated. Mysql has been lenient on this rule in the past. With your sql_mode setting you must either

• omit fields from the selection that are not in the group by clause, or
• put all selected (non aggregated) fields in the group by clause

See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Edited September 5, 2018 by Barand
Added link to manual

[ajoo]

Thank You Guru Barand for this wonderful answer. I will need to work on it to completely understand it though. 

Thank you !