SQL errors on group view page for non-superusers

I have two groups created by root:

- Group #1: Invite-only
- Group #2: Open

Each has only one subscriber; root.

My admin >> settings >> og is set to posts only being visible within targeted groups (default).

The authenticated user role only has the default permissions, no create group but should have access content at least.

I don't think this will show up in 4.7/HEAD because node access queries are done completely differently there.

Will step-through with a debugger in an hour or so to try and narrow this down.

jyang: Ok. then please file a different issue about that error, since it is not the same.

This error is coming in @ line 647 in og_list_groups_page:

$result = pager_query(db_rewrite_sql("SELECT n.nid, n.title, n.body, n.uid, u.name, og.description FROM {og} og INNER JOIN {node} n ON og.nid = n.nid INNER JOIN {users} u ON n.uid = u.uid WHERE og.directory=1 AND n.status=1 ORDER BY n.nid DESC"), 50);

The original query that gets passed in executes okay. But the final query that pager_query executes is:

SELECT COUNT(*) FROM {og} og  
INNER JOIN {node_access} na ON na.nid = n.nid 
INNER JOIN {node} n ON og.nid = n.nid 
INNER JOIN {users} u ON n.uid = u.uid 
WHERE  (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','og_public0','og_all0'))
AND  og.directory=1 AND n.status=1

(I've thrown line breaks in here so it's easier to read)

Notice that node_access is trying to do a join on n.nid before the node table has been added. If you swap lines 2 and 3 of that query, it executes fine.

The reason this query doesn't fail for the 'root' user is because it skips the node_access check altogether, and only passes the following into pager_query():

SELECT COUNT(*) FROM {og} og INNER JOIN {node} n ON og.nid = n.nid INNER JOIN {users} u ON n.uid = u.uid WHERE og.directory=1 AND n.status=1

Any ideas how I can fix the order of the table joins in OG's rewrite query?

Thanks to merlinofchaos (and chx), here is a simple patch to fix this! It simply reverses the order of the og and node tables in the pager query.

The reason this is needed is (paraphrasing) because db_rewrite_sql stuff is only programmed to react when the primary table is node; here it was set to 'og' instead.

Yep, this issue is for the 4.6 version of OG module, that's why it's better to put your issue in a separate bug report. :) Thanks for doing that. I'll try and take a look.

Committed to 4.6 branch, thanks!

I did try the same fix against 4.7/HEAD (since its pager query is still done with the og table in front of node) and it ended up breaking the page horribly. Apparently this has to do with HEAD issuing node_access queries based on a different sort order than 4.6 does. For instance, 4.6 doesn't seem to suffer from the statistics module bug that the 4.7 version does. Weirdness. :P

Also, just so this is noted here, apparently this is only an issue that's cropped up in MySQL 5 and the way it does joins. But it doesn't hurt anything in previous versions.