Query with GROUP BY and ORDER BY not working when

问题:

I'm updating an old website and one of the queries isn't working anymore:

SELECT * FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

I noticed if I dropped the GROUP BY it works, but the result set doesn't match the original:

SELECT * FROM tbl WHERE col1 IS NULL ORDER BY col2

So I tried reading up on GROUP BY in the docs to see what might be the issue, and it seemed to suggest not using * to select all the fields, but explicitly using the column name so I tried it with just the column that was being ordered and grouped:

SELECT col2 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

Which works but after looking through the code the query requires 2 columns in the query so whoever added * was overdoing it, but if I add that column produces an error, similarly adding a third column produces the same error:

SELECT col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2
SELECT col1, col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

Can anyone tell me why this last query doesn't work? I can't decipher why from the docs, but this is the minimum query required to get the result set I need.

Running the query in Adminer I get this error

Error in query (1055): Expression #2 of SELECT list is not in GROUP BY 
clause and contains nonaggregated column 'name.table.column' 
which is not functionally dependent on columns in GROUP BY clause; this is 
incompatible with sql_mode=only_full_group_by

回答1:

First of all, when query() returns false, you should find out what the error was. You seem to be using PDO, so I will direct you to this page: http://php.net/manual/en/pdo.error-handling.php

TL;DR - you should enable PDO exceptions, or else you need to write code to check the result of every call to query(), prepare(), and execute() to see if an error occurred. And if so, use errorInfo() to find out the actual error. Doing anything else is flying blind!

Error in query (1055): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'webvictoria.cats_oct.matchLink' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is a common issue. See dozens of questions tagged mysql-error-1055.

I guess you just upgraded to MySQL 5.7. MySQL 5.7 enabled strict mode by default, so I guess you just upgraded. Prior to MySQL 5.6, strict mode was optional and not enabled by default.

See: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

You can't write ambiguous queries. If you GROUP BY col2, which value in the group of rows of each group should be used for col1 and col3? It's ambiguous.

Without strict mode, MySQL chooses an arbitrary row from the group. With strict mode, it reverts to standard SQL behavior, and disallows the ambiguous query. This is how most other brands of SQL database behave, by the way.

To fix it, you must follow this rule: Every column in your select list must be one of:

• A column in your GROUP BY clause
• A column functionally dependent on the columns in your GROUP BY clause (so there can only be one value)
• Used in an aggregate function like MIN(), MAX(), COUNT(), SUM(), AVG(), or GROUP_CONCAT()

Some people choose to disable strict mode in MySQL 5.7 for the sake of "getting the code working again." But it isn't working—it's just giving ambiguous results like it did before MySQL 5.7.

It's better to fix the logic of your queries.

回答2:

You need to be careful when you use GROUP BY. Once you understand what GROUP BY does, you will know the issue yourself. It does an aggregation on your data or in other words, it reduces your data by doing some operation on the raw entries and creating new reduced number of entries on which some aggregation function has been applied(SUM, COUNT, AVG, etc.)

The fields you provide in the GROUP BY clause represents the level of aggregation/roll-up you are going for.

SELECT col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col1 ORDER BY col1

Here you are trying to do the aggregation at col1 level, meaning that for every distinct value present in column col1, there will be some operation done on some other columns you provide in SELECT clause(here col2,col3) so that in the output you have non-repeating values in col1 and some rolled-up values of col2 and col3 against each distinct col1 value based on what function you apply(SUM, COUNT, AVG, etc.).

How do you apply this function? That is what is missing in your above query. To solve it, you need to apply some aggregation function on the fields that are present in the SELECT clause but not in GROUP BY clause. Taking an example of SUM, try this:

SELECT SUM(col2), SUM(col3) FROM tbl WHERE col1 IS NULL GROUP BY col1 ORDER BY col1

OR for a better idea, removing WHERE filter and checking the output by running:

SELECT col1, SUM(col2), SUM(col3) FROM tbl GROUP BY col1 ORDER BY col1

Additionally, the reason why your other query

SELECT col2 FROM tbl WHERE col1 IS NULL GROUP BY col2 ORDER BY col2

worked is because you need not apply aggregation to the field(here col2) which is present in the GROUP BY clause.

回答3:

This query:

SELECT *
FROM tbl
WHERE col1 IS NULL
GROUP BY col1
ORDER BY col1;

never really worked. It may have seemed to work, but you were just lucky. You have unaggregated columns in the SELECT. These come from an arbitrary row.

You can do something like this to get values from other columns:

SELECT col1, min(col2), min(col3)
FROM tbl t
WHERE col1 IS NULL AND
GROUP BY col1
ORDER BY col1;

回答4:

The reason it didn't work is because you need to use one of the selection criteria in the GROUP BY and the ORDER BY. So if you wanted to group by col1, you would need to do this:

SELECT col1, col2, col3     
   FROM tbl     
   WHERE 
      col1 IS NULL     
   GROUP BY col1     
   ORDER BY col1 
;

Without selecting that field, you are basically saying "Hey go get me every phone number in California" Then after you get that you say "Now order them by first name and group them by last name" and DBMS says "but... I don't have any of that"

回答5:

try this

SELECT col2, col3 FROM tbl WHERE col1 IS NULL GROUP BY col2, col3 ORDER BY col2, col3