Error loading file entity admin page due to improper GROUP BY handling

Here's the SQL Server query, made more readable:

 SELECT TOP(50) fm.[fid] AS [fid], fm.[uid] AS [uid], SUM(fu.count) AS total_count, fm.timestamp AS _field_0, fm.fid AS _field_1
FROM {file_managed} fm
LEFT OUTER JOIN {file_usage} fu ON fm.fid = fu.fid
GROUP BY fm.fid
ORDER BY fm.timestamp DESC

The original MySQL query is:

SELECT fm.fid AS fid, fm.uid AS uid, SUM(fu.count) AS total_count
FROM file_managed fm
LEFT OUTER JOIN file_usage fu ON fm.fid = fu.fid
GROUP BY fm.fid
ORDER BY fm.timestamp DESC
LIMIT 50 OFFSET 0

The error stems from the first query in file_entity_admin_files() in the file_entity.admin.inc file from the File Entity module (v7.x-2.x-dev):

  $query = db_select('file_managed', 'fm')
    ->extend('PagerDefault')
    ->extend('TableSort');
  $query->leftJoin('file_usage', 'fu', 'fm.fid = fu.fid');
  $query->groupBy('fm.fid');
  $query->addExpression('SUM(fu.count)', 'total_count');
  file_entity_build_filter_query($query);

  $result = $query
    ->fields('fm', array('fid', 'uid'))
    ->limit(50)
    ->orderByHeader($header)
    ->addTag('file_access')
    ->execute()
    ->fetchAllAssoc('fid');

The reason for the error is that SQL Server is more strict about how GROUP BY queries are executed - each column that is static in the query must be listed as a new GROUP BY statement.

In this case the solution is to add a GROUP BY statement for each column in the {file_managed} table that is listed in the SELECT statement:

  $query->groupBy('fm.fid');
  $query->groupBy('fm.uid');
  $query->groupBy('fm.timestamp');

.. instead of just adding fm.fid. However, this should be handled dynamically by SelectQuery_sqlsrv->__toString() in select.inc.

It might be worthwhile to fix the problem in preExecute().

Also, I noticed that the logic for adding the group-by fields to the SELECT list doesn't check if the fields are already included, thus fields can be loaded twice - are there any performance penalties for doing so? It shouldn't be too hard to check that the field isn't already in the list before adding it.