db_rewrite_sql improperly rewriting a subquery

kenorb - November 8, 2008 - 00:08

Project:	Drupal
Version:	6.x-dev
Component:	node system
Category:	bug report
Priority:	critical
Assigned:	Unassigned
Status:	active

user warning: Unknown column 'node.nid' in 'on clause' query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS nid FROM node node WHERE (node.status <> 0 OR node.uid = 0 or 0 = 1) 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 = 0 AND na.realm = 'view_own_owner') OR (na.gid = 1 AND na.realm = 'view_own_role'))) AND ( tn.tid = 23 )) )) count_alias in /sites/all/modules/contributions/views/includes/view.inc on line 697.
user warning: Unknown column 'node.nid' in 'on clause' query: SELECT DISTINCT(node.nid) AS nid, node.sticky AS node_sticky, node.created AS node_created FROM node node WHERE (node.status <> 0 OR node.uid = 0 or 0 = 1) 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 = 0 AND na.realm = 'view_own_owner') OR (na.gid = 1 AND na.realm = 'view_own_role'))) AND ( tn.tid = 23 )) )ORDER BY node_sticky DESC, node_created DESC LIMIT 0, 10 in /sites/all/modules/contributions/views/includes/view.inc on line 723.

after I'm clicking specified tag in block (uri: /category/tags/drupal).
I don't have this problem, when I'm logged in as admin.
Any ideas?

» Login or register to post comments

#1

kenorb - November 8, 2008 - 00:11

Probably the reason is when I've enabled `taxonomy_term` view.
After I disabled it, there is no error. But MySQL syntax should be fixed.

#2

merlinofchaos - November 8, 2008 - 00:18

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

#3

jthaxton - November 10, 2008 - 02:34

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.

#4

jthaxton - November 10, 2008 - 02:40

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!

#5

aelling - November 24, 2008 - 21:39

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.

#6

merlinofchaos - November 26, 2008 - 18:22

Title:	Unknown column 'node.nid' in 'on clause' query: SELECT COUNT(*)	» db_rewrite_sql improperly rewriting a subquery
Project:	Views	» Drupal
Version:	6.x-2.x-dev	» 6.x-dev
Component:	Code	» node system

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. :/

#7

dotist - December 14, 2008 - 13:15

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.

#8

kahenya - January 2, 2009 - 12:59

Sorry wrong place to suubmit

#9

kenorb - January 16, 2009 - 01:16

Duplicates:
#40623: Bad interaction with MySQL 5 & Image & Event modules
#51850: nailing down db_rewrite_sql (mysql 5)
#46864: SQL error when anonymous: Unknown column 'n.nid'
#303906: SQL Error: Unknown column 'n.nid'
#357073: Getting `Unknown column 'n.uid' in 'field list'` Error After Installing Storm
http://drupal.org/node/43735

#10

kenorb - January 16, 2009 - 04:40

Priority:

normal

» critical

Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.
Read more: http://dev.mysql.com/doc/refman/5.0/en/join.html

Explanations of the issue:
http://drupal.org/node/40623#comment-362005
http://drupal.org/node/43735#comment-92865

Here you will find my quick-fix to hide those error messages for all users apart admin:) http://drupal.org/node/342128#comment-1203466

#11

dotist - January 19, 2009 - 15:38

thanks!

#12

kenorb - May 22, 2009 - 10:24

Possible quick fix with parenthesis:
http://bugs.mysql.com/bug.php?id=13832
in the bottom

db_rewrite_sql improperly rewriting a subquery

Jump to:

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

User login

Contributor links