Jump to content

mysql error caused by the newer version


ajoo

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.

 

 

Link to comment
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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.