Sorting by nid corrupts ORDER BY, converting "." to "_" [#136794]

I set up a very simple view showing node IDs, titles, and creation date, and it works fine until you attempt to sort by nid by clicking on its header. (The default sort criterion is by "Node: Created Time") Then, the following SQL error is generated:

user warning: Unknown column 'node_nid' in 'order clause' query: SELECT node.nid, node.created AS node_created_created, term_data.name AS term_data_name, term_data.tid AS term_data_tid, node.title AS node_title, node.changed AS node_changed, node.created AS node_created, review.review_rate AS review_review_rate FROM drupal_node node LEFT JOIN drupal_term_node term_node ON node.nid = term_node.nid LEFT JOIN drupal_term_data term_data ON term_node.tid = term_data.tid LEFT JOIN drupal_review review ON node.nid = review.nid WHERE (node.type IN ('review')) ORDER BY node_created_created ASC, node_nid ASC LIMIT 0, 15

As you can see, it is somehow converting "node.nid" to "node_nid," plus not only does it convert "node.created" to "node_created" it adds another "_created" to make it into "node_created_created"!!! wtf?

Thank you for looking into this! Here's the exported view:

  $view = new stdClass();
  $view->name = 'ViewsTest';
  $view->description = 'List of all reviews';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Views Test';
  $view->page_header = 'Header';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = 'There are no items currently posted. Check back!';
  $view->page_empty_format = '1';
  $view->page_type = 'table';
  $view->url = '';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '15';
  $view->menu = TRUE;
  $view->menu_title = '';
  $view->menu_tab = FALSE;
  $view->menu_tab_default = FALSE;
  $view->menu_tab_weight = '0';
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'created',
      'sortorder' => 'ASC',
      'options' => 'normal',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'nid',
      'label' => 'Node ID',
      'sortable' => '1',
    ),
    array (
      'tablename' => 'term_data',
      'field' => 'name',
      'label' => 'Category',
      'handler' => 'views_handler_field_tid_link',
      'sortable' => '1',
      'defaultsort' => 'DESC',
    ),
    array (
      'tablename' => 'node',
      'field' => 'created',
      'label' => 'Posted',
      'handler' => 'views_handler_field_date_custom',
      'sortable' => '1',
      'options' => 'M j, Y',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'review',
),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, term_data);
  $views[$view->name] = $view;

Comments

Comment #1

Vallenwood CreditAttribution: Vallenwood commented 16 April 2007 at 20:21

Whoops, I spoke too soon on the "node_created_created" part because the query does specify node.created AS node_created_created. But there is no such alias for "node_nid" so it remains undefined in the query.

Comment #2

merlinofchaos CreditAttribution: merlinofchaos commented 16 April 2007 at 20:29

Can you try clearing your cache? (You can easily clear your cache by going to the views admin page, clicking on the tools tab, and clicking the clear cache button). Then visit your view and see if it continues to show this problem?

Comment #3

Vallenwood CreditAttribution: Vallenwood commented 18 April 2007 at 18:43

Certainly did. Clearing the cache does nothing. Can't you duplicate this? I can't imagine this is unique to me.

Side note: the title of this bug report, and some of the text, is inaccurate; as I mention in my first comment I later noticed that the ORDER BY clause was referring to the alias of "node.nid" and expects there to be a "node_nid" alias. So the real bug here is not in the ORDER BY clause, but rather in the SELECT clause; the bug seems to be that in the generated SQL query the snippet SELECT node.nid should be SELECT node.nid AS node_nid but just isn't.

Comment #4

merlinofchaos CreditAttribution: merlinofchaos commented 18 April 2007 at 21:36

Sigh. This was supposed to be fixed.

You were more right the first time; node.nid is 'special' and is what is broken here is that trying to add node.nid as node_nid breaks because of Drupal's query rewriting (which gets unfortunately confused when you have multiple instances of node.nid in the select portion of a query and then corrupts it). I tested this pretty thoroughly and thought I had it completely fixed but I may've missed some small element somewhere.

Comment #5

merlinofchaos CreditAttribution: merlinofchaos commented 4 May 2007 at 23:40

Ok, figured this out. Fixed in -dev, will go out in beta5.

Comment #6

merlinofchaos CreditAttribution: merlinofchaos commented 4 May 2007 at 23:54

Status:

Active

» Fixed

Comment #7

(not verified) CreditAttribution: commented 19 May 2007 at 00:05

Status:

Fixed

» Closed (fixed)

Comment #8

alansz CreditAttribution: alansz commented 11 January 2008 at 22:00

Version:	5.x-1.6-beta4	» 5.x-1.6
Status:	Closed (fixed)	» Active

So I think I have something like this problem in 5.x-1.6. Here's the warning:

user warning: Unknown column 'node_data_field_purpose_field_purpose_value' in 'order clause' query: SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, node_data_field_suitable_for.field_suitable_for_value AS node_data_field_suitable_for_field_suitable_for_value, node_data_field_codebase.field_codebase_value AS node_data_field_codebase_field_codebase_value FROM node node LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid LEFT JOIN content_field_purpose node_data_field_purpose ON node.vid = node_data_field_purpose.vid LEFT JOIN content_field_genres node_data_field_genres ON node.vid = node_data_field_genres.vid LEFT JOIN content_type_mushlist node_data_field_suitable_for ON node.vid = node_data_field_suitable_for.vid LEFT JOIN content_type_mushlist node_data_field_codebase ON node.vid = node_data_field_codebase.vid WHERE (node.type IN ('mushlist')) AND (node.status = '1') AND (workflow_node.sid = '3') ORDER BY node_data in /home/drupal/drupal-5.6/includes/database.mysql.inc on line 172.

Here's the exported view:

$view = new stdClass();
$view->name = 'mushlist_published';
$view->description = 'MUSHlist';
$view->access = array (
);
$view->view_args_php = '';
$view->page = TRUE;
$view->page_title = 'Community MUSHlist';
$view->page_header = '';
$view->page_header_format = '1';
$view->page_footer = ' An RSS feed of this list is available at: http://community.pennmush.org/mushlist/feed

';
$view->page_footer_format = '3';
$view->page_empty = 'No mushes are currently on the MUSHlist.';
$view->page_empty_format = '1';
$view->page_type = 'table';
$view->url = 'mushlist';
$view->use_pager = FALSE;
$view->nodes_per_page = '0';
$view->menu = TRUE;
$view->menu_title = 'MUSHlist';
$view->menu_tab = FALSE;
$view->menu_tab_weight = '0';
$view->menu_tab_default = FALSE;
$view->menu_tab_default_parent = NULL;
$view->menu_tab_default_parent_type = 'tab';
$view->menu_parent_tab_weight = '0';
$view->menu_parent_title = '';
$view->sort = array (
);
$view->argument = array (
array (
'type' => 'rss_feed',
'argdefault' => '2',
'title' => '',
'options' => '',
'wildcard' => '',
'wildcard_substitution' => '',
),
);
$view->field = array (
array (
'tablename' => 'node',
'field' => 'title',
'label' => 'Name',
'handler' => 'views_handler_field_nodelink',
'sortable' => '1',
'defaultsort' => 'ASC',
'options' => 'link',
),
array (
'tablename' => 'node_data_field_purpose',
'field' => 'field_purpose_value',
'label' => 'Purpose',
'handler' => 'content_views_field_handler_group',
'sortable' => '1',
'options' => 'default',
),
array (
'tablename' => 'node_data_field_genres',
'field' => 'field_genres_value',
'label' => 'Genre(s)',
'handler' => 'content_views_field_handler_group',
'sortable' => '1',
'options' => 'default',
),
array (
'tablename' => 'node_data_field_suitable_for',
'field' => 'field_suitable_for_value',
'label' => 'Suitable for',
'handler' => 'content_views_field_handler_group',
'sortable' => '1',
'options' => 'default',
),
array (
'tablename' => 'node_data_field_codebase',
'field' => 'field_codebase_value',
'label' => 'Server flavor',
'handler' => 'content_views_field_handler_group',
'sortable' => '1',
'options' => 'default',
),
);
$view->filter = array (
array (
'tablename' => 'node',
'field' => 'type',
'operator' => 'OR',
'options' => '',
'value' => array (
0 => 'mushlist',
),
),
array (
'tablename' => 'node',
'field' => 'status',
'operator' => '=',
'options' => '',
'value' => '1',
),
array (
'tablename' => 'node',
'field' => 'title',
'operator' => 'contains',
'options' => '',
'value' => '',
),
array (
'tablename' => 'node_data_field_purpose',
'field' => 'field_purpose_value_default',
'operator' => 'OR',
'options' => '',
'value' => array (
0 => 'Educational',
1 => 'Roleplaying',
2 => 'Social',
3 => 'Other',
),
),
array (
'tablename' => 'node_data_field_codebase',
'field' => 'field_codebase_value_default',
'operator' => 'OR',
'options' => '',
'value' => array (
0 => 'Mux',
1 => 'PennMUSH',
2 => 'Rhost',
3 => 'TinyMUSH',
4 => 'Other',
),
),
array (
'tablename' => 'node_data_field_genres',
'field' => 'field_genres_value_default',
'operator' => 'OR',
'options' => '',
'value' => array (
0 => 'Contemporary',
1 => 'Fantasy',
2 => 'Historical',
3 => 'Horror',
4 => 'Science fiction',
5 => 'Other',
),
),
array (
'tablename' => 'workflow_node',
'field' => 'sid',
'operator' => 'AND',
'options' => '',
'value' => array (
0 => '3',
),
),
);
$view->exposed_filter = array (
array (
'tablename' => 'node',
'field' => 'title',
'label' => 'MUSH name',
'optional' => '1',
'is_default' => '0',
'operator' => '0',
'single' => '0',
),
array (
'tablename' => 'node_data_field_codebase',
'field' => 'field_codebase_value_default',
'label' => 'Server flavor',
'optional' => '1',
'is_default' => '0',
'operator' => '0',
'single' => '0',
),
array (
'tablename' => 'node_data_field_purpose',
'field' => 'field_purpose_value_default',
'label' => 'Purpose',
'optional' => '1',
'is_default' => '0',
'operator' => '0',
'single' => '0',
),
array (
'tablename' => 'node_data_field_genres',
'field' => 'field_genres_value_default',
'label' => 'Genre(s)',
'optional' => '1',
'is_default' => '0',
'operator' => '0',
'single' => '0',
),
);
$view->requires = array(node, node_data_field_purpose, node_data_field_genres, node_data_field_suitable_for, node_data_field_codebase, workflow_node);
$views[$view->name] = $view;