Error getting statistics on project issues

the query:
$result = db_query(db_rewrite_sql("SELECT pn.nid, pn.title, p.sid, COUNT(n.nid) AS total FROM {node} n INNER JOIN {project_issues} p ON n.nid = p.nid INNER JOIN {node} pn ON p.pid = pn.nid WHERE n.status = 1 AND pn.status = 1 GROUP BY pn.nid, p.sid, pn.title"));

now specifically note this function: http://api.drupal.org/api/function/hook_db_rewrite_sql/5

now compare with your query. yours has a DISTINCT clause. project_db_rewrite_sql() doesn't ever use a DISTINCT clause, so it's not rewriting the query that way. you have some other module that's fouling up the rewrite of the query -- this is most likely not a project issue module problem. start disabling modules and trying to load that page, and when you find the module that's causing the issue, please move this bug report to that module.

Actually, I think I found the problem. It is on line 1930 in issue.inc and it does indeed have DISTINCT in the db_rewrite_sql query:

And so does the next query - although I haven't had any error on this yet.


'count' => db_rewrite_sql('SELECT COUNT(DISTINCT(n.nid)) FROM {node} n INNER JOIN {project_issues} p ON p.nid = n.nid INNER JOIN {node_revisions} r ON r.vid = n.vid INNER JOIN {users} u ON p.assigned = u.uid LEFT JOIN {project_comments} c ON c.nid = p.nid WHERE n.status = 1 AND ('. implode(') AND (', $sql) .')')
);


Is it safe to remove the DISTINCT keyword from the first query - do you think that will solve the problem?

thanks, dantina.

Ok - I've just read over my last post and I can see it's not the same query. I think I just got excited that I've finally found the source of the error :)

One thing though - it looks like there are times when the DISTINCT clause can be used 'safely' with db_rewrite_sql. I've now got only a few modules enabled: project, project issue, og, events, cck, auto nodetitles & token modules and I suppose it shouldn't be too difficult to find the source. For now, I've removed the statistics menu items from the project issue pages until I find the source of the error.

I ran into the same error and was able to remediate the problem by uninstalling the content_access module and running node_access_rebuild();. I have no idea what the issue here is though, which is a little unnerving.

reassigning.

hunmonk, content_access does nothing but setting node access by using drupal's node access API. If one module uses it, the queries got rewritten through db_rewrite_sql - otherwise not.

It seems that the problem occur only if a node access module is active.
So this is either a bug of "Project issue tracking" or drupal's node access sql-rewrite (http://api.drupal.org/api/function/node_db_rewrite_sql).

Yes, this is definitely a project bug. See http://drupal.org/node/191974 (now marked duplicate with this), in particular, comment #15 for steps to reproduce.

with the pending views conversion, this isn't worth the manpower.

erm. excuse me. i'll leave this open, but we need somebody else to come in and get a patch going here...

I don't see why this needs more info. We know how to reproduce it. We just need someone to write the patch, hence, "Unassigned". ;)

I stumbled across this problem earlier today, but didn't find this issue until Derek advised me that the one I did submit (216474) was a duplicate. (Sorry, I do really try to check first, but have been caught twice now in the project_issue queue alone.)

Anyway, I rooted around, and I'm pretty convinced that the query shown in #1 above is the culprit. I haven't researched db_rewrite_sql, but it looks like "SELECT pn.nid" got changed to "SELECT p DISTINCT(n.nid)" somewhere along the way, which was causing the error. I tried changing "pn" to "x" everywhere in that line and the error went away, but Derek didn't think that was a viable solution. I invite you to take a look at the patch at http://drupal.org/files/issues/project_issue-pager_query.patch anyway, if only to prove or disprove that this is the line of code causing the problem. That's probably not necessary, because I think it's been established now that I see the other issues on it.

Regarding #6 and #7 above, yes I have OG installed in my dev environment, which is where I encountered this bug. In hindsight, I probably didn't need to apply my patch when I rolled 5.x-2.0 into production today.

I've fixed this (at least for my purposes) with a quick patch, but I'm not really sure this is a project_issue bug though. It seems to me that something along the way wants to change n.nid to DISTINCT(n.nid), but since it's stepping on pn.nid, it really should be changing " n.nid" to " DISTINCT(n.nid)". Yes, that's oversimplifying, too, because it assumes every developer remembered to put a space in the right place. The parsing would really need to be more explicit than that, but you get the idea of what I'm trying to say. It means that my patch treats the symptom and not the cause, which probably isn't the best fix.

#234136: Statistics - db error was marked as a duplicate of this bug.

To make it short: Whats now the solution for us? Thats an annoying error...

@softtouch: If the error is so annoying to you, then help us fix it :)

AFAIK, none of the project* developers use other node access control modules (such as node access, OG, etc.) on sites where we also run project*, so we don't routinely run into this bug and are therefore not as motivated to spend the time to figure out what exactly is causing this bug.

If you start reading at http://drupal.org/node/191974#comment-641355, dww has posted steps to reproduce the bug, and further down in that issue there are some guesses as to what is causing the bug. If you would like to look into the problem here and, if there is a bug in project_db_rewrite_sql(), submit a patch, we'd be more than happy to review it.

I have no idea why it happen. I do not have node access module installed, but I have OG.
I had a 2nd site, also with OG, and did not get that error, so I am clueless.
And, I am not a php programmer... so I cant help, even I would like to.
I will just remove the statistics link for now. better one less function then errors.

FYI: OG *is* a node access module.

Ok, this is definitely due to a core bug. See #206224-3: db_distinct_field mangles some queries for a core patch that fixes the problem.

The only question left is do we want to patch project_issue to work around this core bug? We could easily do what is mentioned above in comment #11, and that should fix the problem. But I think it would be best for others to test the core patch and post their results there, and hopefully this will be committed to core.

I'd rather not hack around core bugs if they can be fixed in core. Thanks for tracking all this down. If there's any trouble in the other issue, I'll try to come to its aid. ;)

The patch at #206224: db_distinct_field mangles some queries was set from RTBC to CNR by drumm. If you have tested that patch, please respond in that issue with results if you want to see it committed.

#284639: Project Issues - Statistics: "You have an error in your SQL syntax" has been marked as a duplicate of this issue.