MySQL query with GROUP BY behaving differently between MySQL versions?

Question

I have two MySQL tables - equipment and calibration, where equipment represents an inventory of equipment and calibration holds records for each equipment calibration. One equipment will have multiple calibrations.

In MySQL 5.5 the following query was fully working to identify equipment where the most recent calibration has expired:

SELECT * FROM equipment AS e
LEFT JOIN (
  SELECT calibration_id, equipment_id, calibration_company, certificate_no, date_certified, date_nextdue
  FROM (
    SELECT calibration_id, equipment_id, calibration_company, certificate_no, date_certified, date_nextdue
    FROM calibration
    WHERE deleted=0
    ORDER BY date_certified DESC
  ) AS a GROUP BY a.equipment_id
) AS c ON c.equipment_id=e.equipment_id
WHERE e.deleted=0 AND c.date_nextdue <= CURRENT_DATE()

However in MySQL 5.7 the same SQL query works but returns rows including the oldest calibration not the most recent.

I've been experimenting with different joins but all need the GROUP BY facility and that seems to be where this all goes wrong.

While the above is a fictional example I have a lot of queries that are very similar in structure and behaving the same way. My question in two parts is:

1. Why is this behaving differently in MySQL 5.7, and

2. What changes do I need to make to the SQL to get it to function as desired in MySQL 5.7?

Thank you for your help.

Answer 1

Your original query has non-aggregated columns in the select clause that do not belong to the group by clause. While MySQL might allow that, which value will be picked is actually undefined, meaning that it is not guaranteed to be consistent over consecutive executions.

Preventing developers from falling into this trap is one of the reason why MySQL enables ONLY_FULL_GROUP_BY by default starting version 5.7 (since your query is still running, it means that this sql mode was explicitly disabled in the configuration of your new database server).

If I understood your query correctly, you can use a correlated subquery instead:

select e.*
from equipments e
where (
    select max(c.date_certified) 
    from calibration c 
    where c.equipment_id = e.equipment_id and c.deleted = 0
) <= current_date