Distinct no longer working

I have the same problem. I filed a (duplicate) bug report before I found this.
#624950: Distinct doesn't work with aggregator feed title

yep.. same problem here

Same problem here. Distinct seemed to be working until this last update. View export attached.
Any help with this is most appreciated!

I experienced this as well for several views.

We've got the same issues, all views that have a distinct no longer work. We checked through the code, seems the problem lies with the set_distinct function in includes/query.inc, or rather with the function node_access_view_all_nodes() which is used to determine whether access it authorized, related to issue #284392: db_rewrite_sql causing issues with DISTINCT.

It tests whether there is a record in node_access with nid=0, which in our case doesn't exist.. There is a record with nid = 1 however.

I added a record with nid=0 to the table, which seems to have restored the distinct option functionality.

Seems to be part of an ongoing discussion.. see #579892: Authors and only authors see duplicate items after upgrading Drupal to 6.14 and #501552: Provide workaround for core DISTINCT bug in db_rewrite_sql

Upgrading to the latest (Nov 3rd) dev version seems to have fixed the problem for us.

I ended up reverting the DISTINCT workaround because the code actually caused problems with other tables.

That should fix this issue.

#8, yes, upgrading to the dev version fixed the problem for me

I don't know if someone else is also experiencing my problem and if it's related. I have a teaser view wich show distinct nodes for the administrator and anonymous users but for other roles the nodes appear duplicated (all of them). I have two filters applied to the view "node: type" and "workflow: current state", this could be the reason of the duplicated nodes.

Regards.

"Distinct" "yes" failed in 6.x-2.7. (found the reason and upgrade to Alpha1 was reported to have fixed it)
The distinct failure would display duplicates of the images in each gallery...

Doing testing with node_gallery using a view with distinct to display just one cover image from each gallery.
-- node_gallery uses imagecache for all images in this case --
Everything worked fine until either content_access or node_gallery access(contrib) was enabled.
That made Distinct fail, uninstall the content access module and it worked fine again.
The same failure was happening with either content access module..
I don't know if this will help anyone, but I thought it might be good to make it known..

subscribing

Had the same problem with 6x-2.7 -- apparent in a wide variety of views. Admin, registered user and anonymous users all saw one iteration only. Users with roles saw one extra iteration per role. 6x-3.0Alpha1 fixed the problem, all users now see a single iteration no matter how many roles they have.

THANKS!

I upgraded to 6.x-3.0-alpha1 and it still does not work for me.

The problem is with the GROUP BY and ORDER keywords. In those location, the fields are not fully qualified. So for instance, when you group a node on nid (because of the DISTINCT) you get GROUP BY nid instead of GROUP BY node.nid.

Although 3.0 does not produce errors at length, with DISTINCT set to Yes, I get an empty list.

At this time, most of my views can use DISTINCT No so I'm not in too bad a position, but that's still annoying.

Thank you.
Alexis

--- EDIT ---

Nevermind, I actually do get an error when DISTINCT is set to Yes. The same as with 2.7

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column reference "nid" is ambiguous LINE 11: GROUP BY nid ^ in /usr/clients/www/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid, FIRST(node.title) AS node_title, FIRST(GREATEST(node.changed, node_comment_statistics.last_comment_timestamp)) AS node_comment_statistics_last_updated, FIRST(node_revisions.teaser) AS node_revisions_teaser, FIRST(node_revisions.format) AS node_revisions_format, FIRST(node.created) AS node_created FROM node node INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.type in ('scam')) AND (node.status <> 0) GROUP BY nid ORDER BY node_created DESC ) count_alias in /usr/clients/www/drupal/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1058.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column reference "nid" is ambiguous LINE 11: GROUP BY nid ^ in /usr/clients/www/drupal/includes/database.pgsql.inc on line 139.
    * user warning: query: SELECT DISTINCT(node.nid) AS nid, FIRST(node.title) AS node_title, FIRST(GREATEST(node.changed, node_comment_statistics.last_comment_timestamp)) AS node_comment_statistics_last_updated, FIRST(node_revisions.teaser) AS node_revisions_teaser, FIRST(node_revisions.format) AS node_revisions_format, FIRST(node.created) AS node_created FROM node node INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.type in ('scam')) AND (node.status <> 0) GROUP BY nid ORDER BY node_created DESC LIMIT 10 OFFSET 0 in /usr/clients/www/drupal/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1084.

The issue with the GROUP BY keywords not being fully qualified is a different issue.

ORDER BY keywords do not need to be fully qualified.

same here... but, in addition, i've a view that's visible from only 3 different roles... admins see distinct results, the other 2 not! :-(

Merlin of Chaos,

Sorry for the noise... Are you referencing this node: #622602: 2.6 to 2.7: Queries being created without ORDER BY ?

Thank you.
Alexis Wilke

Upgrading to 2.8 solved the problem for me.

Regards.

sorry, but no current version will work for me! Version 2.8 and the current 2.x-dev may result with "warning: pg_query() [function.pg-query]: Query failed: ERROR: column reference "nid" is ambiguous LINE 17: GROUP BY nid ^ in /data/edarling/drupal-6.14/includes/database.pgsql.inc on line 139."

what to do?

@derMaggus: Your issue is not related to this one. Please check this issue #607418: Column 'nid' in field list is ambiguous query and provide there more information if you can.

@dagmar

thx but my problem comes only with die GROUP BY which will be added if the distinct is set.

dagmar,

That's my problem too. Thus #607418: Column 'nid' in field list is ambiguous query is probably the same issue. 8-)

I get the same error as derMaggus.

Thank you.
Alexis

Any chance to solve this problem with a quick fix or something? This is boring ... is there any other chance to get this working instead of downgrading to version 2.6?

susbcribe

*subscribing*

Forced downgrade to 2.6

Do I have to do any DB updates upon downgrading?

Does upgrading to 6.x-2.8 fix this issue?

you have to use
1) the latest stable views version
2) the core patch / the latest core dev version.

Dereine - According to some other posts here, the 6x-3.0Alpha1 version of Views fixes it.... but at the time this was happening I don't know if 6.x-2.8 had been released yet so thats why I'm asking. Don't the fixes in the 3.x branch get back-ported to 2.x?

And are you talking about using the latest dev of Core Drupal?! I don't recall reading anything about that in this thread. I don't feel comfortable doing that on a production site... and I'm not entirely sure its even necessary.

This is a pretty serious problem - my users are deleting entire nodes in an effort to "get rid of the duplicates" they are seeing.

Reiterating:

This is a Drupal core issue. The patch in http://drupal.org/node/284392 fixes the issue. It has been committed to Drupal 6 in CVS but a release has not yet been rolled. You can apply the latest patch, grab Drupal 6.x-dev or wait.

This fix has been available for several months. I have no control over when core patches get committed or deployed.

Awesome - thanks for clearing that up, my options are clear now.