I have the following system (all modules up-to-date stable):
- Content Profile-module
- Multiple custom fields in profile
- a view-"Relationship" with "Content Profile"
- a view showing two user-fields and two custom-fields of "Content Profile"
I get the following error (after creating the view via dialog):
user warning: Column 'nid' in field list is ambiguous query: SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, node_users_node_data_field_age.field_age_value AS node_users_node_data_field_age_field_age_value, node_users.nid AS node_users_nid, node_users.type AS node_users_type, node_users.vid AS node_users_vid, field_residence_value, nid, type, vid FROM proto_users users INNER JOIN proto_node node_users ON users.uid = node_users.uid AND node_users.type = 'profile' LEFT JOIN proto_content_type_profile node_users_node_data_field_age ON node_users.vid = node_users_node_data_field_age.vid ORDER BY users_name ASC LIMIT 0, 25 in /home/jschoder/Documents/projekte/apache/active-for/modules/views/includes/view.inc on line 755.
The preview shows this SQL-statement:
SELECT users.uid AS uid,
users.picture AS users_picture,
users.name AS users_name,
node_users_node_data_field_age.field_age_value AS node_users_node_data_field_age_field_age_value,
node_users.nid AS node_users_nid,
node_users.type AS node_users_type,
node_users.vid AS node_users_vid,
field_residence_value,
nid,
type,
vid
FROM proto_users users
INNER JOIN proto_node node_users ON users.uid = node_users.uid AND node_users.type = 'profile'
LEFT JOIN proto_content_type_profile node_users_node_data_field_age ON node_users.vid = node_users_node_data_field_age.vid
ORDER BY users_name ASC
Comment | File | Size | Author |
---|---|---|---|
#20 | 607418-ensure-additional-table-actually-ensured.patch | 3.24 KB | merlinofchaos |
#18 | rh_schema_only.tgz | 14.6 KB | neilnz |
#7 | 607418-group-by-is-ambiguous.patch | 628 bytes | Josh Waihi |
Comments
Comment #1
axlord CreditAttribution: axlord commentedsame here.
the relevant modules im using are:
-cck
-content profile
i installed other modules, i'm not sure they're relevant but i list them anyway
- profile permission
- profile fields
what im trying to achieve is to put a profile node with custom data (i need it to be numeric data) and extract the data in there (put there by content profile and cck) into a page and a block. this is the exported view i made:
my error message is as follows:
user warning: Column 'nid' in field list is ambiguous query: SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, users.created AS users_created, node_users_node_data_field_weapon.field_weapon_value AS node_users_node_data_field_weapon_field_weapon_value, node_users.nid AS node_users_nid, node_users_node_data_field_weapon.delta AS node_users_node_data_field_weapon_delta, node_users.type AS node_users_type, node_users.vid AS node_users_vid, field_flags_value, nid, type, vid, field_deaths_value, field_rankpts_value, node_users_node_data_field_rankpts.field_rankpts_value AS node_users_node_data_field_rankpts_field_rankpts_value FROM users users INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'profile' LEFT JOIN content_field_weapon node_users_node_data_field_weapon ON node_users.vid = node_users_node_data_field_weapon.vid LEFT JOIN content_type_profile node_users_node_data_field_rankpts ON node_users.vid = node_users_node_data_field_rankpts.vid WHERE users.uid not in ('0') ORDER BY node_users_node_data_field_rankpts_field_rankpts_value DESC LIMIT 0, 30 in /home/axlord/www/drupal-6.14/modules/views/includes/view.inc on line 755.
here i formatted the query a little for reading:
so that's about it. thank you in advance for attention.
Comment #2
toniher CreditAttribution: toniher commentedI had this same problem, and after a few minutes I noticed I was forgetting to add relationships to some fields. You may try to check this.
Comment #3
merlinofchaos CreditAttribution: merlinofchaos commentedI've occasionally seen this, but the conditions that create it seem to be complex enough that I've never been able to duplicate it reliably enough to debug it. I would love it if anyone could come up with reliable duping conditions (preferably without needing CCK fields, but I recognize that this may be necessary).
Comment #4
drupal92037 CreditAttribution: drupal92037 commentedFWIW... I just encountered the problem and it went away.
I created a view of user nodes. Then, in the view, I created a relationship to a "user profile" cck type I had created, which included 3 text fields for first, middle and last name. Then I added the first, middle and last name fields. At that point, I got the error. The fields' formats were all "Default". I set the formats to "Plain Text" and the error went away.
Being a newbie, I don't understand why it happened, but I thought this might help someone out there diagnose and/or fix the problem.
I'm on Drupal 6.14 w/ Views 6.x-2.7 and CCK 6.x-2.6.
Comment #5
ericbellot CreditAttribution: ericbellot commentedI just encountered the problem when I added some CCK fields at the same time in my view (Content Profile and User fields).
I have deleted all fields and new added this fields one by one (exactly the same fields)... no more problem !
Comment #6
BWPanda CreditAttribution: BWPanda commentedFlexiField has the same issue (#606412: Views warning: Ambiguous query)...
Comment #7
Josh Waihi CreditAttribution: Josh Waihi commentedI've got this problem, fixed it by referencing the table name in the GROUP BY clause. See attached below.
Comment #8
merlinofchaos CreditAttribution: merlinofchaos commentedHmm. The original query doesn't have a GROUP BY. I don't think this fix addresses the original problem.
I haven't actually figured out what *causes* the original problem.
Comment #9
dagmarOnly for contribute with this issue:
I have noticed that sometime, when several fields are added together (lets say, Name, Surname and Age from a content Profile added all together not one by one) views fires and "Column 'nid' in field list is ambiguous query" error.
This is because relationships are not correctly saved. This is fixed if you go one by one filed not configured and save the changes again.
Probably my case is only related to Content Profile, and probably it should be fixed providing a default relationship for the field. However since a field may be in several content types this is not easy to determine, and for this reason Content Profile doesn't provide a default relationship.
So, #1, #2, #5, #6, what do you think about this explanation?
Comment #10
drupal92037 CreditAttribution: drupal92037 commentedThe workaround posted in #5 above by ericbellot also worked for me. I removed all the fields, then added them one by one, saving after each add.
Comment #11
dagmarDoes anybody can replicate this issue in a different way to explained in #9? If not, we should make this as Won't fix, or move to Content Profile issue queue.
Comment #12
webchickThis is the first search result in Google, so I figured I would put my findings here...
I saw this issue occur on one of a client's custom module's views after the update from Drupal 6.14/Views 6.x-2.7 and Drupal 6.15/Views 6.x-2.8. I do not know which of these (or both) was the culprit, unfortunately.
I had no idea where to begin debugging this issue so I naturally searched for other issues where this problem was discussed, and came across #348864-18: user warning: Column 'nid' in field list is ambiguous query. There, the module developer recommends always setting Distinct: Yes to get around this problem, and it occurred to me that we had had this problem before, and setting Distinct: Yes solved it. This time I did the reverse, and changed Distinct: Yes to Distinct: No, and voila. No more red errors of death. Huh.
I don't think this is enough info to bring this out of "postponed (maintainer needs more info)" but maybe this will provide some kind of a breadcrumb to the next person who hits this. Though I would welcome a rational explanation as to why I accidentally fixed this by grasping at straws. ;) The only core issue that comes to mind is #284392: db_rewrite_sql causing issues with DISTINCT, but I don't recall any commits for that issue for 6.15...
Oh, I guess fwiw, the client is using Acquia Drupal, so technically it was an update from 1.2.19 (Drupal 6.14 core) to 1.2.21 (Drupal 6.15 core). But afaik, AD sticks with un-hacked copies of Drupal / modules.
Comment #13
neilnz CreditAttribution: neilnz commentedAfter looking through other related-looking issues (#506818: [Postgres] GROUP BY uses field names instead of aliases, #460838: Distinct setting doesn't always work), I came across the patch from #7 here, which is the only one that fixes this problem for me.
My view is (note no content profile):
The bad SQL generated (before the patch) is:
The Postgres error:
And the good SQL that works (after the patch):
Based on this, and the fact that it doesn't break any of my other views, I'm happy to mark Josh's patch RTBC (although I guess someone needs to confirm it doesn't break MySQL).
Comment #14
dagmar@neilnz Sorry, but as merlinofchaos said in #8 some users are not using Group By in theirs queries.
Probably this is a kind of meta issue that is triggered by different causes.
Anyway, if patch in #7 have to be committed, it needs a re-roll since Views 3 is not using query.inc anymore.
But IMO, #7 is not enough to mark this whole issue as RTBC, sorry.
@webchick, Is there any chance to see the view export from your client's view?
Comment #15
jwilson3I have an OpenAtrium install running on PGSQL that was getting these same errors. Actually I was getting ambiguous "uid" in some places and ambiguous "nid" in others depending on which table was the pivot, and which was being joined (aka views relationship). One common page the error could be seen was on the member directory. Applying the two-line patch in #7 fixed the errors for the members page as well as in other custom views.
Comment #16
grub3 CreditAttribution: grub3 commentedLooking at:
It seems to me that if you do a
SELECT DISTINCT ON (node.nid) FROM node GROUP BY node.nid
It is equivalent to a
SELECT (node.nid) FROM node LEFT JOIN ...
and then replacing INNER JOINS with LEFT JOINs and maybe some subqueries (?)This is only a first impression, don't flame me!
Could you print the data definition involved. Just open pgAdmin3, copy table definition and paste here.
It will allow me to play with your SQL.
In general, DISTINCT should never be used and replaced with LEFT JOINs whenever possible, because DISTINCT creates sequential scans in every database, just to differentiate data. You may not notice a difference on 10 entries, but on 100.000 rows it can kill your database.
Also when using DISTINCT, it is very hard to predict how the database reacts with JOINs.
Please note I am not sure to help you, I can only try.
Comment #17
Josh Waihi CreditAttribution: Josh Waihi commentedthis is related to #460838: Distinct setting doesn't always work.
Comment #18
neilnz CreditAttribution: neilnz commentedHere's a schema-only dump of the database from pg_restore in SQL format. Let me know if something else would be more useful.
Comment #19
merlinofchaos CreditAttribution: merlinofchaos commentedJosh's problem in #7 is http://drupal.org/node/506818
Anything dealing with postgres and group by should be used in that issue.
Let's use this issue to work on the issue where sometimes the base field is added without an alias -- that seems to happen only in some strange relationship cases, as dagmar mentioned in #9.
Comment #20
merlinofchaos CreditAttribution: merlinofchaos commentedOk, I believe I understand this issue, thanks very much to q0rban reproducing this with a custom relationship and sending me the tarball so I could dsm() the crap out of it.
What happens is that some fields might use the additional_fields() mechanism of views_handler_field to try and get more fields, but due to the complexity of the relationships, those tables might not be automatically added.
What then happens is that views_handler_field::add_additional_fields() might try to add a field even though ensure_table failed. Then, query::add_field() is called with $table NULL, which means that the field is considered a formula and added by itself. Therefore 'nid' is added. Unfortunately, we can't detect and prevent that condition, since that's how formulae like like LEFT(node_title, 1) are added as fields. We can stop it in add_additional_fields() but that won't prevent handlers from failing to test if ensure_table produced a result and error appropriately if it did not.
We can, however, test add_additional_fields().
Here's a patch. I'd love it if anyone who experiences this issue *not* related to the pgsql issue could test this.
Comment #21
merlinofchaos CreditAttribution: merlinofchaos commentedComment #22
q0rban CreditAttribution: q0rban commentedI tested this and it did indeed hide the errors, printed a nice debug error, and the view actually output again. :)
I'm going to still leave this as needs review since merlinofchaos was probably working from my codebase when he tested it.
Comment #23
bevin CreditAttribution: bevin commentedI got solution unless it is only work for me.
I used:
* drupal-6.16
* views 6.x-2.8
I found the problem is only because I selected 'distinct' n tick 'yes'. now I changed to 'no'.
the procedures is as below:
go to admin > site buildings > views
click in edit of taxonomy_term(default)
in default play : basic settings : distinct, if that is yes (because my setting is Yes by default), just change to NO.
that is all. By the way, I dont have any relationships in that settings. and my arguments are termID and termID with depth. sorts by book weight asc. filters: node published and admin. others are keep default.
my taxonomy pages has no errors, which show books under by sequence of book weight.
wish it could help to somebody.
Comment #24
ezra-g CreditAttribution: ezra-g commentedI experienced this error on a site in Views 2.x, and the patch in #20 resolved it. I realize that's not much of a thorough review -- I'd be happy to post an output of the affected view if that helps.
Thanks!
Comment #25
gg4 CreditAttribution: gg4 commentedI was seeing the warning when adding imagefield as a relationship. Patch seems to have resolved the issue.
Drupal 6.16
Views 2.10
Filefield + Imagefield 3.3
Comment #26
rlo CreditAttribution: rlo commentedHello,
I tested the patch and it doesn't seem to work for me.
Environment: Drupal 6.16, MySQL 5.1.47, PHP 3.2.3, Views 6.x-2.10, Views JSON(Datasource) 6.x-1.0-beta1, CCK Not installed.
These are the steps I took in testing a JSON content view:
1. Added a relationship of comment to node.
2. Added 4 fields at the same time. Preview.
3. Received the ambiguous error. Notice 3 fields are actually ambiguous:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, language, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
4. Removed the 4 fields and added each one individually, as suggested above. Did not save until all were added.
5. Error was gone.
6. Repeated, by removing and then adding 4 fields at the same time.
7. Received the ambiguous error again exactly the same.
8. Added the patch code.
9. Went back to preview view and received slightly different error: language is out of the query:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
10. Removed the 4 fields and added them again at the same time.
11. Received same ambiguous error:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
12. Commented out the patch code and hit preview again.
13. Received ambiguous error with language once again part of query:
user warning: Column 'nid' in field list is ambiguous query: SELECT comments.cid AS cid, comments.name AS comments_name, comments.uid AS comments_uid, comments.homepage AS comments_homepage, comments.comment AS comments_comment, comments.format AS comments_format, nid, title, language, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN node node_comments ON comments.nid = node_comments.nid ORDER BY comments_timestamp DESC LIMIT 0, 1 in C:\UniServer\www\drupal-6.16\sites\all\modules\views\includes\view.inc on line 771.
14. Removed the 4 fields and added each individually, did not save until all were added.
15. Error is gone, code is returned in preview.
It seems the patch successfully removed one ambiguous field. Hope this helps a bit. I'm up and running with adding each field separately.
edit. sorry seemed to have changed the priority here-
Comment #27
rlo CreditAttribution: rlo commentedComment #28
dawehnerThis patch looks fine.
I broke manually a field and this debug message appeared and the view works further. This is good
Comment #29
merlinofchaos CreditAttribution: merlinofchaos commented#26 rlo: Ok, I reproduced your problem, and I understand it.
Your problem happens because when 'node' fields are added, they actually *require* a relationship. However, if you don't actually go and 'update' the field, they are set, by default, to use the base relationship. They cannot use this relationship, though, because they are not valid for 'comment' base, they are only valid for 'node' base. As soon as you click 'update' on the field, this problem goes away because the relationship is forced to be set by the selector.
This means that the default relationship logic needs some help. However it's not a critical issue because it's easily worked around by clicking 'update' on the broken field. The patch does fix other problems, though, and is being committed.
Can you please reproduce your comment in a new issue so we can address that separately? And thank you very much for the thoroughly detailed report you gave!
#20 has been committed to all branches.
Comment #30
rlo CreditAttribution: rlo commentedThanks for the education. I've opened #830716: Column 'nid' in field list is ambiguous query~ while adding multiple fields and not setting relationship -hope it is sufficient.
Comment #31
rlo CreditAttribution: rlo commented