SQLSTATE[HY000]: General error: 1 ambiguous column name: fid

I have same problem in clean Drupal 8 beta 1 installation (SQLite):

Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: "Exception in Files[files]: SQLSTATE[HY000]: General error: 1 ambiguous column name: fid: SELECT file_managed.fid AS fid, file_managed.filename AS file_managed_filename, file_managed.uri AS file_managed_uri, file_managed.filemime AS file_managed_filemime, file_managed.filesize AS file_managed_filesize, file_managed.status AS file_managed_status, file_managed.created AS file_managed_created, file_managed.changed AS file_managed_changed, SUM(file_usage_file_managed.count) AS file_usage_file_managed_count, MIN(file_managed.fid) AS fid_1\nFROM \n{file_managed} file_managed\nLEFT JOIN {file_usage} file_usage_file_managed ON file_managed.fid = file_usage_file_managed.fid\nGROUP BY fid, file_managed_filename, file_managed_uri, file_managed_filemime, file_managed_filesize, file_managed_status, file_managed_created, file_managed_changed\nORDER BY file_managed_changed DESC\nLIMIT 51 OFFSET 0; Array\n(\n)\n" at /var/www/d8.localhost/core/modules/views/src/Plugin/views/query/Sql.php line 1446, referer: http://d8.localhost/admin/content

In path admin/content/files

I'm having the same problem with with Drupal 7.38 with an SQLite database, Views 7.x-3.11 and Media 7.x-2.0-alpha4+37-dev.

Tried latest Views dev and problem persists.

Changing "Use Aggregation" to NO in the Media Browser view solves the problem, but it might bring other issues.

I have same problem with media browser. The Views query is:

/* media_browser */ SELECT file_managed.timestamp AS file_managed_timestamp, file_managed.fid AS fid, SUM(file_usage.count) AS file_usage_count
FROM 
{file_managed} file_managed
INNER JOIN {users} users_file_managed ON file_managed.uid = users_file_managed.uid
LEFT JOIN {file_usage} file_usage ON file_managed.fid = file_usage.fid
WHERE (( (file_managed.status IN  ('1')) AND( (users_file_managed.uid = '1') )))
GROUP BY file_managed_timestamp, fid
ORDER BY file_managed_timestamp DESC
LIMIT 25 OFFSET 0

I think, the problem is in the "GROUP BY file_managed_timestamp, fid" line, the fid should be file_managed.fid there.
The SQL is ambiguous because of file_managed and file_usage table both contain fid column.

I hope, this little debug helps. (I am trying to fix it.)

I just debugged a little bit on sqlite, seems the file_managed table fid field has an fid alias. It's the same as the file_usage table fid.

The views atomatically generates the group by statement, builds it from field aliases. So the ambiguous fid comes there by this. No way to alter it by views_query_alter().

I tried to force to alter the file_managed fid field alias using an another method , but then the fid column missing from the query - all theming became broken.

So, the possible solution is - disable the aggregation of this view. At least it works, buts maybe there will be duplicated content in the media browser.

I tried the media_browser_plus, with same result, fid in the group by:

SELECT file_managed.fid AS fid, file_managed.timestamp AS file_managed_timestamp, SUM(file_usage.count) AS file_usage_count
FROM 
{file_managed} file_managed
LEFT JOIN {file_usage} file_usage ON file_managed.fid = file_usage.fid
LEFT JOIN {field_data_field_folder} field_data_field_folder ON file_managed.fid = field_data_field_folder.entity_id AND (field_data_field_folder.entity_type = 'file' AND field_data_field_folder.deleted = '0')
WHERE (( (file_managed.status IN  ('1')) AND (field_data_field_folder.field_folder_tid = '4') ))
GROUP BY fid, file_managed_timestamp
ORDER BY file_managed_timestamp DESC
LIMIT 10 OFFSET 0

I have the same problem with the 'library' and 'my files' tabs of the media browser not show any files and just shows 'no files available'.

When i run the example in the view Media Browser (media_default) I get the error:
'SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "fid" is ambiguous LINE 8: GROUP BY file_managed_timestamp, fid) subquery ^'

When I run the query that is shown in the view in postgres it fails with the same error. On Mysql, the query runs just fine.

SELECT file_managed.timestamp AS file_managed_timestamp, file_managed.fid AS file_managed_fid, SUM(file_usage.count) AS file_usage_count
FROM 
file_managed file_managed
LEFT JOIN file_usage file_usage ON file_managed.fid = file_usage.fid
WHERE (( (file_managed.status IN  ('1')) ))
GROUP BY file_managed_timestamp, fid
ORDER BY file_managed_timestamp DESC
LIMIT 25 OFFSET 0

The problem is the fid in the GROUP BY statement. This field is present in both file_managed and file_usaged tables which confuses postgres.

When I add a function hook_views_query_alter(&$view, &$query) and debug $query I see that the 'fid' field is added with an alias 'fid'.

[fid] => Array
  (
    [field] => fid
    [table] => file_managed
    [alias] => fid
  )

I changed the alias with this function:

function MODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'media_default') {
    $query->fields['fid']['alias'] = 'file_managed_fid';
  }
}

But this causes problems in template_preprocess_media_views_view_media_browser() in media.views.inc because that function uses the 'fid' field which I just renamed.

So I used a hook_views_pre_render() to put the 'fid' properties back into the results.

function MODULE_views_pre_render(&$view) {
  // Only in the media view.
  if ($view->name == 'media_default') {
    foreach($view->result as $key => $result) {
      // Add the original 'fid' property back into the results.
      $view->result[$key]->fid = $result->file_managed_fid;
    }
  }
}

And now the files are shown again in the media browser.