Jump to content
ajoo

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

 

 

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.