Query with MySQL incompatible with sql_mode=only_full_group_by

Question

I have error on my Query

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

Query :

SELECT
    Employee.Id as Id,
    Employee.FingerId as FingerId,
    Employee.Name as Name,
    Departement.Departement as Departement,
    EmployeeShift.Shift as Shift,
    ScanIn
FROM
    HrEmployee as Employee
    LEFT JOIN HrEmployeeShift as EmployeeShift 
        ON Employee.ShiftId = EmployeeShift.Id
    LEFT JOIN CmDept as Departement 
        ON Employee.DeptId = Departement.Id
    LEFT JOIN (
        SELECT
            TableEmployee.FingerId,
            ScanIn
        FROM
            HrEmployee as TableEmployee,
            HrAttLogsFormatted
        WHERE
            TableEmployee.FingerId = HrAttLogsFormatted.FingerId
            AND DateIn = '2019-11-04'
        GROUP BY HrAttLogsFormatted.FingerId
    ) AS HrJoinLogs 
        ON Employee.FingerId = HrJoinLogs.FingerId
WHERE
    Employee.Status = 1
    AND Employee.Flag = 1
    AND Employee.ShiftId = 1
    AND ScanIn is NULL
GROUP BY
    Employee.Name
ORDER BY
    Employee.Name ASC

Does anyone have a solution?

Why are you using group by at all? There is no aggregate function in your query. — GMB
There are no aggregations in this query so a group by is inappropriate - what are you trying to do. — P.Salmon
Side note: properly formatted queries are much easier to understand. I formatted your query, please consider doing it yourself next time. — GMB

O. Jones O. Jones · Accepted Answer · 2019-11-04T12:01:11

First, read about MySQL's notorious non-standard handling of GROUP BY. Here.

Second, it looks like you're using GROUP BY to eliminate duplicates from your result set. That procedure is questionable. In my opinion it's unacceptable when your query deals with other peoples' money. You may want to use SELECT DISTINCT instead. But, your best bet is to figure out the intended logic of this query and rewrite it to deliver that logic more explicitly.

If you still think you need GROUP BY, look at a result set from when your query worked, before you upgraded to a more modern version of MySQL. You will see that all rows of your result set's ScanIn column are NULL, because your query says AND ScanIn IS NULL.

So, grouping by ScanIn won't hurt anything. Add , ScanIn to both GROUP BY clauses.

Query with MySQL incompatible with sql_mode=only_full_group_by

1 Answers