SQL errors in tracker caused by views

SELECT arg.*, view.name FROM view_argument arg JOIN view_view view ON arg.vid=view.vid WHERE arg.type IN ('') AND view.page_type='date_views_browser'

This is really odd.

That query is working on a views table, but that query does not appear in Views. For one, nowhere in Views do I use a JOIN without qualifying it with either LEFT or INNER; for two, 'date_views_browser' is not a default view type.

I think you have some other module that utilizes Views that is causing this problem.

A quick grep through contrib shows that the module 'date' for CCK is the source of the errant query:

$result = db_query("SELECT arg.*, view.name FROM {view_argument} arg JOIN {view_view} view ON arg.vid=view.vid WHERE arg.type IN ($argument_list) AND view.page_type='date_views_browser'");

Sending this over there.

I haven't been able to replicate this. I need to know a couple things.

Did you have any date views set up, like Date Browser? If so, do you have a block view showing on the page you are having trouble with? If so, that view is the culprit, and I need an export of the view to see how it is set up.

Also, double-check your setup of the tracker view when you re-enable the Date module. There is an option for a Date: Browser plugin which ends up being the first option in the list. If that got selected somehow instead of the table view, it would produce strange results like you describe, and it would go away if you disable the Date module because that plugin would no longer be available.

I also am getting this error, but it's not on the tracker page, but is being show on the pages where I have my views blocks. If I disable the date module, the problem goes away. If I disable the views blocks that I have on these pages, it also goes away. Here is an export of one of my views:

  $view = new stdClass();
  $view->name = 'news_theme';
  $view->description = 'News related to a theme';
  $view->access = array (
);
  $view->view_args_php = 'if ($type == 'block' && arg(0) == 'taxonomy' && is_numeric(arg(2))) {
$tid = arg(2);
//$node = node_load($nid);
$args[] = $tid;
}
return $args;';
  $view->block = TRUE;
  $view->block_title = 'Latest News';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'teaser';
  $view->nodes_per_block = '5';
  $view->block_more = '0';
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'created',
      'sortorder' => 'DESC',
      'options' => '',
    ),
  );
  $view->argument = array (
    array (
      'type' => 'taxid',
      'argdefault' => '1',
      'title' => '',
      'options' => '5',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'content_news',
),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node);
  $views[$view->name] = $view;

Cheers,

Charles.

Update to the latest version of views, pull up your view and make sure that Page type is not set to Date:Browser, and save the view and that should fix it.

Your export shows that there is no page_type defined and in earlier versions of Views the Date: Browser would end up at the top of the page type list and thus be chosen as the page type by default when you save the view. That has been fixed in latest views which puts the normal views page types at the top of the list. You will need to pull up and save your view to be sure it wasn't previously saved with the wrong option.

Reopen if that doesn't do it.

The problem is caused on date_views.inc file as the
SELECT arg.*, view.name FROM tribunali_view_argument arg JOIN tribunali_view_view view ON arg.vid=view.vid WHERE arg.type IN ('') AND view.page_type='date_views_browser'

SQL code doesn't provide a valid JOIN specification. At least, this is not working under mysql 3.23.

You won't get that SQL if you don't have the date browser selected as the page type. Did you try what I suggested above?

I did need to add a test for whether you were trying to do a page or block view, which is in the latest commit. But you still need to fix your view so it does not have Date:Browser selected for the page type, just in case you every want to enable a page view.

Thanks KarenS and pulpzebra. I initially couldn't find where to change the page view type (I know, really stupid...). Found it under the Page link on the view edit screen, and changed it, but it still didn't help. ALso, I'm not displaying as a page, but as a block - and the block view type is "Teaser List". I think I'll try upgrading to the latest views module and see if this sorts it out. I'm also not sure what version of MySQL the host is using, but I suspect it could be 3.23.... so that could be the problem?

Cheers.

The problem is not your mysql version, but I think I see now a situation where you would get this error. Update to the latest version of the date module which I think will fix it.

so many posts...

well. the execution of the sql code above in phpmyadmin issued a syntax error. Don't know if in the meantime you updated the date module, but yes, I was running the latest date and views module (downloaded ab. 1 hour and a half ago).

However I think that mysql version DOES matter: here is what reported from mysql manual

Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.

Since I am on mysql 3.23 (... hope to upgrade as soon as possible!... ) a JOIN statement without condition should issue a syntax error.

The reason I said the version did not matter is that the sql in the example would be invalid in any version. And yes, I made a fix when I made that last post, so you need to pick up the latest version of the date module.

It actually works on mysql 4.1.x

but ok, backporting is always appreciated.

Thanks Karen for keeping your work always up to date!

--- Karen

I'm sorry to report that the new date_views.inc

$Id: date_views.inc,v 1.6.2.12 2006/11/15 16:23:25 karens Exp $

doesn't seem to correct the problem. It still runs the bad sql code

I still get this problem.
Upgraded to the newest views, cck, date.module and even contemplate (just in case) :)

I've figured out how to fix it (maybe). I'm hoping one of you can shed more light on this.

I too am running mysql 3.23

OLD CODE (dumps the aforementioned error):

SELECT arg.*, view.name 
FROM {view_argument} arg 
JOIN {view_view} view ON arg.vid=view.vid 
WHERE arg.type IN ($argument_list) AND view.page_type='date_views_browser'

NEW CODE (doesn't dump the error, appears to work, emphasized diff)

SELECT arg.*, view.name 
FROM {view_argument} arg 
<strong>INNER</strong> JOIN {view_view} view ON arg.vid=view.vid 
WHERE arg.type IN ($argument_list) AND view.page_type='date_views_browser'

Not being a SQL expert, I'm unclear if this is going to alter the behavior of my views, but I think it should be ok.

oops, forgot to change status.

I too am getting this in 5.x RC1, I have the latest version of Views and the latest version of Date. I am on MySQL v3.23.56

@KarenS
I forgot to point out that i DID check the View Type on the tracker view, and it was set to "Table View".

I don't have a MYSQL 3 installation to test on, but if I get some confirmation that changing JOIN to INNER JOIN fixes the problem, I'll commit the change.

I can confirm that this works for me.

Fix committed to all branches. Thanks everyone!