mysql incompatible sql mode

Question

I changed my server and there's the newest of mysql.

First my query:

SELECT userdata.ONLINE,pictures.IMAGELINK,userdata.BIRTHDATE,userdata.POSITION,InfiniteLove.USERNAME  FROM InfiniteLove
                                  LEFT JOIN pictures ON InfiniteLove.USERNAME = pictures.USERNAME
                                  LEFT JOIN userdata ON InfiniteLove.USERNAME = userdata.USERNAME
                                  WHERE NOT InfiniteLove.USERNAME = 'lalox3' AND InfiniteLove.GENDER = 'M' AND InfiniteLove.SEARCH = 'M' OR InfiniteLove.SEARCH = 'B'
                                  AND NOT InfiniteLove.GENDER = 'W' GROUP BY InfiniteLove.USERNAME

And the error:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'simplylabs.pictures.IMAGELINK' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I really have to say, I'm just so stupid if I have to work with more than one table.

I can't change the mysql settings so my query have to be changed.

What part of the error message do you not understand? – Gordon Linoff Feb 17 '18 at 17:23 — Gordon Linoff, Feb 17 '18 at 17:23
@GordonLinoff `SELECT list is not in GROUP BY clause` – J.Doe Feb 17 '18 at 17:26 — J.Doe, Feb 17 '18 at 17:26

score 1 · Accepted Answer · answered Feb 17 '18 at 17:26

You have a GROUP BY clause but no aggregation functions. This leads to the possibility that it is not needed at all:

SELECT u.ONLINE, p.IMAGELINK, u.BIRTHDATE, u.POSITION, il.USERNAME
FROM InfiniteLove il LEFT JOIN
     pictures p
     ON il.USERNAME = p.USERNAME LEFT JOIN
     userdata u
     ON il.USERNAME = u.USERNAME
WHERE il.USERNAME <> 'lalox3' AND il.GENDER = 'M' AND 
      il.SEARCH IN ('M', 'B') AND 
      il.GENDER <> 'W';

Note the use of <> rather than not . . . =. This is the more conventional syntax. I also combined the OR into an IN. This is not the same as your logic, but I suspect it is what you really intend.

If you want one row per user, then you need an aggregation function. This might be what you want:

SELECT u.ONLINE, GROUP_CONCAT(p.IMAGELINK) as imagelinks,
       u.BIRTHDATE, u.POSITION, il.USERNAME
FROM InfiniteLove il LEFT JOIN
     pictures p
     ON il.USERNAME = p.USERNAME LEFT JOIN
     userdata u
     ON il.USERNAME = u.USERNAME
WHERE il.USERNAME <> 'lalox3' AND il.GENDER = 'M' AND 
      il.SEARCH IN ('M', 'B') AND 
      il.GENDER <> 'W'
GROUP BY u.ONLINE, u.BIRTHDATE, u.POSITION, il.USERNAME;

This combines all the images into a single column.

Thank you, I think I really have to go to a sql curse, because I really really don't understand it. But thanks. I will learn it. — J.Doe, Feb 17 '18 at 17:29

score 1 · Answer 2 · answered Feb 17 '18 at 17:29

You are using group by without aggregation function this is deprecated in sql and not allowed in most of the database if you want avoid duplicated result don't use group by but distintc

  SELECT DISTINCT  userdata.ONLINE
        ,pictures.IMAGELINK
        ,userdata.BIRTHDATE
        ,userdata.POSITION
        ,InfiniteLove.USERNAME  
  FROM InfiniteLove
  LEFT JOIN pictures ON InfiniteLove.USERNAME = pictures.USERNAME
  LEFT JOIN userdata ON InfiniteLove.USERNAME = userdata.USERNAME
  WHERE NOT InfiniteLove.USERNAME = 'lalox3' 
  AND InfiniteLove.GENDER = 'M' 
  AND (InfiniteLove.SEARCH = 'M' OR InfiniteLove.SEARCH = 'B')
  AND NOT InfiniteLove.GENDER = 'W'

mysql incompatible sql mode

2 Answers2