Query with GROUP BY and ORDER BY not working when multiple columns in SELECT are chosen

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

Solution

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.