SELECT clause needs actual field for db_rewrite_sql() to work correctly.

When I change the db_query_range(db_rewrite_sql()) to a normal db_query() it works like it should.

The original query is:

SELECT n.*, cs.*
FROM {node} n
INNER JOIN {casetracker_case} cs ON (n.vid = cs.vid)
WHERE n.status = 1
ORDER BY n.created DESC

This is altered by db_rewrite_sql() to:

SELECT n.*, cs.*
FROM {node} n
INNER JOIN {casetracker_case} cs ON (n.vid = cs.vid)
INNER JOIN {node_access} na ON na.nid = n.nid
WHERE
(na.grant_view >= 1
  AND ((na.gid = 0 AND na.realm = 'all') 
       OR (na.gid = 0 AND na.realm = 'og_public')
	   OR (na.gid = 1 AND na.realm = 'og_subscriber')))
AND ( n.status = 1 )
ORDER BY n.created DESC

This second query gives the dupes. So the problem might be with the hook_db_rewrite_sql() in og.module or something. I'll try to look into this later.

OG inserts the following in node_access when creating a node:

+-----+-----+---------------+------------+--------------+--------------+
| nid | gid | realm         | grant_view | grant_update | grant_delete |
+-----+-----+---------------+------------+--------------+--------------+
|  10 |   0 | og_public     |          1 |            0 |            0 |
|  10 |   1 | og_subscriber |          1 |            1 |            1 |
+-----+-----+---------------+------------+--------------+--------------+

Then node_db_rewrite_sql() inserts a join from node on node_access resulting in dubbel rows. I'm not sure how to resolve this though.

Changing project. Didn't read the warning the first time.

And fixed the typo in the title.

It's og_node_grants() that feeds the where's to the db_rewrite_sql(). What the query tries to achieve is determining if the user has access to that node. But when your access rights match several rows in node_access your original query will start to return incorrect results because of the join. The only way to write the query in a semantically correct way is to use a subquery.

So the correct query that should come out of db_rewrite_sql is:

SELECT n.*, cs.*
FROM node n
INNER JOIN casetracker_case cs ON (n.vid = cs.vid)
WHERE
n.nid IN (SELECT nid FROM node_access na WHERE
(na.grant_view >= 1
  AND ((na.gid = 0 AND na.realm = 'all')
       OR (na.gid = 0 AND na.realm = 'og_public')
   OR (na.gid = 1 AND na.realm = 'og_subscriber'))))
AND ( n.status = 1 )
ORDER BY n.created DESC

There is the concern that not all versions of MySQL support subqueries. Don't know about the other platforms though.

Drupal uses DISTINCT to fix the multiple rows issue with node_access. But the db_distinct_field() functions expects the exact field that is passed to db_rewrite_sql() to be present in the SELECT clause. This is not the case with the query in the casetracker module. So in stead of tampering with core and changing everything I've attached a patch that just adds that field to the query so that the DISTINCT is inserted correctly in the query.

After adding n.nid to the select clause the query that is returned by db_rewrite_sql() is:

SELECT  DISTINCT(n.nid), n.*, cs.*
FROM {node} n
INNER JOIN {casetracker_case} cs ON (n.vid = cs.vid)
INNER JOIN {node_access} na ON na.nid = n.nid
WHERE (na.grant_view >= 1
       AND ((na.gid = 0 AND na.realm = 'all')
	        OR (na.gid = 0 AND na.realm = 'og_public')
			OR (na.gid = 1 AND na.realm = 'og_subscriber')))
AND ( n.status = 1 )
ORDER BY n.created DESC