Tosses SQL errors for unregistered users

tazzytazzy,

Can you tell me a little bit more about your configuration? I see what is wrong with the SQL, but need a little assistance in working out what to do about it. The following portion of the SQL is not part of what "I wrote" in the module - some other bit of the magic within Drupal is adding this in for your site:

INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0'))

That portion of the code is expecting to be inserted into a query which contains a node table record named "n", but my query does not have a node in it (nor does it need one to count up the purchased items once it has the internal item identifier).

My newness to Drupal module development will shine through here - I don't know what exactly is adding that in and why I don't see it on my site for users who are not logged in.

What modules do you have configured that may be inserting the SQL that relates to the node_privacy_byrole?

Scott

I am getting the same issue. It not only does it once but numerous times depending on how many items are showing on the page. This appears when logged out.

Same error meesage as stated previously:

user error: Unknown table 'n' in on clause
query: SELECT SUM(wishlist_purch_quantity) as quantity_purchased FROM wishlist_purchased  INNER JOIN node_access na ON na.nid = n.nid WHERE  (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','og_uid0','og_group0')) AND  wishlist_purch_nid = 149 in /home/www/VooDooStevie/includes/database.mysql.inc on line 66.

Stats:
Ubuntu Linux (Breezy Release)
Apache2
Drupal Version 4.6.3
PHP Version 4.4.0-3
MySQL API Version: 4.0.24

What other info do you need to know?

VooDooStevie,

Are you running the node privacy by role module? If so, can you disable that module and see what happens then? What I believe is happening is that the node privacy by role module is inserting some additional SQL on the rewrite call. This SQL assumes that there is a table of type 'NODE' called 'n' already in the SQL statement. The errors that are being tossed out are from the code that retrieves the 'what was purchased' records. I retrieve these records by matching on the Node ID, but not through a join that includes the node record.

I have two paths I see to fix this.

The approach that I'd prefer is to find a way to instruct the node privacy by role module to NOT include its code on the two rewrite calls where I'm not taking a view of the database that expicitly includes a node. In a late night cursory run through the privacy by node module I didn't see any obvious way to do this. I would welcome assistance here.

The second method is to rework those two SQL statements to join to the node table. The code in the wishlist module does not need this and I presume it adds a performance penalty that I've not quantified. In the bigger picture this supporting table does not require permissioning as it is a helper table that simply keeps track of how many of an item a given user took off the list. The permission checking really belongs (and is on) the retrieval of rows for the wishlist table, which logically extends the node table.

On a side note, I just started a new job today after an 11.5 year stint at another company and apologize on not being able to quickly address issues in the wishlist module.

Scott

The problem was in my use of db_rewrite_sql. The drupaldocs did not explain (to my reading) the significance of the $primary_table and $primary_field arguments, which default to "n" and "nid" respectivly. When a user does not have "administer nodes" rights the _node_access_where_sql() method in node.module spits out SQL fragments which look up access grants in the node_access table. This function is only called when the primary_field in the db_rewrite_sql call is set to "nid" - which it is by default. Because I did not fill in the $primary_table and $primary_field arguments on the db_rewrite call the SQL fragment that checks node_access grants was added to my SQL. But my SQL didn't have a table called n with a field called nid, so the call failed.

This will only happen when your wishlist is viwed by a user context which does not have "administer nodes" set, which is bound to be all of your anonymous users and most of your authenticated users.

The fix is in CVS Head. I'll get it into the DRUPAL-4-6 branch shortly. I guess it will take 24 hours for it to appear on the module download page. You want wishlist.module version 1.2 or greater.

That node privacy by role module was actually disabled before I got these errors. So we can rule that issue out. Heh. Well I hope to see the update soon. And delay? Dude in the past I waited WEEKS before a developer would respond to issues posted on their project. (*cough* Postnuke Community *cough*)

I think the Drupal community is faster in responding to end user's than most of the other CMS development communities. Again thanks for the follow-up and will get the update soon.

dorry for the previous duplicate issue, but I'm running into the same problem. I definitely have users that I'd rather not have admin access. Is there a patch for this yet?

tazzytazzy and bigbman,

I'm not seeing the problem with the patched version. Tazzytazzy went through some paranioa double checks for me (appreciated) to make sure the correct version was indeed installed. I've gone between the patched version and the original broken version in my development environment and I can reliably make the problem occurr with the old version and reliably make it go away with the new one.

The wishlist module download page has not updated based on CVS and is still dispensing the version of the wishlist module with the conflict to the node_privacy_byrole module. You can download the correct version from here. You can also disable the node_privacy_byrole module, but that's not an optimal solution in my mind.

Please double check that you do indeed have the latest version. There were two lines that were fixed. One is in

function _wishlist_get_node_quantity_purchased($nid)

The line of code changed within this function is

   $result = db_query(db_rewrite_sql("SELECT SUM(wishlist_purch_quantity) as quantity_purchased FROM {wishlist_purchased} WHERE wishlist_purch_nid = %d", "p", "wishlist_purch_nid"), $nid);

The change is the addition of ,"p", "wishlist_purch_nid" to the db_rewrite_sql call. This tells the code deeper within the drupal core that there is no 'node' table within this query, which in turn prevents it from adding the node_access portion of the SQL.

The second line which was fixed is in

function _wishlist_render_view($node, $teaser = FALSE)

The line of code changed within the function is

     // Retrieve all of the records showing what the currently logged in user purchaed on this item.
     $result = db_query(db_rewrite_sql("SELECT p.wishlist_purch_wid, p.wishlist_purch_buyer_uid, p.wishlist_purch_quantity FROM {wishlist_purchased} p WHERE p.wishlist_purch_buyer_uid=".$user->uid." AND p.wishlist_purch_nid=".$node->nid, "p", "wishlist_purch_buyer_nid"));

Here again the change is an addition to db_rewrite_sql. It is the exact same change described above.

Look at your wishlist.module, make sure these changes are in place. Make sure there is not an accidental copy of the older wishlist.module anywhere in your Drupal installation that may be being picked up first. Please let me know what you find. I'm open to the possibility that it is still not working, but without help demonstrating exactly what combination of modules breaks the wishlist, I'm not going to be able to do much more. I cannot fix what I cannot reproduce, and to the best of my knowledge this bug has been identified, understood, analyzed and fixed.

What remains broken is the fact that my changes have not been propogated to the module downloads page.

Scott