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

Files: 
CommentFileSizeAuthor
#37 views-3.x-dev-issue_1331056-36.patch6.67 KBthekevinday
PASSED: [[SimpleTest]]: [MySQL] 1,661 pass(es).
[ View ]
#32 views-3.x-dev-issue_1331056-32.patch6.84 KBthekevinday
PASSED: [[SimpleTest]]: [MySQL] 1,661 pass(es).
[ View ]
#31 views-3.x-dev-issue_1331056-31.patch2 KBsteinmb
PASSED: [[SimpleTest]]: [MySQL] 1,603 pass(es).
[ View ]
#31 media_browser_default.views_.txt9.43 KBsteinmb
#26 views-3.x-dev-issue_1331056-6.patch2 KBthekevinday
PASSED: [[SimpleTest]]: [MySQL] 1,458 pass(es).
[ View ]
#25 views-3.x-dev-issue_1331056-5.patch1.68 KBthekevinday
PASSED: [[SimpleTest]]: [MySQL] 1,451 pass(es).
[ View ]
#22 views-3.x-dev-issue_1331056-4.patch3.36 KBthekevinday
FAILED: [[SimpleTest]]: [MySQL] 1,430 pass(es), 41 fail(s), and 796 exception(s).
[ View ]
#20 views-3.x-dev-issue_1331056-3.patch1.06 KBthekevinday
FAILED: [[SimpleTest]]: [MySQL] 1,430 pass(es), 41 fail(s), and 0 exception(s).
[ View ]
#11 views-3.x-dev-issue_1331056-2.patch1.06 KBthekevinday
FAILED: [[SimpleTest]]: [MySQL] 1,371 pass(es), 41 fail(s), and 0 exception(s).
[ View ]
#10 views-3.x-dev-issue_1331056-1.patch868 bytesthekevinday
Test request sent.
[ View ]

Comments

Status:Active» Postponed (maintainer needs more info)

Are any of your Workbench Views overridden?

I've never modified a workbench related view ... so I'd have to say no ... only views I've ever played with is calendar related ...

k, 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?

Status:Postponed (maintainer needs more info)» Active

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

Title:Ambiguous column: 7 ERROR: column reference "vid" is ambiguous LINE 7: ...de_revision_name, users_node_revision_uid, nod...Regression: "Unified way to retrieve results" causes ambiguous column error with workbench module in postgresql database
Project:Workbench» Views
Version:7.x-1.1» 7.x-3.0-rc3

Thanks 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..).

I would love to temporarily revert this commit on my end until a proper solution is made (so much for wishful thinking..).

You know you could solve your issue such easy by using mysql. Pgsql is like theory, it's nice in theory.

actually, 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).

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.

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

node_revision.vid AS vid

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?

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?

I 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):

<?php
  
// Add the field if the query back-end implements an add_field() method,
    // just like the default back-end.
   
if (method_exists($this->query, 'add_field')) {
     
$this->field_alias = $this->query->add_field($this->table_alias, $this->base_field, '');
    }
?>

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:

<?php
   
// We check for this specifically because it gets a special alias.
   
if ($table == $this->base_table && $field == $this->base_field && empty($alias)) {
     
$alias = $this->base_field;
    }
?>

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.

Title:Regression: "Unified way to retrieve results" causes ambiguous column error with workbench module in postgresql databaseRegression: Improper use of GROUP BY statement produces ambiguous column error
Status:Active» Needs review
StatusFileSize
new868 bytes
Test request sent.
[ View ]

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

SELECT node.nid AS node_nid, node_revision.vid AS vid
FROM
node_revision node_revision
LEFT JOIN node node ON node_revision.nid = node.nid
GROUP BY nid, vid;

This query succeeds:

SELECT node.nid AS node_nid, node_revision.vid AS vid
FROM
node_revision node_revision
LEFT JOIN node node ON node_revision.nid = node.nid
ORDER BY vid;

This query succeeds:

SELECT node.nid AS node_nid, node_revision.vid AS vid
FROM
node_revision node_revision
LEFT JOIN node node ON node_revision.nid = node.nid
GROUP BY node.nid, node.vid, node_revision.vid

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

StatusFileSize
new1.06 KB
FAILED: [[SimpleTest]]: [MySQL] 1,371 pass(es), 41 fail(s), and 0 exception(s).
[ View ]

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

<?php
 
if (preg_match("/^'.*'$/i", $field['field']) > 0){
    continue;
  }
?>

Once again, please review for any mistakes I may have made.

If you have expressions can't you add them to groupby as well? At least for ordering this works pretty well.

First off, my first example case (the one that is supposed to fail) had a typo and should be:

SELECT node.nid AS node_nid, node_revision.vid AS vid
FROM
node_revision node_revision
LEFT JOIN node node ON node_revision.nid = node.nid
GROUP BY node_nid, vid;

The problem is that the two tables node_revision and node look like:

node

  Column   |          Type          |                     Modifiers
-----------+------------------------+----------------------------------------------------
nid       | integer                | not null default nextval('node_nid_seq'::regclass)
vid       | bigint                 | not null default 0
type      | character varying(32)  | not null default ''::character varying
language  | character varying(12)  | not null default ''::character varying
title     | character varying(255) | not null default ''::character varying
uid       | integer                | not null default 0
status    | integer                | not null default 1
created   | integer                | not null default 0
changed   | integer                | not null default 0
comment   | integer                | not null default 0
promote   | integer                | not null default 0
sticky    | integer                | not null default 0
tnid      | bigint                 | not null default 0
translate | integer                | not null default 0

node_revision
  Column   |          Type          |                          Modifiers
-----------+------------------------+-------------------------------------------------------------
nid       | bigint                 | not null default 0
vid       | integer                | not null default nextval('node_revision_vid_seq'::regclass)
uid       | integer                | not null default 0
title     | character varying(255) | not null default ''::character varying
log       | text                   | not null
timestamp | integer                | not null default 0
status    | integer                | not null default 1
comment   | integer                | not null default 0
promote   | integer                | not null default 0
sticky    | integer                | not null default 0

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

Triggering the testbot.

The test bot results seem to be stuck in the step: "Test request sent" for almost a month now.

Version:7.x-3.0-rc3» 7.x-3.x-dev

That would be why.

Status:Needs review» Needs work

The last submitted patch, views-3.x-dev-issue_1331056-2.patch, failed testing.

Status:Needs work» Needs review

#11: views-3.x-dev-issue_1331056-2.patch queued for re-testing.

Status:Needs review» Needs work

The last submitted patch, views-3.x-dev-issue_1331056-2.patch, failed testing.

Status:Needs work» Needs review
StatusFileSize
new1.06 KB
FAILED: [[SimpleTest]]: [MySQL] 1,430 pass(es), 41 fail(s), and 0 exception(s).
[ View ]

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

Status:Needs review» Needs work

The last submitted patch, views-3.x-dev-issue_1331056-3.patch, failed testing.

Status:Needs work» Needs review
StatusFileSize
new3.36 KB
FAILED: [[SimpleTest]]: [MySQL] 1,430 pass(es), 41 fail(s), and 796 exception(s).
[ View ]

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

Status:Needs review» Needs work

The last submitted patch, views-3.x-dev-issue_1331056-4.patch, failed testing.

Okay, 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.

Status:Needs work» Needs review
StatusFileSize
new1.68 KB
PASSED: [[SimpleTest]]: [MySQL] 1,451 pass(es).
[ View ]

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

StatusFileSize
new2 KB
PASSED: [[SimpleTest]]: [MySQL] 1,458 pass(es).
[ View ]

When DISTINCT is used, the non-aggregates did not get included in the GROUP BY.

Status:Needs review» Needs work
Issue tags:+Needs tests

The 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

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

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, COUNT(DISTINCT field_wsp_ssu_ag_group_node.title) AS field_wsp_ssu_ag_group_node_title, COUNT(DISTINCT field_ssu_nonag_gt300k_node.title) AS field_ssu_nonag_gt300k_node_title
FROM
{node} node
LEFT JOIN {field_data_field_wsp_ssu_ag_group} field_data_field_wsp_ssu_ag_group ON node.nid = field_data_field_wsp_ssu_ag_group.field_wsp_ssu_ag_group_target_id
LEFT JOIN {node} field_wsp_ssu_ag_group_node ON field_data_field_wsp_ssu_ag_group.entity_id = field_wsp_ssu_ag_group_node.nid
LEFT JOIN {field_data_field_ssu_nonag_gt300k} field_data_field_ssu_nonag_gt300k ON node.nid = field_data_field_ssu_nonag_gt300k.field_ssu_nonag_gt300k_target_id
LEFT JOIN {node} field_ssu_nonag_gt300k_node ON field_data_field_ssu_nonag_gt300k.entity_id = field_ssu_nonag_gt300k_node.nid
WHERE (( (node.status = '1') AND (node.type IN  ('wsp_planning_entity')) ))
GROUP BY node.title, node.nid, node.created
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

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

Priority:Normal» Major

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

StatusFileSize
new9.43 KB
new2 KB
PASSED: [[SimpleTest]]: [MySQL] 1,603 pass(es).
[ View ]

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

Status:Needs work» Needs review
StatusFileSize
new6.84 KB
PASSED: [[SimpleTest]]: [MySQL] 1,661 pass(es).
[ View ]

It 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.'

Status:Needs review» Reviewed & tested by the community

Has enough testing been completed on this patch?

It would be nice to have this in the dev branch.
Thanks

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

Looking RTBC too me.

Status:Reviewed & tested by the community» Needs review
StatusFileSize
new6.67 KB
PASSED: [[SimpleTest]]: [MySQL] 1,661 pass(es).
[ View ]

I 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 like SELECT 'node' AS field_data_field_event_group_node_entity_type ....

Please double-check my logic I am feeling rather dyslexic at the moment.

Status:Needs review» Reviewed & tested by the community

This patch worked good for me. I think its RTBC.

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

Can also confirm this fixes problems with Media module nicely :)

+1 this patch (#37) fixes the Media module Media library view issue.

As stated in #35 we are running it in production and on multiple dev. instances and with no issues.