I submitted an event views module in this thread http://drupal.org/node/49157. The view generally seems to work fine, but someone has discovered that if she has taxonomy access enabled the view will not work. She gets an error like:

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 'DISTINCT(node.nid) = event.nid WHERE (node.status = '1') AND (node.type IN ('fl' at line 1 query: SELECT count(*) FROM drp_node node LEFT JOIN drp_event event ON DISTINCT(node.nid) = event.nid WHERE (node.status = '1') AND (node.type IN ('flexinode-1','flexinode-5')) AND (YEAR(FROM_UNIXTIME(event.event_start)) = 2006) AND (MONTH(FROM_UNIXTIME(event.event_start)) = 4) in //includes/database.mysql.inc on line 120.

I can confirm that if I create a flexinode event and set up the default view it works fine. As soon as I enable taxonomy access the error occurs. If I disable taxonomy access the view is fine again.

Is there anything that can be done to make this work with taxonomy access enabled? The view doesn't look particularly complicated, and I don't see anything that could be changed that seems to make any difference.

Here's the view she is using:

  $view = new stdClass();
  $view->name = 'copy of event list';
  $view->description = 'User selectable table of events.';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'event list';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'list';
  $view->url = 'event/views';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '10';
  $view->menu = TRUE;
  $view->menu_title = 'event list';
  $view->menu_tab = FALSE;
  $view->menu_tab_default = FALSE;
  $view->menu_weight = '';
  $view->sort = array (
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'sortable' => '1',
    ),
    array (
      'tablename' => 'event',
      'field' => 'event_start',
      'label' => 'Start Time:',
      'handler' => 'views_handler_field_date_small',
      'sortable' => '1',
      'defaultsort' => 'ASC',
    ),
    array (
      'tablename' => 'event',
      'field' => 'event_end',
      'label' => 'End Time:',
      'handler' => 'views_handler_field_date_small',
      'sortable' => '1',
      'defaultsort' => 'ASC',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'flexinode-1',
  1 => 'flexinode-5',
),
    ),
    array (
      'tablename' => 'event',
      'field' => 'year',
      'operator' => '=',
      'options' => '',
      'value' => 'current',
    ),
    array (
      'tablename' => 'event',
      'field' => 'month',
      'operator' => '=',
      'options' => '',
      'value' => 'current',
    ),
    array (
      'tablename' => 'event',
      'field' => 'day',
      'operator' => '=',
      'options' => '',
      'value' => 'all',
    ),
  );
  $view->requires = array(node, event);
  $views[$view->name] = $view;

Comments

merlinofchaos’s picture

merlinofchaos commented

It's more node_access() than taxonomy_access in particular that's causing the problem; all of the _access* modules rely on node_access's rewriting, which in this case is failing because it's not getting quite what it expects.

I'm not sure if it will help or not, but try turning on the DISTINCT filter; it's possible that with that on, node_access won't try to add its own DISTINCT. Failing that, I may need to do yet a little more work on the query to ensure it doesn't get mangled during db_rewrite_sql.

karens’s picture

karens commented

Did some more experimenting and found that if I used a different sql format taxonomy access is not a problem. The sql that worked OK was: 

pager_query SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.event_start AS event_event_start, event.event_end AS event_event_end FROM node node LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND (node.type IN ('event','flexinode-1')) AND (event.event_start > '1048568400') AND (event.event_end <= '1148533200') LIMIT 0, 10

The sql that would not work was:

pager_query SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.event_start AS event_event_start, event.event_end AS event_event_end FROM node node LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND (node.type IN ('event','flexinode-1')) AND (YEAR(FROM_UNIXTIME(event.event_start)) = 2006) AND (MONTH(FROM_UNIXTIME(event.event_start)) = 4) AND (DAY(FROM_UNIXTIME(event.event_start)) = 25) LIMIT 0, 10
karens’s picture

karens commented

I tried turning on and off the distinct filter. Turning it on actually made the problem worse -- I got no results plus an error. Turning it off I got results but still got the error.

I realize it is taxonomy access that is at fault here, just hoping to find a workaround to make the view work. I did find that if she uses the jscalendar option that changes the sql (as described in the previous post) the view will work. I posted that info on the original issue page.

karens’s picture

karens commented

OK, I've done some more investigation. The same error occurs if I turn on node privacy by role. Like you said, a common thread for node_access issues. I also tried the example arguments in your documentation that used a similar sql format (the example that used the year as an argument and a handler that writes the sql as YEAR(FROM_UNIXTIME()) and get the same error, so it is some incompatibility between that particular sql format and the sql rewrite and it looks like it is going to bite anyone who tries to use those examples who has any type of node access control set up, so this could affect a lot of users.

I took a look at the node_db_sql_rewrite function in the node module and saw that it is set up to always return a 1 for the distinct value. It does NOT check whether there is already a DISTINCT in the query, so that is why adding the node:distinct filter does no good and even makes things worse.

Just to see what would happen, I changed that line in node.module to only return a 1 if there was not already a distinct in the query (using a strstr to check for the value 'DISTINCT'), then added node:distinct to the view so the rewrite function wouldn't add it, and that eliminated the error and the views all worked fine.

You know core and node_access far better than I do. Is this something that could or should be patched in core without doing any harm to anything else? It certainly seems like bad behavior for node access to automatically add a distinct clause to every query without checking whether one already exists.

karens’s picture

karens commented

More info. Sorry for the blizzard of messages, but this is interferring with all the event and date-related views I try to create. I wanted to update the events_view module to take into account arguments for the month, year, event id, etc, but I need this kind of sql to work to do that. I now see that there are actually 2 queries generated when the view is generated and the worst error is actually in the first one while I've been focusing on the second. The queries I pasted above were from the second execution which is why they don't appear to be as garbled as they really are. Anyway when running the devel module I see the following queries that seem to be the problem. Note that DISTINCT has been added to the ON part of the join in the first one. This is the pattern I get if I have no node.distinct in the view. If I add node.distinct to the view, I get the error in the second query as well.

SELECT count(*) FROM node node LEFT JOIN event event ON DISTINCT(node.nid) = event.nid WHERE (node.status = '1') AND (node.type IN ('blog','event')) AND (YEAR(FROM_UNIXTIME(node.created+-21600)) = 2005)

SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, event.event_start AS event_event_start, event.event_end AS event_event_end FROM node node LEFT JOIN event event ON node.nid = event.nid WHERE (node.status = '1') AND (node.type IN ('blog','event')) AND (YEAR(FROM_UNIXTIME(node.created+-21600)) = 2005) ORDER BY event.event_end ASC LIMIT 0, 10

merlinofchaos’s picture

merlinofchaos commented

My best guess at this time is that it's a bug in node_access and it has to do with from_unixtime. I need to ask Moshe.

merlinofchaos’s picture

merlinofchaos commented

Alright, it looks like my patch to fix the FROM_UNIXTIME stuff in db_rewrite_sql actually got undone at some point. Boo. Yah.

I think preg_replace is case sensitive. Can someone who's set up to repreoduce this try something for me?

In views, replace all instances of FROM_UNIXTIME with from_UNIXTIME, refresh your cache (and possibly re-save the view because the query may *also* be cached if it has no arguments) and see if the problem still occurs?

karens’s picture

karens commented

OK, this is weird, after struggling with this problem a couple weeks ago I now cannot reproduce it. I didn't even try your change since it now seems to be working. Maybe something changed in HEAD??? At any rate, it seems to be fixed in the latest Drupal cvs. I'd like to have someone besides me confirm it, though.

merlinofchaos’s picture

merlinofchaos commented

Update: I had some success helping out MichelleC by changing a line in views_query.inc:

    $this->count_field = "*";

becomes

    $this->count_field = "$views_get_title_table.$views_get_title_field";

I'm not committing this yet because I seem to recall in the depths of my memory that I'd *originally* tried to have that as the count field and it caused a problem somewhere.

I'd appreciate it if anyone affected by this could try it out and 1) see if it works and 2) see if there are other consequences.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

Well I committed this by accident at some point, so what the heck.

Hopefully nothing breaks.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
karens’s picture

karens commented
Version: » 6.x-2.x-dev
Status: Closed (fixed) » Active

Boy you don't know how bad I feel about re-opening this issue again, but I am trying to get views integration working with organic groups and this problem has reared its ugly head again since we're dealing with node access. Anyway, I decided I have to get this thing wrestled to the ground for once and for all so I have come up with a simple useage example that always fails. The pattern is when 2 tables are joined and there is a 'from_unixtime' in the where clause. That's it. Any query that fits that pattern will get broken when run through db_rewrite_sql. Remove the join or from_unixtime and it works fine again.

I just reported it as a core issue at http://drupal.org/node/79904. (Yes, I know it's not a views issue, but I thought it would be good to keep a views issue open so people who have this problem can find it and see what is going on.) Hopefully someone will be able to figure this out...

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

The referenced patch has been committed to HEAD and 4.7 so hopefully this is all finally really truly done with.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)