Node_access issue

Just using content_access... but any module that will cause _db_rewrite_sql to return a join to the node_access table should cause the issue if both sbp_nodes and sbp_attach are active.

I think just enabling the content_access module will be enough to create the issue. The important thing is to make it add a join to {node_access} twice in the $join variable in _search_by_page_do_search() function in search_by_page.module.

My MSQL version is 5.0.45

Unfortunately the new patch in 6.x-1.x-dev doesn't work for me as it doesn't return any results when it should do...

If I disable either the attachments or the node module I get results but when both enabled and I search as a non-admin user I get no results.

Investigating further, the query the new patch generates when all search_by_page modules are enabled is:

SELECT SUM(i.score * t.count) AS score 
FROM search_index i 
INNER JOIN search_total t ON i.word = t.word 
INNER JOIN sbp_path sp ON i.sid = sp.pid 
LEFT JOIN sbpa_files saf ON sp.modid = saf.fid 
LEFT JOIN node sbpa_n ON sbpa_n.nid = saf.nid AND sbpa_n.vid = saf.vid 
INNER JOIN node_access sbpa_na ON sbpa_na.nid = sbpa_n.nid 
LEFT JOIN node sbpn_n ON sbpn_n.nid = sp.modid 
INNER JOIN node_access sbpn_na ON sbpn_na.nid = sbpn_n.nid 
LEFT JOIN sbptt1959937329 sbpp ON sbpp.pid = sp.modid 
WHERE (0 OR 
( sp.from_module = 'sbp_attach' AND 1 AND (0 OR ( saf.source = 'cck' AND (1))) AND sbpa_n.status = 1 AND (sbpa_na.grant_view >= 1 AND ((sbpa_na.gid = 0 AND sbpa_na.realm = 'all') OR (sbpa_na.gid = 33 AND sbpa_na.realm = 'content_access_author') OR (sbpa_na.gid = 2 AND sbpa_na.realm = 'content_access_rid') OR (sbpa_na.gid = 3 AND sbpa_na.realm = 'content_access_rid')))) 
OR ( sp.from_module = 'sbp_nodes' AND sbpn_n.status = 1 AND (sbpn_na.grant_view >= 1 AND ((sbpn_na.gid = 0 AND sbpn_na.realm = 'all') OR (sbpn_na.gid = 33 AND sbpn_na.realm = 'content_access_author') OR (sbpn_na.gid = 2 AND sbpn_na.realm = 'content_access_rid') OR (sbpn_na.gid = 3 AND sbpn_na.realm = 'content_access_rid')))) 
OR ( sp.from_module = 'sbp_paths' AND sbpp.perm = 1) 
OR ( sp.from_module = 'sbp_users' AND 0)) 
AND (i.word = 'test') AND i.type = 'search_by_page' 
GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 0, 1

I think the issue is with the inner joins on node_access... if I change these to left joins the queries return the same result as for my sub-select patch.

If I remove the part of the query for the nodes or the files I get a result so the issue is not with the node access records - also, as stated above if I disable either nodes or attachments I get a result - so almost certainly not a node_access issue.

Unfortunately changing the node_access join to a left join is not right as this would then give access to nodes the user is not supposed to have. As far as I know, the only way to solve this issue is with the badly-performing sub-selects or with a dreaded union select. The latter solution would require a re-write of do_search and therefore probably is not practical.

Any ideas?

I'm confused too... :)

Maybe this is a mysql version issue - I'm on 5.0.45... stuck on RHEL5 unfortunately.

My permissions set up is a bit different... I have public and private node types... but the node_access records would be pretty similar.

However the following tests suggest this is not a node_access issue.

If I run this select I get 0 rows... (both nodes and attachments)

 SELECT SUM( i.score * t.count ) AS score
FROM search_index i
INNER JOIN search_total t ON i.word = t.word
INNER JOIN sbp_path sp ON i.sid = sp.pid
LEFT JOIN sbpa_files saf ON sp.modid = saf.fid
LEFT JOIN node sbpa_n ON sbpa_n.nid = saf.nid
AND sbpa_n.vid = saf.vid
INNER JOIN node_access sbpa_na ON sbpa_na.nid = sbpa_n.nid
LEFT JOIN node sbpn_n ON sbpn_n.nid = sp.modid
INNER JOIN node_access sbpn_na ON sbpn_na.nid = sbpn_n.nid
WHERE (
i.word = 'week'
)
AND i.type = 'search_by_page'
GROUP BY i.type, i.sid
HAVING COUNT( * ) >=1
ORDER BY score DESC
LIMIT 0 , 1 

if I run this select... I get 1 row (attachment only)

 SELECT SUM( i.score * t.count ) AS score
FROM search_index i
INNER JOIN search_total t ON i.word = t.word
INNER JOIN sbp_path sp ON i.sid = sp.pid
LEFT JOIN sbpa_files saf ON sp.modid = saf.fid
LEFT JOIN node sbpa_n ON sbpa_n.nid = saf.nid
AND sbpa_n.vid = saf.vid
INNER JOIN node_access sbpa_na ON sbpa_na.nid = sbpa_n.nid
WHERE (
i.word = 'week'
)
AND i.type = 'search_by_page'
GROUP BY i.type, i.sid
HAVING COUNT( * ) >=1
ORDER BY score DESC
LIMIT 0 , 1 

And this returns 1 row too (attachments only)

 SELECT SUM( i.score * t.count ) AS score
FROM search_index i
INNER JOIN search_total t ON i.word = t.word
INNER JOIN sbp_path sp ON i.sid = sp.pid
LEFT JOIN sbpa_files saf ON sp.modid = saf.fid
LEFT JOIN node sbpa_n ON sbpa_n.nid = saf.nid
AND sbpa_n.vid = saf.vid
INNER JOIN node_access sbpa_na ON sbpa_na.nid = sbpa_n.nid
WHERE (
i.word = 'week'
)
AND i.type = 'search_by_page'
GROUP BY i.type, i.sid
HAVING COUNT( * ) >=1
ORDER BY score DESC
LIMIT 0 , 1 

The parentheses work great... never knew that they worked in joins...not too obvious from the MYSQL pages either... however having a quick look at MYSQL's doc we seem to have introduced a dependency on mysql versions...

In versions of MySQL prior to 5.0.1, parentheses in table_references were just omitted and all join operations were grouped to the left. In general, parentheses can be ignored in join expressions containing only inner join operations.

All's working well... however through testing I did come across another issue... :) (will raise new issue once confirmed)

I can confirm that the module respects node_access permissions, the upload permission and cck field permissions.