MySQL - sql mode ONLY_FULL_GROUP_BY

Question

I have this query:

select distinct user_id,   
if((user_A_id = 2 or user_B_id = 2) and connected = 1, 1, 0) mutualConnections,  
if(user_A_id = 2 and connected = 0, 1, 0) requestsUserSent,  
if(user_B_id = 2 and connected = 0, 1, 0) requestsUserReceived,  
if((user_A_id != 2 and user_B_id != 2) or (user_A_id is null and user_B_id is null), 1, 0) notConnected

from Connections 
right join user_configuration on(user_id = user_A_id or user_id = user_B_id)
group by user_id having user_id != 2;

When I run it in my local DB, it works just fine. I uploaded my DB to AWS EC2 machine, and when i run this query I get the following error:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'users_db.Connections.user_A_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I added this command in My SQL server: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); and the output of the query is not right. I read in other post that when I disable ONLY_FULL_GROUP_BY, the server is free to choose any value from each group from group by clause.

How can I fix the query or the error?

Thorsten Kettner · Answer

You group by user_id in order to get one result row per user. So the result can show the user ID and aggregation results for that user ID, like minimum, maximum and average values, sums and counts. Your if((user_A_id = 2 or user_B_id = 2) and connected = 1, 1, 0), however, is no aggregation result. Which row is user_A_id referring to for instance, when there are many rows for a user_id?

It seems you want a row for every user showing their relation to user #2. In MySQL true = 1 and false = 0. So you can use MIN, MAX and SUM on boolean expressions to get aggregated results.

The following query may come close to what you are looking for. There was a little guesswork to do :-)

select 
  uc.user_id,   
  max(c.user_a_id = 2 and c.connected = 1) *
   max(c.user_b_id = 2 and c.connected = 1) as mutually_connected,  
  sum(c.user_a_id = 2 and c.connected = 0) as requests_user_sent,  
  sum(c.user_b_id = 2 and c.connected = 0) as requests_user_received,  
  max(c.user_a_id is null) as not_connected
from user_configuration uc
left join connections c on (c.user_a_id = uc.user_id and c.user_b_id = 2)
                        or (c.user_b_id = uc.user_id and c.user_a_id = 2)
group by uc.user_id
having user_id <> 2
order by uc.user_id;

Some more points:

As the user ID is in your result and there is one row per user, every row is unique by nature. There is no reason hence to use DISTINCT on the result rows; they are already distinct.
Right joins are very often close to unreadable. Don't use them. Always use left outer joins instead.
When working with more than one table, qualify each column with its table name. You can use alias names (mnemonic ones please, like uc for user_configuration).
As mentioned true = 1 and false = 0 in MySQL. So you can decide between and c.connected = 1 and a mere and c.connected. They mean the same thing. Same for and c.connected = 0 and and not c.connected.
You should always work in ONLY_FULL_GROUP_BY mode in MySQL, so the DBMS prevents you from writing invalid queries. It is a very bad idea to have that switched off which seems to be the case with your local database. Make sure to SET sql_mode = 'ONLY_FULL_GROUP_BY';.

MySQL - sql mode ONLY_FULL_GROUP_BY

1 Answers