Posted by simonruffle on January 11, 2012 at 12:08pm
8 followers
Jump to:
| Project: | Views |
| Version: | 7.x-3.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed (duplicate) |
Issue Summary
Just uninstalled Media 7.x-1.0-rc2 and installed 7.x-2.x-dev
In general it all works, and the images that were there before are still there.
When I go "Select media", the following error appears in the top part of the Upload/Web/Library box
The same error appears when I try to edit the Media Browser View.
'Exception: SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "fid" is ambiguous
LINE 8: GROUP BY file_managed_timestamp, fid) subquery
^'in views_plugin_query_default->execute() (line 1386 of D:\inetpub\wwwroot\drupal\sites\all\modules\views\plugins\views_plugin_query_default.inc).
Comments
#1
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.
#2
+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_countFROM
{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
#3
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_countFROM
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 ambiguousLINE 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?
#4
A workaround for now is to remove the aggregation settings from the view.
#5
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_countFROM
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;
#6
bump.. same error, running dev branch media on PostgreSQL with latest dev code on most modules.
#7
#8
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).
#9
Thanks, Torsten, the patch you mentioned fixed it for me too.
#10
Since #1331056: Regression: Improper use of GROUP BY statement produces ambiguous column error
fixes the underlying issue.