PostgreSQL 'Exception: SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "fid" is ambiguous

I might have to move this to the Views issue queue to see if they have any answer. We have a default view in the Media module that is on the {file_managed} table, and aggregates the file usage field as a total.

+1 got this when upgrading from media 1.x to 2.x, the query on the media browser view provided by the media module looks like this:

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
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

Having the same issue. Currently running on postgreSQL 9.1.5 (latest)

Current SQL executed by the view (that ships with default media 2.x):

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
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

PostgreSQL error (when run manually against the DB):

ERROR:  column reference "fid" is ambiguous
LINE 6: GROUP BY file_managed_timestamp, fid

I made a small change to the manual SQL (file_managed.fid AS file_managed_fid), and the query runs:

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, file_managed_fid 
ORDER BY file_managed_timestamp DESC;

 file_managed_timestamp | file_managed_fid | file_usage_count 
------------------------+------------------+------------------
             1348111377 |               93 |                1
             1348098624 |               92 |                1
             1348092878 |               91 |                1
             1348024454 |               90 |                1
             1348024440 |               89 |                1
             1348024424 |               88 |                1
             1348024407 |               87 |                1
             1348024394 |               86 |                1
             1348024356 |               85 |                1
             1348004505 |               83 |                1
             1348004481 |               82 |                1
             1348003758 |               81 |                1
             1347939486 |               80 |                1
             1347937160 |               79 |                1
             1347937148 |               78 |                1
             1347935720 |               77 |                1
             1347929253 |               76 |                1
             1347320477 |               74 |                2

I of course have no idea how to alter the view in order to produce said SQL.

Any views ninjas around to help debug?

A workaround for now is to remove the aggregation settings from the view.

I'm seeing the same thing using Postgres 9.21. The code in #3 works also fine for me.

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, file_managed_fid
ORDER BY file_managed_timestamp DESC;

bump.. same error, running dev branch media on PostgreSQL with latest dev code on most modules.

Having the same problem with (using Postgres 9.17 on Linux). The select in #3 works in my installation.

Is this the same issue? #1331056: Regression: Improper use of GROUP BY statement produces ambiguous column error

The last patch supplied in that issue seems to resolve this too (subject to more testing, but the error message goes away).

Thanks, Torsten, the patch you mentioned fixed it for me too.

Since #1331056: Regression: Improper use of GROUP BY statement produces ambiguous column error
fixes the underlying issue.