Aggregated query without GROUP BY

Question

This query seems to work perfectly on my older machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25 it throws the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pixel_perfect.users.id'; this is incompatible with sql_mode=only_full_group_by' in C:\wamp64\www

Here is what my query looks like:

$sql="SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email LIMIT 1";

$stmt=$db->prepare($sql);
$stmt->bindValue(':email', $email);
$stmt->execute();

Why am I getting this error now and what can I do to resolve it?

Strasser

count() is an aggregation function in mysql. It can only be used when attempting to aggregate the values of a column across all rows. It cannot be used along with getting other values on a per row basis as you are attempting. You can simple count the results returned if you need a count. – Procyon

A user only has 1 id right so couldn't you just group by the id? – Amulet

@Amulet True, yet it'd be interesting to find out the reason why their code failed. I for one am a bit baffled. – Gutshall

Answer 1

A change was made in version 5.7.5 where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.

You have two options:

1. You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here
2. You can fix your query

Option 2 would look something like:

SELECT id, password, COUNT(id) AS count 
FROM users 
WHERE email = :email 
GROUP BY id, password 
LIMIT 1

It's also important to note that excluding a non-aggregated column from the GROUP BY clause is permitted in 5.7.5 and newer version in the event that the unaggregated column has been limited to a single value (such as a filter in the WHERE clause). See the link above for examples of this allowed exception.

Redpencil

So in other words, they're forced to do a GROUP BY. – Gutshall

@Fred-ii- They don't HAVE to do a GROUP BY. They could also toss an aggregate function around the remaining fields like SELECT max(id), max(password), count(id) would probably be closer to the arbitrary nature of the same query without the GROUP BY but it feels very wrong. – Redpencil

So... is this a thing simply because it's a thing? I mean what does this have to do with the Relational Calculus* RDBMS's are based on? I fail to see how this assists in maintaining data integrity. I assume I'm missing something but maybe not? *(en.wikipedia.org/wiki/Relational_calculus) – Cyperaceous

@Cyperaceous It's everything to do with data integrity. Imagine a simple table as id | parent_id and two rows: 1 | 3, 2 | 3. And you issue: SELECT I'd, parent_id FROM table GROUP BY parent_id;. Old mysql isnt going to error that like every other RDBMS, it will spit out a single record. But... Which id will it choose? 1 or 2? Totally up to chance. You wrap this in an app and your user gets back 1 and runs again and gets 2 and then runs against and gets 1... Nonsense. – Redpencil

Answer 2

Its a little late but I just ran into this error.

This command might be useful for anyone else who runs into the same error

     mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

More information about this can be found at Table Plus and other links quoted above by JNevill.

Hope it helps someone else.

Filipino

Yeah yeah, sweeping the dirt under the rug is surely useful when your task is to clean the room. – Eroticism

Life saver answer – Incept

It is wise to use GROUP BY in this kind of situation. – Lucas

I have a huge website built in CakePHP 2 and this helped me to sweep the dirt under the rug! ;) – Scutiform

@YourCommonSense It is when you are expecting guests at any moment, if you know what I mean. – Exceed

Answer 3

Easiest answer, in config/database.php make sure to set strict => true to strict => false for the mysql settings.

This will allow for less than strict queries at a cost of security for normal well-formed sql calls (still not 100% secure), but will allow for the use of other sql calls that could be in-secure if written improperly.

Married

So we enforce a group by for ... security??! Sorry not challenging your statement but that it seems to be generally accepted. I guess so you can't inject a subselect or other SQL command inside a concat() or similar? ... hmmm I think that's a very thin argument... As you mentioned, the real security is in what actually runs the queries... I mean sure there should be an effort made but this one has always seemed silly to me. – Cyperaceous

Answer 4

Change ur SQL mode to default.. it will execute without error The SQL mode defines the syntax of the query. If you using ONLY_FULLY_GROUPBY you have to write a query with group by for all aggregator functions

Extracanonical

Answer 5

I came across this error recently,

The below steps helped me and saved me a lot of time.

SET GLOBAL sql_mode = (SELECT 
REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = (SELECT 
REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT @@sql_mode;

Or

The below also works:

mysql > SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');

More information about this can be found at (https://database.guide/fix-error-1140-in-aggregated-query-without-group-by-expression-1-of-select-list-contains-nonaggregated-column-in-mysql/)

Thanks

Chandler