db_rewrite_sql improperly rewriting a subquery

That error makes no sense. It says your 'node' table has no 'nid' column. That's...not possible.

I get this error as well for a View on my site:

Timeline Events for...
user warning: Unknown column 'node_node_data_field_timeline_bio.vid' in 'on clause' query: SELECT DISTINCT(node.nid) AS nid, node_data_field_timeline_start_dt.field_timeline_start_dt_value AS node_data_field_timeline_start_dt_field_timeline_start_dt_value, node_data_field_timeline_start_dt.nid AS node_data_field_timeline_start_dt_nid, node.type AS node_type FROM node node LEFT JOIN content_type_timeline node_data_field_timeline_bio ON node_node_data_field_timeline_bio.vid = node_data_field_timeline_bio.vid INNER JOIN node node_node_data_field_timeline_bio ON node_data_field_timeline_bio.field_timeline_bio_nid = node_node_data_field_timeline_bio.nid LEFT JOIN content_type_timeline node_data_field_timeline_start_dt ON node.vid = node_data_field_timeline_start_dt.vid WHERE (node.status <> 0) AND (node.type in ('timeline')) AND (node_data_field_timeline_bio.field_timeline_bio_nid = 7) LIMIT 0, 50 in /home/machobea/public_html/everybodypoopsagain/sites/all/modules/views/includes/view.inc on line 723.

From what I can tell, the table alias is being referenced before the table alias is set (at least in my instance) in the second line. The alias is calling from the 'node' table so if I update the query and run it in mysql (outside of Drupal) the replacement for the alias works. Does that help?

It appears that I can have an argument or a relationship, but not both.

As long as I leave the "use relationship" field empty in my argument, I can totally get rid of the error and get data back. Sorry to post twice and resolve my own issue along the way but perhaps this will help? Thanks!

I receive the same error as previously posted. The view works fine when logged in as an administrator, but as an anonymous user the following error is generated.

    * user warning: Unknown column 'node.nid' in 'on clause' query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid FROM node node LEFT JOIN content_type_advertiser_content node_data_field_advertiser_website ON node.vid = node_data_field_advertiser_website.vid LEFT JOIN content_field_city node_data_field_city ON node.vid = node_data_field_city.vid LEFT JOIN content_field_state node_data_field_state ON node.vid = node_data_field_state.vid WHERE (node.status <> 0) AND (node.vid IN ( SELECT tn.vid FROM term_node tn INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner'))) AND ( tn.tid = 88 )) )) count_alias in /WEBSITES/websitesDevelopment/drupal/sites/all/modules/views/includes/view.inc on line 697.
    * user warning: Unknown column 'node.nid' in 'on clause' query: SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node_data_field_advertiser_website.field_advertiser_website_url AS node_data_field_advertiser_website_field_advertiser_website_url, node_data_field_advertiser_website.field_advertiser_website_title AS node_data_field_advertiser_website_field_advertiser_website_title, node_data_field_advertiser_website.field_advertiser_website_attributes AS node_data_field_advertiser_website_field_advertiser_website_attributes, node_data_field_advertiser_website.nid AS node_data_field_advertiser_website_nid, node.type AS node_type, node_data_field_city.field_city_value AS node_data_field_city_field_city_value, node_data_field_city.nid AS node_data_field_city_nid, node_data_field_state.field_state_value AS node_data_field_state_field_state_value, node_data_field_state.nid AS node_data_field_state_nid, node.sticky AS node_sticky, node.created AS node_created FROM node node LEFT JOIN content_type_advertiser_content node_data_field_advertiser_website ON node.vid = node_data_field_advertiser_website.vid LEFT JOIN content_field_city node_data_field_city ON node.vid = node_data_field_city.vid LEFT JOIN content_field_state node_data_field_state ON node.vid = node_data_field_state.vid WHERE (node.status <> 0) AND (node.vid IN ( SELECT tn.vid FROM term_node tn INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner'))) AND ( tn.tid = 88 )) )ORDER BY node_sticky DESC, node_created DESC LIMIT 0, 20 in /WEBSITES/websitesDevelopment/drupal/sites/all/modules/views/includes/view.inc on line 723.

This would appear to be another case of core's db_rewrite_sql improperly rewriting a query. It is rewriting the sub select rather than the primary select. :/

hi,

i get the same warning as above but doubled:

    * user warning: Unknown column 'node_data_field_honorarhhe.field_honorarhhe_value' in 'field list' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid, node_data_field_autor.nid AS node_data_field_autor_nid, node.type AS node_type, node_data_field_artikelpublishdate.field_artikelpublishdate_value AS node_data_field_artikelpublishdate_field_artikelpublishdate_value, '' AS title, 'SUM' AS TOTAL_SUM, '' AS field_autor_nid, '' AS field_artikelpublishdate_value, SUM(node_data_field_honorarhhe.field_honorarhhe_value) AS field_honorarhhe_value FROM node node LEFT JOIN content_type_content_artikelfuerkalendar node_data_field_autor ON node.vid = node_data_field_autor.vid LEFT JOIN content_type_content_artikelfuerkalendar node_data_field_artikelpublishdate ON node.vid = node_data_field_artikelpublishdate.vid WHERE (node.type in ('content_artikelfuerkalendar')) AND (node.nid IN (758,639,188,259,234,735,414,579,460,302,248,699,504,388,247,201,246,777,28,26,30,27,51,50,97,36,52,54,58,56,55,59,61,41,62,31,65,66,67,42,69,68,73,72,70,75,77,76,78,79)) GROUP BY TOTAL_SUM ORDER BY node_data_field_artikelpublishdate_field_artikelpublishdate_value ASC ) count_alias in /home/.perry/dotist/dyss.net/artnet/modules/contrib/views/includes/view.inc on line 697.
    * user warning: Unknown column 'node_data_field_honorarhhe.field_honorarhhe_value' in 'field list' query: SELECT node.nid AS nid, node_data_field_autor.nid AS node_data_field_autor_nid, node.type AS node_type, node_data_field_artikelpublishdate.field_artikelpublishdate_value AS node_data_field_artikelpublishdate_field_artikelpublishdate_value, '' AS title, 'SUM' AS TOTAL_SUM, '' AS field_autor_nid, '' AS field_artikelpublishdate_value, SUM(node_data_field_honorarhhe.field_honorarhhe_value) AS field_honorarhhe_value FROM node node LEFT JOIN content_type_content_artikelfuerkalendar node_data_field_autor ON node.vid = node_data_field_autor.vid LEFT JOIN content_type_content_artikelfuerkalendar node_data_field_artikelpublishdate ON node.vid = node_data_field_artikelpublishdate.vid WHERE (node.type in ('content_artikelfuerkalendar')) AND (node.nid IN (758,639,188,259,234,735,414,579,460,302,248,699,504,388,247,201,246,777,28,26,30,27,51,50,97,36,52,54,58,56,55,59,61,41,62,31,65,66,67,42,69,68,73,72,70,75,77,76,78,79)) GROUP BY TOTAL_SUM ORDER BY node_data_field_artikelpublishdate_field_artikelpublishdate_value ASC LIMIT 0, 50 in /home/.perry/dotist/dyss.net/artnet/modules/contrib/views/includes/view.inc on line 723.

any clues on potential fixes? i ran suspect query on mysql out of drupal and got the results i was looking for. it only seems broken w/i drupal framework...
any hints would be appreciated.

thanks!

This is active as there are no patches to review.

I don't know about drupal 6 but in drupal 5 I had this problem:
any sub-queries in my sql that was passed through db_rewrite_sql was mangled
because it runs on all WHERE's in the sql, even the subqueries.

A workaround I used from D5 was to have "where" in lower case in my sub-queries.
Because db_rewrite_sql looks for WHERE in uppercase it leaves my sub-queries alone.

Again, that is in drupal 5, I haven't looked into it for drupal 6.

Also, if you have in your subquery GROUP, ORDER BY or LIMIT db_rewrite_sql will close it parentheses in between your WHERE and whichever of those you are using.

This will cause you SQL errors.

To get around this you can also just use lower case for these as it only checks for them in upper case.

Has this issue been fixed in the latest D6?