I am currently getting the error message below geneated when I enter teh 'My Workbench' section of the site ... in order to make sure that I am using the latest, I've upgraded today to Views 3.x-DEV + Workbench 1.1 ... still happens:
Debug:
'Exception: SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "vid" is ambiguous
LINE 7: ...de_revision_name, users_node_revision_uid, node_changed, vid
^'
in views_plugin_query_default->execute() (line 1386 of /usr/local/www/d_admin/dev.bestseniorsinfo.com/www/sites/all/modules/views/plugins/views_plugin_query_default.inc).
the query, according ot th epostgresql logs is:
SELECT node.title AS node_title, node.nid AS node_nid_1, node.type AS node_type, node.status AS node_status,
users_node_revision.name AS users_node_revision_name, users_node_revision.uid AS users_node_revision_uid,
node.changed AS node_changed, node_revision.vid AS vid, COUNT(node.nid) AS node_nid
FROM
node_revision node_revision
LEFT JOIN users users_node_revision ON node_revision.uid = users_node_revision.uid
LEFT JOIN node node ON node_revision.nid = node.nid
WHERE ((( (users_node_revision.uid = '1') )))
GROUP BY node_title, node_nid_1, node_type, node_status, users_node_revision_name, users_node_revision_uid, node_changed, vid
ORDER BY node_changed DESC
LIMIT 5 OFFSET 0
but, I can't figure out *where* or how that code is being generated ... since it only happens in My Workbench, I'm assuming it is related to this module, and not the views module ... the fix is really simple ... the 'vid' in GROUP BY has to be changed to 'node_revision.vid' ... but, where?
Help ... ?
Comments
Comment #1
stevectorAre any of your Workbench Views overridden?
Comment #2
hubScrappy CreditAttribution: hubScrappy commentedI've never modified a workbench related view ... so I'd have to say no ... only views I've ever played with is calendar related ...
Comment #3
hubScrappy CreditAttribution: hubScrappy commentedk, I just did an export of my workbench view and comparedit with what was in the package, and they are identical:
1,7d0
< <?php
<
< /**
< * @file
< * Default View for content edited by the user.
< */
<
299,326d291
< $translatables['workbench_edited'] = array(
< t('Defaults'),
< t('My Edits'),
< t('view all'),
< t('Apply'),
< t('Reset'),
< t('Sort by'),
< t('Asc'),
< t('Desc'),
< t('Items per page'),
< t('- All -'),
< t('Offset'),
< t('Edited something recently and it\'s not in this list? If a content type isn\'t revisioned and you didn\'t create it, it will not show up in this list when you edit it. You can find it in the \'Content I Can Edit\' tab.'),
< t('You haven\'t created or edited any content.'),
< t('revision user'),
< t('GROUPING FIELD TO FORCE DISTINCT'),
< t('Title'),
< t('Type'),
< t('Published'),
< t('Revised by'),
< t('Last updated'),
< t('Actions'),
< t('edit'),
< t('Page'),
< t('more'),
< t('Block'),
< t('Five of the most recently updated pieces of content.'),
< );
And I've re-downloaded the package from the site and did a diff, and they are identical also:
www01# diff workbench_edited.view.inc /tmp/workbench/views/workbench_edited.view.inc
www01#
Yet, if I go into Structure -> Views -> workbench_edited, the error is generated there as well ... something out of sync with the views-dev version, maybe?
Comment #4
thekevinday CreditAttribution: thekevinday commentedThis exact problem happened to me when I upgraded from views 7.x-3.0-rc1 to 7.x-3.0-rc3.
No other changes were applied.
I am also using postgresql.
I manually tested the query directly to postgresql. It seems that removing 'vid' from GROUP BY causes the query to work.
I do not understand why this is the case.
This could be a bug introduced by the views module, I will review the changelog here: http://drupal.org/node/1343786.
With any luck, something will stand out as related.
Comment #5
thekevinday CreditAttribution: thekevinday commentedThanks to
git reset --hard
, I was able to quickly isolate the problem to the exact views commit:- http://drupalcode.org/project/views.git/commit/3b779f6cb164e9104f0d3ba4f...
Unfortunately, that is a somewhat large commit and git --revert 3b779f6cb164e9104f0d3ba4f7e9ccb83ca7478d does not apply cleanly.
I would love to temporarily revert this commit on my end until a proper solution is made (so much for wishful thinking..).
Comment #6
dawehnerYou know you could solve your issue such easy by using mysql. Pgsql is like theory, it's nice in theory.
Comment #7
thekevinday CreditAttribution: thekevinday commentedactually, its in production and running fine with the exception of this commit. This is no theory.
If anything, the differences between mysql and postgresql should be moot if they both follow the sql standard and the sql standard is exclusively used in drupal. Then there would be no mysql this nor postgresql that.. (any issues then would be grounds for a bug in either database).
Comment #8
merlinofchaos CreditAttribution: merlinofchaos commentedYou realize this is actually impossible, right? Ah well.
Anyway I've seen this problem before -- pgsql chokes when an alias is the same as a field name and it can't figure out the alias. IMO that actually *is* a bug in the database.
See this:
So in fact, 'vid' by itself is not ambiguous, but pgsql says it is.
The surprising thing is...I thought we got rid of the code that special cases the alias on the base field in D7. Did that somehow make a return or did we not actually get rid of it?
Comment #9
thekevinday CreditAttribution: thekevinday commentedI would have thought that was exactly what the d7 database api did a good job off fixing/handling so that programmers could follow the SQL standard and leave it to the database api to apply whatever workarounds for any weirdness done by either database.
Somehow, the mentioned commit reintroduces the problem.
After some playing around, I isolated the problem to this block of code in views/handlers/views_handler_field_entity.inc (around line #63):
If I just comment this alias process out, the views start working properly.
The third parameter appears to be the problem.
Setting it to some form of empty() produces this problem.
If I were to set it to "dudd", then the query would work.
After some grepping, I think the following is the add_field() function in question:
views/plugins/views_plugin_query_default.inc:714
This block of code to seems to be the cause of the problem:
Commenting this out gives me a functional view.
So here is what I think is happening:
1) both tables (node and node_revision) have 'vid' as a column name.
2) the previous block of code sets the alias to 'vid', which conflicts with the 'vid' of both tables.
3) Because the vid column already exists and is already defined, it is ambiguous.
By removing the "special case" we prevent the potential ambiguity.
Edit:
But no matter how I look at the query, I cannot understand why postgres is seeing it as ambiguous.
Comment #10
thekevinday CreditAttribution: thekevinday commentedI have finally made sense of this error and why it is happening.
Postgresqls behavior is properly following the sql standard and there is a valid SQL error here.
The problem was not in the SELECT part of the statement, but instead in the GROUP BY part of the statement.
GROUP BY statement uses the original columns from any given TABLE and not the columns from the RESULT SET.
We should be defining GROUP BY columns using the TABLE.FIELD instead of just ALIAS to prevent this conflict from happening.
The attached patch is my first attempt at ensuring that the TABLE.FIELD is used instead of ALIAS in the group by statements.
Example Cases
This query fails:
This query succeeds:
This query succeeds:
References:
- http://archives.postgresql.org/pgsql-sql/2001-05/msg00315.php
- http://archives.postgresql.org/pgsql-sql/2001-05/msg00316.php
- http://w3schools.com/sql/sql_groupby.asp
- http://w3schools.com/sql/sql_orderby.asp
- http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-GROUPBY
- http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY
- http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html
Comment #11
thekevinday CreditAttribution: thekevinday commentedIts seems that in certain cases, some of the fields in the SELECT statement may not belong to any column.
These SELECT variables (I do not know the proper terminology for this name) cannot appear in the GROUP BY as they do not already exist.
I added a preg_match test to ensure that the SELECT variables are not added to the GROUP BY.
Once again, please review for any mistakes I may have made.
Comment #12
dawehnerIf you have expressions can't you add them to groupby as well? At least for ordering this works pretty well.
Comment #13
thekevinday CreditAttribution: thekevinday commentedFirst off, my first example case (the one that is supposed to fail) had a typo and should be:
The problem is that the two tables node_revision and node look like:
node
node_revision
They both have a 'vid' column.
The GROUP BY statement sees that there are 2 vid columns because it gets processed BEFORE the result set is generated.
This the ambiguity.
The ORDER BY statement is processed AFTER the result set is generated and as such there is only 1 vid column.
I hope this makes more sense than my previous attempts at explaining this.
(Side Note: Is it me or is there something wrong with the node table defining vid as bigint and the node_revision defining vid as integer and vice-versa for the nids?)
Comment #14
tim.plunkettTriggering the testbot.
Comment #15
thekevinday CreditAttribution: thekevinday commentedThe test bot results seem to be stuck in the step: "Test request sent" for almost a month now.
Comment #16
tim.plunkettThat would be why.
Comment #18
tim.plunkett#11: views-3.x-dev-issue_1331056-2.patch queued for re-testing.
Comment #20
thekevinday CreditAttribution: thekevinday commentedBefore I start to research why the glossary is failing, I am resubmitted the patch because there seem to have been quite a lot of changes in the views module in the past couple of months.
So, try the test bot again and see what happens with the same code, but an updated/resynced patch.
Comment #22
thekevinday CreditAttribution: thekevinday commentedIt seems that using $fields_array directly after calling $this->compile_fields() is what caused the test failures.
Non-table-column-fields are being included, such as COUNT(..).
I decided that the proper solution would be use $this->compile_fields() directly and so I made my changes there.
I changed the behavior of how $this->compile_fields() returns the fields.
It now returns an array of arrays, one sub-array being the usual non_aggregates and a new sub-array containing the actual fields prior to compilation.
I also had to change the behavior for when groupby gets added.
When any groupby field gets added, according to the SQL standards, all of the groupby fields must be added.
The behavior is to add all fields to the groupby if any single groupby is added or if aggregation is enabled and non-aggregates exist.
Comment #24
thekevinday CreditAttribution: thekevinday commentedOkay, I will get working (aka correct) patch in tomorrow.
After fixing the typos, I found that there were some additional problems.
The fault for the additional problems is mine from this patch: #1431600: Invalid use of SUBSTRING() sql and placeholder_length for views_handler_argument_string.
I made two mistakes:
1) I did not add the field to the groupby array.
2) The fix in the patch applied to the functional code, but the simpletests were not updated to apply the fix (Meaning the tests themselves are still invalid).
- I will make a separate bug report for this tomorrow.
Comment #25
thekevinday CreditAttribution: thekevinday commentedA good nights rest seems to have unfogged my mind.
From #24, mistake #2 is not a mistake.
- I reviewed the code and saw nothing wrong and simpletest does in fact pull the correct data (I had my debug statement mis-typed).
The problem with my patch in #22 is that I am including aggregate fields along with non-aggregates in the GROUP BY.
It turns out that the standards allow for aggregates to not be specified in the GROUP BY.
The original code in this respect is perfectly fine.
The new patch is very simple and resolves the standards violation, thus avoiding "ambiguous column error" as far as I can tell.
Comment #26
thekevinday CreditAttribution: thekevinday commentedWhen DISTINCT is used, the non-aggregates did not get included in the GROUP BY.
Comment #27
dawehnerThe patch itself seems to like quite simply though i would like to be really sure, so it would be cool if you could some kind of simpletest code which shows that nothing will be broken in the future.is broken
Comment #28
robertwb CreditAttribution: robertwb commentedJust putting in my experience testing this - it seems to work without flaw on my test case:
I have a set of relationships where I have one content type (Water Planning Region) which has multiple other content types (Water Use Types) with an entity reference. I am doing a view which lists the Region name and a column for each use type with a count of the number of users in that type. This patch seems to work perfectly.
Here is the SQL generated by views after applying this patch:
Comment #29
thekevinday CreditAttribution: thekevinday commentedIt looks like this patch is waiting on me to write simpletest support.
I had completely lost track of this issue.
If anybody has any tests cases that we can use, please send them.
I need to come up with a bunch of test cases for this patch to get applied.
(And I need to first learn how simpletests works, but given then name I assume it will be simple).
Perhaps sending exported views that don't work before but do work after this patch would be helpfull.
Comment #30
steinmb CreditAttribution: steinmb commentedComing from #1400988: PostgreSQL 'Exception: SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "fid" is ambiguous
Patch in #26 fixed my issue with Postgres 9.2.x. Patch still applies on latest stable. Let's get a reroll on latest dev.
Comment #31
steinmb CreditAttribution: steinmb commentedThat was a easy re-roll, it still applied :)
Attaching a view that fails in Postgres. Note, this is the Media module default file browser view. So to make it work (Format: Media browser ) you also need Media module 7.x-2.x.
Comment #32
thekevinday CreditAttribution: thekevinday commentedIt appears that the existing simpletests always passed because a contextual filter was used, which somehow changes the way in which the query is generated and bypasses the sql problem.
I have added a new simpletest called "testAggregateAmbiguity" to the groupby simpletest class.
The simpletest view is designed to trigger the ambiguity bug by doing the following:
1) pull in two different tables that have at least one column with the same name, such as: node and node_revision.
2) do not use contextual filters.
3) add an aggregate operation, such as COUNT()
4) select the nid from both the node and the node_revision tables.
If you wish to see the test fail, then revert the changes to plugins/views_plugin_query_default.inc.
Edit:
There is a grammar error with the added simpletest.
The test should read: 'Make sure there are no ambiguity problems with the group by operation.'
Comment #33
steverweber CreditAttribution: steverweber commentedWe had a similar issue #1400988: PostgreSQL 'Exception: SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "fid" is ambiguous
Your solution solved it.
Thanks
Comment #34
steverweber CreditAttribution: steverweber commentedHas enough testing been completed on this patch?
It would be nice to have this in the dev branch.
Thanks
Comment #35
steinmb CreditAttribution: steinmb commentedWe have tested it, and used it in production for a few months with no problems, though this is #31. I'll will have a look at #32 that include the improved testscase.
Comment #36
steinmb CreditAttribution: steinmb commentedLooking RTBC too me.
Comment #37
thekevinday CreditAttribution: thekevinday commentedI found a mistake from the change introduced in #32.
When filtering out non-aggregates for distinct tables, the
$fieldname
variable was being used instead of the$string
variable.The
$fieldname
variable potentially contains the alias (and if no alias, then it contains the original field name).The
$string
variable contains the original field name.The only exception to using
$fieldname
instead of$string
appears to be when the original fieldname does not come from a table, but is instead a string (Note: string and$string
represent different things here). Drupal seems to do this with things likeSELECT 'node' AS field_data_field_event_group_node_entity_type ...
.Please double-check my logic I am feeling rather dyslexic at the moment.
Comment #38
steverweber CreditAttribution: steverweber commentedThis patch worked good for me. I think its RTBC.
Comment #39
seanBI had this problem after migrating a database from MySQL to Postgres. The patch from nr 37 worked for me as well! Thnx for the fix.
I did need to turn on the 'Pure distinct' checkbox, did was not the case for MySQL databases. Not sure if this is a bug?
Comment #40
sylus CreditAttribution: sylus commentedCan also confirm this fixes problems with Media module nicely :)
Comment #41
acbramley CreditAttribution: acbramley commented+1 this patch (#37) fixes the Media module Media library view issue.
Comment #42
steinmb CreditAttribution: steinmb commentedAs stated in #35 we are running it in production and on multiple dev. instances and with no issues.
Comment #43
steinmb CreditAttribution: steinmb commentedThis issue have been rusting way in there. Any changes this would make it into dev?
Comment #44
drmabuse_ CreditAttribution: drmabuse_ commentedHi!
I'm afraid, the patch does not longer apply. I tried to use it with the latest Views module (7.x-3.7+30-dev) but it didn't work.
Comment #45
steinmb CreditAttribution: steinmb commentedHmmm not sure about that. This is against HEAD:
Against 7.x-3.8:
Comment #46
acbramley CreditAttribution: acbramley commentedIt applies fine to 3.8 with drush make:
Comment #47
bellerophons_pegasus CreditAttribution: bellerophons_pegasus commentedThe patch in #37 works for me with Drupal 7.31, PostgreSQL and Views 7.x-3.8
Thank you!
Comment #48
Proteo CreditAttribution: Proteo commentedThanks, the patch in #37 also worked for me. Same as above, Drupal 7.31, PostgreSQL 9.1 and Views 7.x-3.8.
Comment #49
Proteo CreditAttribution: Proteo commentedHi. This issue still is causing problems with PostgreSQL and Views 7.x-3.10. Among the affected modules are Media (specifically the media browser, already commented above) but I just found today that Similar By Terms is having the very same problem, causing the module's block to come up empty.
The patch from #37 still works fine with 7.x-3.10. It would be great if this issue gets some attention, thanks!
Comment #50
Proteo CreditAttribution: Proteo commentedUnfortunately the issue still causes problems for PostgreSQL users in 7.x-3.11, and the patch in #37 no longer applies cleanly:
Comment #51
steinmb CreditAttribution: steinmb commentedComment #52
robertwb CreditAttribution: robertwb commentedRe-rolled this patch and verified that it would apply successfully against current dev branch. No substantive changes made whatsoever.
Comment #53
Proteo CreditAttribution: Proteo commentedThanks @robertwb. I can confirm the new patch in #52 works fine. Any chance to have this committed before the next release?
Comment #56
robertwb CreditAttribution: robertwb commented@matheushamonangan - looks like you triggered a retest on the patch in #37 not #52 (which had already passed). Thoughts?
Comment #57
matheushamonangan CreditAttribution: matheushamonangan at Catalyst IT commented@robertwb, I had patch #37 in my build.make. When I updated the views to get the security updates, I cannot re-apply patch #37 so I triggered test for patch #37 to double check. Now I'm using patch #52 for views 7.x-3.11 and have tested in a live site it's working as expected. Thank you
Comment #58
robertwb CreditAttribution: robertwb commentedchanged to RTBC.
Comment #59
colanWe've recently switched our testing from the old qa.drupal.org to DrupalCI. Because of a bug in the new system, #2623840: Views (D7) patches not being tested, older patches must be re-uploaded. On re-uploading the patch, please set the status to "Needs Review" so that the test bot will add it to its queue.
If all tests pass, change the Status back to "Reviewed & tested by the community". We'll most likely commit the patch immediately without having to go through another round of peer review.
We apologize for the trouble, and appreciate your patience.
Comment #60
colanComment #61
robertwb CreditAttribution: robertwb commentedRe-uploaded for testing.
Comment #62
robertwb CreditAttribution: robertwb commentedComment #63
Proteo CreditAttribution: Proteo commentedJust got bitten by this (again) after updating to 7.x-3.14, and the patch in #61 worked great. Please, commit!
Comment #64
scott.whittaker CreditAttribution: scott.whittaker as a volunteer commentedPatch #61 works for me too (PHP 5.5.9, Postgres 9.5.3)
Comment #65
robertwb CreditAttribution: robertwb commentedDoes anyone know if this has been fixed in D8? I think that the "backports only" policy may be holding up committing this.
Comment #66
colanThis may have changed, but I believe we need to open a new ticket for D8 if it's also a problem there.
@robertwb: As I'm assuming this hasn't been fixed in D8 given that there's been no mention of it here (check if you can), I'd recommend creating a ticket and cross-referencing it with this one.
Once there's a new ticket, or someone confirms that this isn't a problem in D8, I can commit this (if someone pings me on IRC).
Comment #67
Proteo CreditAttribution: Proteo commentedJust FYI: the patch from #61 still works great with the latest release (7.x-3.15).
Comment #68
Anonymous (not verified) CreditAttribution: Anonymous commentedHello,
Thanks @robertwb, this patch works for me too !
PHP v7.0.10
Postgres v9.4.9
Drupal v7.54
Comment #69
Proteo CreditAttribution: Proteo commentedJust reporting, the patch from #61 works fine with 7.x-3.16. Hope you guys can devote some time to this issue.
Comment #70
steinmb CreditAttribution: steinmb commentedLooking at the failing tests on #61 I would say there is work to be done.
Comment #71
chamilsanjeewa CreditAttribution: chamilsanjeewa as a volunteer and commentedDue to type casting, I was unable to use flag in views (drupal 8.3 + postgres 10.1).
Is there any patch for this ?
Comment #72
joseph.olstadrerolling 61
same code, just one space caused 4th segment to fail, I removed the offending part of the patch, it's the exact same code as #61
same patch as #61 rerolled for 7.x-3.x-dev
NOTE: I don't use postgresql, however this patch is an improvement compared to the current status of postgresql tests.
postgresql tests of head are failing on "tuple concurrently updated"
I expect by the above reports that this patch will improve the postgresql test results.
Comment #73
joseph.olstadComment #74
joseph.olstadviews release 3.19 is nearly ready. It appears that postgres test results may be holding this release up. It'd be nice if someone with postgresql would step up.
Comment #75
DamienMcKennaThis is nice, and I really appreciate the effort to add a test for it.
Comment #76
DamienMcKennaLets focus on fixing the test suite itself in #2960146: Fix PostgreSQL test problems in Views on D7, but I'm happy with the new changes.
Comment #77
DamienMcKennaLets see if the tests on their own trigger a failure.
Comment #78
DamienMcKennaComment #79
DamienMcKennaThe tests on their own don't trigger a failure, but the problem apparently didn't exist in MySQL anyway, so I think this is safe.
Comment #81
DamienMcKennaCommitted. Thanks everyone!
Comment #82
Proteo CreditAttribution: Proteo commentedGreat, thanks everybody!
Comment #84
webengr CreditAttribution: webengr commentedyep and you broke it... https://www.drupal.org/project/views/issues/2966615