view query ends up with two DISTINCT clauses

I am not really sure where this error is coming from... Acidfree does work fairly well with Drupal5. I just tested a new install against CVS HEAD and it worked just fine. What else have you got going on to break things? What modules are enabled? Is this a clean install?

The HEAD version works fine for me on Drupal 5 beta 2 running on WampServer.

I just reproduced this with the drupal-5-beta1 package with CVS HEAD of views and acidfree. But with the same version of acidfree and views, a CVS HEAD version of drupal, the error is gone. So I am going to say that this is fixed in head.

This is somehow a database problem. I am not sure what the cause is yet, but if I replace the code from the broken installation with the code from the working version, it doesn't fix the problem. If I swap the databases, the problem moves with the database.

The broken installation was an upgrade from a drupal 4.7 installation. I am not sure why this would make a difference because I didn't use views with 4.7... But the clean installation seems to work just fine.

Okay. I tracked this down to db_distinct_field in database.mysql.inc. The comment says the regex will not add a second DISTINCT to the query. db_distinct_field gets called with 'node', 'nid' and the first query below. It returns the second query. This results in a sql syntax error and makes the view unusable.

The regex in db_distinct_field is beyond my humble regex knowledge, so I defer to somebody else to fix it.

Before call to db_distinct_field:

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 ('2') 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 (term_node2.tid = '10') GROUP BY node.nid ORDER BY node_sticky DESC, node_nid DESC

After call to db_distinct_field:

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 ('2') 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 (term_node2.tid = '10') GROUP BY node.nid ORDER BY node_sticky DESC, node_nid DESC

Selecting the same field two times is not supported. Of course, if you want, you can try fixing the regexp. But I am not too keen on this.

Okay. I plead insanity. I have a view that selects nodes for Acidfree listings and it is bombing out because node.nid gets added twice to the query. Then db_distinct_field adds a DISTINCT to the second node.nid which causes a syntax error.

I have attached my views just in case that might help. Basically, I roughed it out using the views ui, exported it and then modified it.

Am I doing something stupid or is this something we can fix in the views module?

Here is a patch I cooked up that defends against multiple instances of any given field. The old way only looked for an exact duplicate, this patch checks for DISTINCT and possibly a different alias. If it is only an alias issue and the old field had no alias, it just adds the alias to the field.

Way too complex.

Views already tries to defend itself against multiple fields (though perhaps it doesn't do it quite as robustly as it should) but this particular one is caused by the fact that node.nid and node.nid AS node_nid aren't the same thing. Simply checking against the primary table & field will be sufficient to fix this one.

I already have this one started, too.

Yes, I agree that the patch was a bit complex. While you responded, I was in the process of writing a retraction for my patch. I don't think it is quite right...

I agree that node.nid and node.nid AS node_nid are not the same thing. But I am not sure how to cut it down to just one, if it is not 'Drupally Legal' to have the same column listed twice. The first one must be there to ensure that IF the node.nid is made distinct, it will be in the first position. The second one is needed because of the alias used for the sorting.

I convinced myself that adding an alias to a non-aliased column was BAD (tm), so I was writing in to retract my patch. But I don't have a better solution.

Well, yes, I do, that would be to add something like:

  if (strstr($query, $field_to_select)) {
    return $query;
  }

to the top of the db_distinct_field function. Then you could use a cheaper regex without the negative look behind, not add duplicate DISTINCT wrappers and still allow for multiple aliases of the same table.

That is my $0.02.

No, the second one is not actually needed, which is why I'm simply excluding primary_table.primary_field from being added as a field. That'll start out as an automatic.

This ought to be fixed in 1.4

This problem is still around. Lots of people are seeing this. It seems that this only appears when users are using the node_access modules. See http://drupal.org/node/123481

Doh! My check to protect against this situation was broken!

Checked in a fix; will be fixed in 1.6. Really.

I'm running the new views CVS version which contains your the fix for this bug. I still get the error when using acidfree and taxonomy_access.

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

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.

Have you cleared your cache?

I have cleared all 5 cache tables, disabled caching on the site and cleared my browsercache. No change :( The error occurs when i list or view acidfree albums.

Sorry merlinofchaos but I still have the same problem as well. I did try as you said the 5.x-1.x-dev version of views,
// $Id: views_query.inc,v 1.51.2.7 2007/03/17 16:21:02 merlinofchaos Exp $
obviously the same as jeanzorg, and I cleared the 5 cache tables.

I checked the line 314 of views_query.inc and I have what is supposed to be the fix:

if ($table == $this->primary_table && $field == $this->primary_field) {

but it doesn't work.

The new condition doesn't make any difference, the buggy SQL query is exactly the same as before.
I don't know if it can help, but if I change the condition by FALSE, I get the same SQL query:
SELECT DISTINCT(node.nid), node.sticky AS node_sticky, DISTINCT(node.nid) AS node_nid FROM node...
and if I try with TRUE I get:
SELECT DISTINCT(node.nid) FROM node...

Subscribing

Ok. Yet another reason that wasn't working. This time, it's fixed. I promise. Really really fixed.

Yup, the DISTINCT error is gone. Now it is this:

user warning: Unknown column 'node_nid' in 'order clause' query: SELECT DISTINCT(node.nid), node.sticky AS node_sticky FROM v_node node LEFT JOIN v_term_node term_node ON node.nid = term_node.nid LEFT JOIN v_term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('1') LEFT JOIN v_term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN v_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 = '3') GROUP BY node.nid, node_sticky, node_nid ORDER BY node_sticky DESC, node_nid DESC LIMIT 0, 15 in /var/www/drupal-5.1/includes/database.mysql.inc on line 172.

Sorry, I forgot to add that I checked out DRUPAL-5 CVS (revision 1.166.2.30). After running the update script, and then viewing one of the albums that used to show the DISTINCT error, now it has this new error.

You may need to re-save the view that uses the nid.

Nope. This is an Acidfree view, which is provided with Acidfree in a file called view_acidfree.inc, so it is not saved to start with. I did try 'editing' the view and saving it, which resulting in the original view getting overridden, but that has the same outcome, the error shown above. I also tried truncating all the cache tables too, but that didn't seem to affect anything.

Ok, I was confused until I looked at the acidfree default views and realized they sort by nid and I'd forgotten to account for that. So that, also, is fixed. Hopefully I didn't miss anything else -- this issue is just one more "Oh and I forgot that too". Hopefully this is the last one of those!

Nope. I updated my CVS and truncated my caches and now the DISTINCT error is back.

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 nid FROM v_node node LEFT JOIN v_term_node term_node ON n' at line 1 query: SELECT DISTINCT(node.nid), node.sticky AS node_sticky, DISTINCT(node.nid) AS nid FROM v_node node LEFT JOIN v_term_node term_node ON node.nid = term_node.nid LEFT JOIN v_term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('1') LEFT JOIN v_term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN v_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 = '4') GROUP BY node.nid, node_sticky, nid ORDER BY node_sticky DESC, nid DESC LIMIT 0, 15 in /var/www/drupal-5.1/includes/database.mysql.inc on line 172.

merlinofchaos: Is there something else I can give you to help you reproduce this yourself? You might want to test out Acidfree. To reproduce this, really all you have to do is create a new album inside the 'root' album and then try viewing the contents of that album. Oh, and you have to have a node_access module of your choice installed and enabled. If you have the Image module enabled, you can also try adding images to make sure that it shows the images.

After re-reading your last post, I realized that there is one more thing that we need to be aware of. By default, we are sorting by nid, but it is also possible to sort by creation date and title as well. I am not sure if this makes a difference or not.

Ooh. I just tested it. If I sort alphabetically, I don't get any sql errors. Same for creation date. It is only when I sort by nid that any errors show up.

Yup. And dammit, I was wrong. Somehow it got node.nid in there again, thus making me cry. =)

Well, that's why I released 1.6 as a beta.

Ok! Try that checkin.

Still not quite right:

user warning: Unknown column 'v_node.nid' in 'order clause' query: SELECT DISTINCT(node.nid), node.sticky AS node_sticky FROM v_node node LEFT JOIN v_term_node term_node ON node.nid = term_node.nid LEFT JOIN v_term_data term_data ON term_node.tid = term_data.tid AND term_data.vid IN ('1') LEFT JOIN v_term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN v_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 = '4') GROUP BY node.nid, node_sticky ORDER BY node_sticky DESC, v_node.nid DESC LIMIT 0, 15 in /var/www/drupal-5.1/includes/database.mysql.inc on line 172.

I don't even remember how my own system works apparently. Of course I didn't need the {} there.

Try now.

Eureka!

Thanks so much for working with me to get this fixed.

Has this been fully resolved yet? I am receiving a very similar error but on a different line. I have tried reinstalling all modules multiple times with no success.

user warning: You have an error in your SQL syntax near 'OFFSET 1' at line 1 query: SELECT DISTINCT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid WHERE n.type <> 'acidfree' AND tn.tid = 87 AND n.status = 1 ORDER BY nid DESC LIMIT 1 OFFSET 1 in /home/virtual/site61/fst/var/www/html/drupal-5.2/includes/database.mysql.inc on line 172.