Workflow Access breaks Views that utilize the "distinct" basic setting

I am changing the referring version, as there is a new version of the project module.
Does anybody have the same issue on 6.x-1.1?

I'm having the same problem.

Although it seems to occur with or without the distinct settings in view.

    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 28800' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 28800 SECOND), '%Y%m%d%H%i') AS node_created_minute FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner'))) AND ( (node.type in ('feeditems')) AND (node.status <> 0) )ORDER BY node_created_minute DESC ) count_alias in /home/s1014118/public_html/drplgc/sites/all/modules/views/includes/view.inc on line 699.
    * user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 28800' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 28800 SECOND), '%Y%m%d%H%i') AS node_created_minute FROM node node INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner'))) AND ( (node.type in ('feeditems')) AND (node.status <> 0) )ORDER BY node_created_minute DESC LIMIT 0, 20 in /home/s1014118/public_html/drplgc/sites/all/modules/views/includes/view.inc on line 725.

I'm using:
Drupal 6.9
workflow 6.x-1.1
views 6.x-2.2
cck 6.x-2.x-dev
a bunch of others

When I enable the workflow module, I have no problems.

When I also enable the workflow access module, I have no problems.

But...then I get a message that says:
The content access permissions need to be rebuilt. Please visit this page.

After I rebuild the content access permissions, I have a problem (the error above).

I tested my view with and without the distinct setting, and it seemed to break both times.

Then I disable workflow access. I get a message that says "Content permissions have been rebuilt" and the views work fine again.

I'm excited about this module - it looks really great!

As per PeterZ I'm having exactly the same problem with the same modules. The problem appears to be that the Workflow module is inserting an additional DISTINCT(node.nid) at the start of the query. Looking at the sql generated by Views it looks like this:

SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, ...

with Workflow enabled the sql now looks like this:

SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.title AS node_title, ...

If I remove the second "AS DISTINCT..." the query executes correctly.

I hope this helps.

As an aside I was a bit surprised\concerned to see that the Workflow module also amends the where clause as well to filter specifically on Workflow settings. I'm sure this is fine in some cases but like most people I suspect the workflow is being used to move the node through various additional states between submitted and published. Personally I'd like to retain control over my views using the filter mechanism within the views whereby I can filter on what I choose.

It seems the module code simply replace nid with SELECT DISTINCT(node.nid), in the SQL query.

I Think I have the same Problem...

This error is on allmost every of my pages(user config pages and views):

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), node_data_field_vorschau_bild.field_vorschau_bild_fid AS ' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_vorschau_bild.field_vorschau_bild_fid AS node_data_field_vorschau_bild_field_vorschau_bild_fid, node_data_field_vorschau_bild.field_vorschau_bild_list AS node_data_field_vorschau_bild_field_vorschau_bild_list, node_data_field_vorschau_bild.field_vorschau_bild_data AS node_data_field_vorschau_bild_field_vorschau_bild_data, node_data_field_vorschau_bild.nid AS node_data_field_vorschau_bild_nid, node.type AS node_type, node.title AS node_title, votingapi_cache_node_vote_average.value AS votingapi_cache_node_vote_average_value, node.status AS node_status FROM node node LEFT JOIN votingapi_cache votingapi_cache_node_vote_average ON node.nid = votingapi_cache_node_vote_average.content_id AND (votingapi_cache_node_vote_average.content_type = 'node' AND votingapi_cache_node_vote_average.tag = 'vote' AND votingapi_cache_node_vote_average.function = 'average') LEFT JOIN votingapi_vote votingapi_vote_node_vote ON node.nid = votingapi_vote_node_vote.content_id AND (votingapi_vote_node_vote.content_type = 'node' AND votingapi_vote_node_vote.tag = 'vote') LEFT JOIN content_field_vorschau_bild node_data_field_vorschau_bild ON node.vid = node_data_field_vorschau_bild.vid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'workflow_access') OR (na.gid = 3 AND na.realm = 'workflow_access') OR (na.gid = 18 AND na.realm = 'workflow_access_owner'))) AND ( (node.status <> 0) AND (node.promote <> 0) )ORDER BY votingapi_cache_node_vote_average_value DESC, node_status ASC LIMIT 0, 5 in /home/hwg/html/news/modules/views/includes/view.inc on line 731.

I have the same problems.

When Workflow access is activated AND I do the following :"The content access permissions need to be rebuilt. Please visit this page."

It only affects the node order on the frontpage and only if I'm logged in as user1.
For everyone else the frontpage is normal.

Drupal 6.9
MySQL-database 4.1.21
PHP 5.2.4

I'm had the same problem.

I'm using views with profile on users:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-22 ON users.uid = profile_values_profile_factfind-22.uid AND profile_values_pro' at line 4 query: SELECT users.uid AS uid, users.name AS users_name FROM users users LEFT JOIN profile_values profile_values_profile_factfind-22 ON users.uid = profile_values_profile_factfind-22.uid AND profile_values_profile_factfind-22.fid = '22' WHERE profile_values_profile_factfind-22.value in ('Strongly Agree') LIMIT 0, 10 in /home/rnw/public_html/client/sites/all/modules/views/includes/view.inc on line 731.

and the Query:

SELECT users.uid AS uid,
   users.name AS users_name
 FROM users users 
 LEFT JOIN profile_values profile_values_profile_factfind-22 ON users.uid = profile_values_profile_factfind-22.uid AND profile_values_profile_factfind-22.fid = '22'
 WHERE profile_values_profile_factfind-22.value in ('Strongly Agree')

I fix it by not using '-' just '_'

Was getting same problem with queries being generated that began like this:

  SELECT DISTINCT(node.nid) AS DISTINCT(node.nid) . . . 

After turning off the workflow access module, the error went away and the view behaves as expected.

I'm getting the same problem too on Views 6.x-2.3, but I'm not using Workflow. I get the following warning:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT(node.nid), node.created AS node_created FROM node node WHERE (nod' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node.created AS node_created FROM node node WHERE (node.promote <> 0) AND (node.status <> 0) AND (node.type in ('news')) ORDER BY node_created DESC LIMIT 0, 10 in /home/ucandoit/public_html/sites/all/modules/views/includes/view.inc on line 731.

It seems to happen in the Views preview as soon as I turn on the "distinct" option.

Edit: if a broken View is displayed in a block, then that block seems to lose its title.

I'm also running into this problem. See #426800: "Workflow: Previous time" views field results in duplicate nodes (D6) for what's causing it for me.

Same here, keep getting duplicates and the "Distinct" setting in Views just dissappears the posts...pretty frustrating.
Using -
Workflow: 6.x-1.1
Workflow fields: 6.x-1.1
Views: 6.x-2.4

Same Problem here, every View with distinct=yes is broken!

D6.10
Views 6.x-2.5
Workflow 6.x-1.1

Hey guys, seriously! this _critical_ issue exists at least since January. but no one seems to care about it!

I discovered that this issue has not necessarily something to do with Views.
I have some other SQL-Queries in my site that are rewritten too.

For example:

$sql = "SELECT tn.nid FROM {term_node} tn WHERE tn.tid = ".$term." ORDER BY tn.nid DESC";
or
$sqlcount = "SELECT COUNT(tn.nid) FROM {term_node} tn WHERE tn.tid = ".$term;

and then queried with db_query(db_rewrite_sql($sql))

then they are rewritten to:

SELECT t DISTINCT(n.nid) FROM term_node tn WHERE tn.tid = 9 ORDER BY tn.nid DESC
and
SELECT COUNT(t DISTINCT(n.nid)) FROM term_node tn WHERE tn.tid = 9

and the SQL-error occurs!

so the problem occurs either in db_query() or db_rewrite_sql() .. so either they are hooks and
workflow has hooked in to alter the query or the problem is generated in drupal itself.

someone else who did some research on this issue??

+1

Also having this issue. Looks related to/the same as this issue with User Relationships:

http://drupal.org/node/300703

Much more comprehensive treatment of this issue can be found here:

http://drupal.org/node/284392

Looks like it is actually a bug with db_rewrite_sql and can be reproduced with various node access modules conflicting with views where distinct is used to filter results.

Current state of the bug seems to be that there's a patch which addresses the issue but is not thoroughly vetted.

If you are running into this, please go test the patch at #284392: db_rewrite_sql causing issues with DISTINCT (the patch is in comment #24). It is confirmed working with everything except workflow access and we need some confirmations that that patch fixes this module.

@KarenS

I will test the patch probably next week with the workflow module, but it is very important for my
drupal installations that the cores remains vanilla.
thus it would very important to see this patch in one of the next D6 releases since this issue turned
out as a real bug. Is it conceivable that this may happen in the near future?

thanks in advance for your information.

side note, i've seen this same problem now pop up when using the book access module and the views 'distinct' flag.

see also: http://drupal.org/project/issues/search/views?issue_tags=duplicate%20vie...

#24 patch works for me

The issue of views breaking if distinct is set to yes comes up in several places (domain access is another). There is an alternative fix here (that uses a module rather than changing code):
http://www.lullabot.com/blog/views-distinct-node-access-problems

Yes. The solution is rather easy
1) update to views 2.8
2) apply http://drupal.org/node/284392 the patch at comment #397