Multiple values node reference error in CCK view

I have exactly the same error!!!

The query that's failing does not look like a Views query. (Worse it looks like Yet Another Bad rewrite from Drupal core.

http://www;drupal.org/node/163652 was marked duplicate - below is the text from this other thread, as it has some detailed info.

To reproduce this bug, create a view with several fields including a nodereference. Then use it as the advanced view to select reference items in another nodereference field. Let's say this field is used in a content type called "thingy".

You should see a mysql error on both the create content page for thingy and on the manage fields page. I haven't looked into it in detail but the problem seems to be related to SQL rewrite.

One thing in passing - when generating the widgets for the manage fields page it would surely be better if there were some flag that told the nodereference module not to bother getting the reference objects. That's the page I first saw the error on, and I was extremely puzzled as to why that page seemed to be fetching nodes, until I dived into the code and it saw what was going on.

I tracked down the code responsible for this error, though I don't have a proper solution as yet. It's in content_views.inc, in the function content_views_field_handler_group().

The lines in question are:

    $query = "SELECT ". implode(', ', $query_columns) .
             " FROM {node} node".
             " LEFT JOIN {". $field_info['content_db_info']['table'] ."} node_data_$field[field_name] ON node.vid = node_data_$field[field_name].vid".
             " WHERE node.nid = ". $data->nid .
             " ORDER BY node_data_$field[field_name].delta";
    $result = db_query(db_rewrite_sql($query, 'node'));

The call to db_rewrite_sql() results in erroneous SQL being generated. I've worked round this by removing it, so the final line above becomes simply:

    $result = db_query($query);

Losing the call to db_rewrite_sql() is unlikely to cause an issue in most cases, but it would be much better to fix the input SQL so it is rewritten correctly.

Thanks a lot for that "temporary" solution, alfaguru ... worked for me!

Jon

Hi,
just to confirm here that i had exactly the same errors -and the fix worked

thanks for posting this issue and the -quick fix

hope the issue will be fixed soon as the nodereference.module and CCK are a popular widely used Drupal modules as its a crutial bug

I'm now running into this error as well. It is indeed a problem with db_rewrite_sql() which grabs the first 'node.nid' it can find and makes it DISTINCT, which breaks badly with nodereference. There are two ways to fix this. One is the one mentioned above, to avoid using db_rewrite_sql() at all. The other is to give the rewrite something else to attach its behavior to, which can be done by adding 'node.nid, '. to the beginning of the list of fields so that it is the first item in the select list.

Both fixes will work, the question is whether we need to be using db_rewrite_sql() or not. If so, the second fix is better. I'm going to commit the second fix. If that seems not to be the right solution, this issue can be re-opened.

The node.nid trick definitely seems better than removing db_rewrite_sql. Cool :-)

Hmm. I wonder if using something like:

SELECT node.nid, DISTINCT(node.nid) 

would fix some of the problems in Views, where db_rewrite_sql gets really twitchy if node.nid is already distinct.