Query builder fails to rebuild query with Node Updated Time filter

Okay, I'll try to explain it a little better then.

The module I've written (and which I plan to make public once it works as intended) allows filtering any view (the admin decides which views it apples to) using terms (from a vocab selected by the admin) which can be specified by the user in his/her profile. In order to do this, the module loads the user's preferences and adds a filter with the terms selected by the user. There is also a block allowing the user to override any preferences and filter by one of the terms listed here. The block is available to all users, anonymous or authenticared. The module is used for a digg-like site where members need to be able to filter stories as they like.

I am using the following code to modify a view (I've omitted some unrelated code for clarity):

<?php
/**
 * Implementation of hook_views_pre_query()
 * 
 * lets us modify a view before it is loaded from the database
 */
function views_termsblock_views_pre_query(&$view){

...

//to add a filter
views_view_add_filter($view, '',  "term_node_$vocab.tid", 'OR', $terms, '');

...

//to add an argument
views_view_add_argument($view, 'taxletter', 1, '', 0);

...

  //unset queries forcing views to rebuild them
  unset ($view->query);
  unset ($view->countquery);

}
?>

If everything goes well, this is the query (a print_r'd copy of $info) and the view object we end up with (the filter with array index 4 is the one the module added) which produces a set of rows allowing us to list the nodes that fit the user's preferences:

$info:Array
(
    [query] => SELECT DISTINCT(node.nid), node.changed AS node_changed_changed FROM {node} node  LEFT JOIN {term_node} term_node ON node.nid = term_node.nid WHERE (%s.%s IN ('%s')) AND (%s.%s %s '%s') AND (%s.%s %s '%s') AND (term_node.tid IN ('76','81')) GROUP BY node.nid ORDER BY node_changed_changed DESC
    [countquery] => SELECT count(DISTINCT(node.nid)) FROM {node} node  LEFT JOIN {term_node} term_node ON node.nid = term_node.nid WHERE (%s.%s IN ('%s')) AND (%s.%s %s '%s') AND (%s.%s %s '%s') AND (term_node.tid IN ('76','81'))  
    [summary] => 
    [level] => 
    [args] => Array
        (
            [0] => node
            [1] => type
            [2] => content_wordsy_story
            [3] => node
            [4] => promote
            [5] => =
            [6] => 1
            [7] => node
            [8] => status
            [9] => =
            [10] => 1
        )

)

stdClass Object
(
    [vid] => 2
    [name] => TopStories
    [description] => Top Stories on Wordsy.com
    [access] => Array
        (
        )

    [page] => 1
    [page_title] => Frontpage
    [page_header] => These are the most popular stories. Click here to see the newest stories.
    [page_header_format] => 1
    [page_empty] => 
    [page_empty_format] => 1
    [page_footer] => 
    [page_footer_format] => 1
    [page_type] => teaser
    [use_pager] => 1
    [nodes_per_page] => 10
    [url] => topstories
    [menu] => 0
    [menu_tab] => 0
    [menu_tab_default] => 0
    [menu_tab_weight] => 0
    [menu_title] => Top Stories
    [block] => 0
    [block_title] => 
    [block_use_page_header] => 0
    [block_header] => 
    [block_header_format] => 1
    [block_use_page_footer] => 0
    [block_footer] => 
    [block_footer_format] => 1
    [block_use_page_empty] => 0
    [block_empty] => 
    [block_empty_format] => 1
    [block_type] => list
    [nodes_per_block] => 0
    [block_more] => 0
    [breadcrumb_no_home] => 1
    [changed] => 1177599597
    [view_args_php] => 
    [sort] => Array
        (
            [0] => Array
                (
                    [vid] => 2
                    [position] => 0
                    [field] => node.changed
                    [sortorder] => DESC
                    [options] => normal
                    [tablename] => 
                    [id] => node.changed
                )

        )

    [argument] => Array
        (
        )

    [field] => Array
        (
        )

    [filter] => Array
        (
            [0] => Array
                (
                    [vid] => 2
                    [tablename] => 
                    [field] => node.type
                    [value] => Array
                        (
                            [0] => content_wordsy_story
                        )

                    [operator] => OR
                    [options] => 
                    [position] => 0
                    [id] => node.type
                )

            [1] => Array
                (
                    [vid] => 2
                    [tablename] => 
                    [field] => node.promote
                    [value] => 1
                    [operator] => =
                    [options] => 
                    [position] => 1
                    [id] => node.promote
                )

            [2] => Array
                (
                    [vid] => 2
                    [tablename] => 
                    [field] => node.status
                    [value] => 1
                    [operator] => =
                    [options] => 
                    [position] => 2
                    [id] => node.status
                )

            [3] => Array
                (
                    [vid] => 2
                    [tablename] => 
                    [field] => node.distinct
                    [value] => Array
                        (
                        )

                    [operator] => =
                    [options] => 
                    [position] => 3
                    [id] => node.distinct
                )

            [4] => Array
                (
                    [tablename] => 
                    [field] => term_node_5.tid
                    [operator] => OR
                    [value] => Array
                        (
                            [0] => 76
                            [1] => 81
                        )

                    [options] => 
                )

        )

    [exposed_filter] => Array
        (
        )

    [build_type] => page
    [type] => teaser
    [used_filters] => Array
        (
        )

    [args] => Array
        (
        )

    [total_rows] => 3
    [num_rows] => 3
)

Unfortunately, this does not seem to work with views that use the Node Updated Time filter. Now I am beginning to wonder whether this is entirely related to my module as it seems to happen even without it. MySQL is throwing errors like the one below on localhost even when the view is not being modified while on the testing server it seems to happen only when the pre_query hook is being used to modify the view object. An unmodified view:

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 'DESC LIMIT 0, 10' at line 1 query: SELECT DISTINCT(node.nid), value FROM node node WHERE (node.type IN ('content_wordsy_story')) AND (node.promote = '1') AND (node.status = '1') AND (node.changed > 1178457101 + -31536000) GROUP BY node.nid ORDER BY DESC LIMIT 0, 10 in /Applications/MAMP/htdocs/sitename/includes/database.mysql.inc on line 172.

This is a similar error produced by a view that is modified:

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 'DESC LIMIT 0, 10' at line 1 query: SELECT DISTINCT(node.nid), value FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid WHERE (node.type IN ('content_wordsy_story')) AND (node.promote = '1') AND (node.status = '1') AND (node.changed > 1178457529 + -31536000) AND (term_node.tid IN ('75','76','77','79','78','80')) GROUP BY node.nid ORDER BY DESC LIMIT 0, 10 in /home/sitename/public_html/sandbox/includes/database.mysql.inc on line 172.

Both queries look perfectly OK to me so I am clearly missing something here. What is confusing me further is that some views that use the Node Updated Time filter (node.changed > now + -x) and are modified by my module return rows as they should without producing any errors whatsoever. That could mean that this error could be entirely unrelated to Views and not the result of a bug at all. What do you think?

Some additional information I forgot to include:

- When clicking a term in the block, an argument is added to the view taking the name of the term, like: sitename.com/movies to only show stories about movies (tagged with the term 'movies').

- The print_r'd output above is produced like this (I've modified Views to pass some extra variables through this hook, such as $info, for debugging purposes):

<?php
function module_name_views_pre_view($view, $items, $info){

  print '<pre>'; 
  print '$info:';
  print_r($info);
  print "\n";
  print_r($view);
  exit;

}
?>

Doh! I totally missed that! Thanks :)

Here are two views that generate errors. The first one generates the error it regardless of whether my module is enabled or not, the second one only does it when the module is enabled:

  $view = new stdClass();
  $view->name = 'TopLast365days';
  $view->description = 'Top Stories in the last 365 days';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'This Year\'s Top Stories';
  $view->page_header = 'Most Popular Frontpage Stories in the last 365 days, ordered by number of votes';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'teaser';
  $view->url = 'toplast365days';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '10';
  $view->sort = array (
    array (
      'tablename' => 'votingapi_cache',
      'field' => 'value',
      'sortorder' => 'DESC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'content_wordsy_story',
),
    ),
    array (
      'tablename' => 'node',
      'field' => 'promote',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'changed',
      'operator' => '>',
      'options' => '-31536000',
      'value' => 'now',
    ),
    array (
      'tablename' => 'node',
      'field' => 'distinct',
      'operator' => '=',
      'options' => '',
      'value' => array (
),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(votingapi_cache, node);
  $views[$view->name] = $view;

  $view = new stdClass();
  $view->name = 'TopToday';
  $view->description = 'Top Stories in the last 24 hours';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Today\'s Top Stories';
  $view->page_header = 'Most Popular Frontpage Stories in the last 24 hours, ordered by number of votes';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = 'Hey, no stories were made popular yet today. Maybe you should <a href="node/add/content_wordsy_story">submit a story yourself</a>.';
  $view->page_empty_format = '1';
  $view->page_type = 'teaser';
  $view->url = 'toptoday';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '10';
  $view->sort = array (
    array (
      'tablename' => 'votingapi_cache',
      'field' => 'value',
      'sortorder' => 'DESC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'content_wordsy_story',
),
    ),
    array (
      'tablename' => 'node',
      'field' => 'promote',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'changed',
      'operator' => '>',
      'options' => '-86400',
      'value' => 'now',
    ),
    array (
      'tablename' => 'node',
      'field' => 'distinct',
      'operator' => '=',
      'options' => '',
      'value' => array (
),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(votingapi_cache, node);
  $views[$view->name] = $view;

Looking at the above, the issue seems related to the Voting API cache table - which in itself is interesting because I haven't made any modifications to that module.

So, what do you think? Is this a Views bug or is it related to something else?

We will pay for effective and prompt assistance with resolving this problem.

Apparently the issue is not with Node Update Field filter, I was wrong and misunderstood how it all worked. Sorry about the confusion.

Point is, I can replicate this exact issue with a view that lists nodes using the votingapi_cache table as a field (without the additional factor of the module I have made). The faulty sorting happens only on those views, which has led me to suspect that there's a bug in votingapi_views.inc.

The faulty ORDER BY query (where the field is omitted) only happens when a votingapi_cache table field is used as field in the query, ie; ORDER BY votingapi_fieldname. It tends to happen under certain conditions and I have yet to determine exactly what those conditions are, however it seems to be pointing at votingapi_cache.

I implemented some form of workaround for the client (was over a year ago now). The issue can be closed.