SQL error in album view when using node_access modules

I saw this for a while. Then I didn't. Then I couldn't get rid of it without modifying either the database backend or the views module. Now I am not seeing it anymore.

The weird thing that I couldn't figure out was that this was database specific. Meaning either the structure or something in the database was triggering this. Using the same code base, an upgraded site from 4.7 to 5.0 showed this error, yet a new 5.0 site did not. But I have recently TRIED to recreate this, but I cannot.

Make sure you are using the latest version of all the modules. Try doing a new intstal rather than an upgrade or vice versa and come back and report.

Very very interesting... I tried two routes. For both, it included the latest (as of 3 days ago) version of 5.0 compatible Image, Video, Views, Acidfree (and for the upgrade, Filemanager). And I had the following modules enabled:

• Comment
• Help
• Menu
• Taxonomy
• Image
• Acidfree
• Video
• Video ffmpeg Helper
• Video Image
• Video Upload
• Views
• Views UI

That is really a small number of modules by Drupal standards :). I did the install and did some mass imports to populate the albums. For the upgrade path I did the same thing and then did the upgrade. And then for good measure, I added some more after the upgrade.

Maybe this has some modules interaction. Try disabling all the non-essential-to-Acidfree modules and let me know what happens.

I think i have the same error trying to access the Acidfree album. I use the msqli driver but it looks pretty similar:

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) AS node_nid FROM node node LEFT JOIN term_node term_node ON ' at line 1 query: SELECT DISTINCT(node.nid), node.sticky AS node_sticky, DISTINCT(node.nid) AS node_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('8') LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid WHERE (term_node.tid IS NOT NULL) AND (term_data.tid IS NOT NULL) AND (node.status = '1') AND (term_node2.tid = '15') GROUP BY node.nid ORDER BY node_sticky DESC, node_nid DESC LIMIT 0, 15 in /usr/local/www/apache22/data/drupal/includes/database.mysqli.inc on line 151.

I recently installed the "Taxonomy Access Control". I'm current researching what modifications the module does upon install.

I think you may have just found the reason I have seen this sometimes but not recently. I had been testing the upgrade script with a replica of one of my live sites. It had a node_access module installed and created these errors. The new install testing I had done did not have a node_access module. It is all so clear now.

I will continue to look into this. I thought that this problem was supposed to have been fixed in the Views module (that is what creates that horrendous query). But obviously not.

This one may take some time to sort out, since it is not my code we are dealing with.

Same problem here... and I use organic groups! with the access control.
Will try to investigate.

I posted a follow up here : http://drupal.org/node/126617

I'm having similar issues:

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) AS node_nid FROM node node LEFT JOIN term_node term_node ON ' at line 1 query: SELECT DISTINCT(node.nid), node.sticky AS node_sticky, DISTINCT(node.nid) AS node_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('16') WHERE (term_node.tid IS NOT NULL) AND (term_data.tid IS NOT NULL) AND (node.status = '1') GROUP BY node.nid ORDER BY node_sticky DESC, node_nid DESC LIMIT 0, 40 in /home/jcoffee/public_html/includes/database.mysql.inc on line 172.

I have taxonomy_access enabled, but i think the problem may have something to do with something left over from 4.7 when i had node_access enabled for a (very) brief period. my 4.7 site occasionally gets the following:

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 'INNER JOIN node_access na ON na.nid = blog/mikemiller/2007/01/03/.nid WHERE (na' at line 1 query: INNER JOIN node_access na ON na.nid = blog/mikemiller/2007/01/03/.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'term_access'))) in /home/jcoffee/public_html/includes/database.mysql.inc on line 121.

there seems to be issues there with taxonomy_ and node_access working together.

i will try to straighten out the node_access issue, and then retest with a fresh install.

I managed to reproduce this bug very easily, from a fresh install of drupal 5.1
Don't activate any core modules other than the few modules activated by default: color, comment, help, menu, taxonomy.

install the following modules in this order: image, views (5.x-1.x-dev), views_rss and acidfree (cvs March 12, 2007 - 12:00).
Create an acidfree album, say album1. Create an image in this album. You can display album1 containing the image without any problem.
Install og 5.x-2.2, (create the group content type, assign it as a Group home page node type in the og admin page, create a group called group1).
Don't activate the og access control.
Check that you can still access the album without any problem.
Look at the node_access table, this is what I have :

nid gid realm grant_view grant_update grant_delete
0 0 all 1 0 0
4 0 all 1 0 0

Notice the nid=0 granting public access to all the nodes.

Activate the og access control, and check your album, you should now see the error.
The noce_access table became:
nid gid realm grant_view grant_update grant_delete
1 0 all 1 0 0
2 0 all 1 0 0
3 0 all 1 0 0
4 0 all 1 0 0

Note that here there is no record nid=0 since the access is granted on a per node basis.
If you manually insert a record in the node_access table for nid=0 gid=0 realm=all etc, the SQL error disappears, (but you also lose your access control).

It looks like acidfree is not compatible with any access control module (organic groups, taxonomy access...) which all have to delete this record nid=0. Is this record required by acidfree?
I think this has to do with the view acidfree uses through the views module.

I don't know if this will help, but I narrowed down the differences between a working acidfree config and a buggy one (the only difference being the nid=0 in the node_access table).
I extracted the 2 SQL queries and compared them.
They are basically the same, except that the buggy one contains an extra DISTINCT.

Here is the query that works:

SELECT  DISTINCT ( node.nid ), node.sticky AS node_sticky, node.nid AS node_nid
FROM node node
LEFT  JOIN term_node term_node ON node.nid = term_node.nid
LEFT  JOIN term_data term_data ON term_node.tid = term_data.tid AND term_data.vid
IN (  '1' )
LEFT  JOIN term_node term_node2 ON node.nid = term_node2.nid
LEFT  JOIN term_hierarchy term_hierarchy ON term_node2.tid = term_hierarchy.tid
WHERE ( term_node.tid IS  NOT  NULL  ) AND ( term_data.tid IS  NOT  NULL  ) AND ( node.status =  '1' ) AND ( term_node2.tid =  '2' )
GROUP  BY node.nid
ORDER  BY node_sticky DESC , node_nid DESC
LIMIT 0 , 15

The query that doesn't work is the same, except for the first line:

SELECT  DISTINCT ( node.nid ), node.sticky AS node_sticky,  DISTINCT ( node.nid ) AS node_nid

The second DISTINCT is the reason of this bug.

I managed to fix the problem by overriding the default acidfree views:
1. Go to the Administer views page /admin/build/views
2. Locate the album_grid_view views in the default views list. Click on add.
3. Scroll down to the Filters section and delete the "Node: Distinct" filter
4. Scroll down to the next section, Exposed filters, and delete the "Node: Distinct" filter
5. Save, and check that the album_grid_view status of the default views is Overridden

You should now be able to display the albums without the sql error. Do the same for the other view : album_list_view
In my case, the difference I can see in the new resulting sql query is the second DISTINCT which is not there anymore, and the GROUP BY node.nid of the end that also disappears.

I don't know whether the issue with the second DISTINCT is due to the views module or the acidfree views.
Let me know if this works for you guys.

vhmauery, there is a views issue http://drupal.org/node/101275 that deals with this problem. It's meant to be fixed though. Would it need to be reopen ?

Sorry, this is my fault. Apparently I can't type; I checked in a broken test and never noticed it wasn't working. This will be fixed (really) in Views 1.6.

great, thank you. I'll check it out as soon as it's released ;)

I have checked out the views sources from CVS with the tag 'DRUPAL-5'. I still get the error.

$Id: views_query.inc,v 1.51.2.7 2007/03/17 16:21:02 merlinofchaos Exp $

This version contains the 'fix' but it does not fix the issue for me.

I still get this error:

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) AS node_nid FROM node node LEFT JOIN term_node term_node ON ' at line 1 query: SELECT DISTINCT(node.nid), node.sticky AS node_sticky, DISTINCT(node.nid) AS node_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('8') LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_hierarchy term_hierarchy ON term_node2.tid = term_hierarchy.tid WHERE (term_node.tid IS NOT NULL) AND (term_data.tid IS NOT NULL) AND (term_node2.tid = '16') GROUP BY node.nid ORDER BY node_sticky DESC, node_nid DESC LIMIT 0, 15 in /usr/local/www/apache22/data/drupal/includes/database.mysqli.inc on line 151.

Can someone else confirm this or is it just me being stupid ? :/

Yes you are right, it's still not fixed. Let's wait til the views 1.6 is released and see. In the meantime, you can try this fix.

Continuation on the views module issue queue.

This issue has been resolved on the Views issue queue.