Jump to content

mysql error caused by the newer version

Recommended Posts

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,
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




Share this post

Link to post
Share on other sites

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 
     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 by Barand
Added link to manual
  • Great Answer 1

Share this post

Link to post
Share on other sites

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

Thank you !


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.