error after subquery patch

bennos,

I'm having similar errors on my Drupal site and found your post. I did what you mentioned to php.ini to reflect:

mysql.default_port = 3306

(Default was blank) After restarting apache, though, I still have the same errors. Where did you pick up the hint on allowing port 3306? I'm curious if there may be further configurations needed.

On a side note, I've noticed that if I allow the user "node administration" permission, these errors go away - but this is highly undesirable for anonymous users...obviously.

Similar Problem here...
user warning: Table 'temp_vfs' already exists query:
I added the default port as suggested, but the error persists.
What's strange is that this occurs on one particular view using vfs as a filter.

[Edit: Add entire error]
user warning: Table 'temp_vfs' already exists query: CREATE TEMPORARY TABLE temp_vfs SELECT n.nid, SUM(1.6666666666667 * COALESCE((i.score * t.count), 0) + 1.6666666666667 * COALESCE((POW(2, GREATEST(n.created, n.changed) - 1209430831) * 6.43e-8), 0) + 1.6666666666667 * COALESCE((2.0 - 2.0 / (1.0 + c.comment_count * 0.076923076923077)), 0)) AS score FROM node n LEFT JOIN search_index i ON n.nid=i.sid LEFT JOIN search_total t ON i.word=t.word LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE i.fromsid=0 AND i.word IN ('asdf') GROUP BY n.nid HAVING COUNT(*)=1 in /var/www/html/includes/database.mysql.inc on line 172.

removed by poster

I get the same error (I believe):

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 '.1.66666666667 IN ('1217423949')) AND (term_node.tid = '62') AND (term_node2.tid' at line 1 query: SELECT count(node.nid) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_hierarchy term_hierarchy ON term_node.tid = term_hierarchy.tid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_hierarchy term_hierarchy2 ON term_node2.tid = term_hierarchy2.tid LEFT JOIN content_type_firma node_data_field_trade ON node.vid = node_data_field_trade.vid LEFT JOIN content_type_firma node_data_field_province ON node.vid = node_data_field_province.vid INNER JOIN (SELECT n.nid, SUM(1.66666666667 * COALESCE((i.score * t.count), 0) + 1.66666666667 * COALESCE((POW(2, GREATEST(n.created, n.changed) - 1217423949) * 6.43e-8), 0) + 0.666666666667 * COALESCE((2.0 - 2.0 / (1.0 + c.comment_count * 0.333333333333)), 0)) AS score FROM node n LEFT JOIN search_index i ON n.nid=i.sid LEFT JOIN search_total t ON i.word=t.word LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE i.fromsid=0 AND i.word IN ('apart') GROUP BY n.nid HAVING COUNT(*)=1) temp_vfs ON node.nid = temp_vfs.nid WHERE (1.66666666667.1.66666666667 IN ('1217423949')) AND (term_node.tid = '62') AND (term_node2.tid = '71') in /www/public_www/ts/includes/database.mysql.inc on line 172.

I have a view with 3 exposed filters: search phrase and 2 taxonomy drop-downs. As shown on a figure 10 on the following page: http://www.lullabot.com/articles/custom_search_forms_views_and_fastsearch

Hello there, I think you are here facing different problems.

@ Bartekg, I have a common problem with you, which appears as soon as I've patched with the supposed-to-be-necessary patch #143888: support subqueries, whenever I add any other filter to the VFS "Search: Fast Index" one.
Please try to disable all the other-than-the-VFS-"Search:-Fast-Index" filters and make a search to see if the error still appears. Trying to reproduce the error this way would help confirming the "filter" case.

I've kind of hijacked the subquery feature request issue with my problem... I'll post back from there to here and link here after some clean-up.

Here are the details I had hijacked the support subquery topic with (my apologies):

With:
- Drupal 5.11
- Views 1.6 patched with:

#218187: Views cache too large
#143888: support subqueries #37

- Apache 2.2.8
- MacOS X Darwin
- PHP 5.2.6 - Memory limit 512M
- MySQ 5.0.45

Previously to applying the above-mentionned patch #37, VFS 2.0 seemed to work with the SQL query done and the single patch 146466 applied.

But, as soon as I implemented the #37 subquery patch and re-index my site, the VFS results carried errors for any other filter added to the VFS "Search: Fast Index".

For example:
While it is obvious that some roles shouldn't access unpublished nodes from the search results... (even just thier titles), the "node published" filter returns:

• 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 '.4 angelin 'node')' at line 1 query: SELECT count( DISTINCT(node.nid)) FROM node node INNER JOIN (SELECT n.nid, SUM(4.5 * COALESCE((i.score * nt.search_weight), 0) + 4 * COALESCE((i.score * t.count), 0)) AS score FROM node n LEFT JOIN search_index i ON n.nid=i.sid LEFT JOIN vfs_ranking_nodetype nt ON nt.type = n.type LEFT JOIN search_total t ON i.word=t.word WHERE i.word IN ('angelin') GROUP BY n.nid HAVING COUNT(*)=1) temp_vfs ON node.nid = temp_vfs.nid WHERE (4.5.4 angelin 'node') in /Shared Items/iDoc/drupal/includes/database.mysql.inc on line 172.
• 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 '.4 angelin 'node') ORDER BY score DESC LIMIT 0, 500' at line 1 query: SELECT DISTINCT(node.nid), temp_vfs.score, temp_vfs.score AS score, node.title AS node_title, node.changed AS node_changed FROM node node INNER JOIN (SELECT n.nid, SUM(4.5 * COALESCE((i.score * nt.search_weight), 0) + 4 * COALESCE((i.score * t.count), 0)) AS score FROM node n LEFT JOIN search_index i ON n.nid=i.sid LEFT JOIN vfs_ranking_nodetype nt ON nt.type = n.type LEFT JOIN search_total t ON i.word=t.word WHERE i.word IN ('angelin') GROUP BY n.nid HAVING COUNT(*)=1) temp_vfs ON node.nid = temp_vfs.nid WHERE (4.5.4 angelin 'node') ORDER BY score DESC LIMIT 0, 500 in /Shared Items/iDoc/drupal/includes/database.mysql.inc on line 172.

(Same results for 5 or 500 nodes on a page.)

Here is is an export of the view generating the error above:

  $view = new stdClass();
  $view->name = 'ch_vfs_table';
  $view->description = 'VFS Chercher Tablo';
  $view->access = array (
  0 => '11',
  1 => '4',
  2 => '10',
  3 => '13',
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Chercher TEST - Résultats en tablo';
  $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 = 'table';
  $view->url = 'chercher/fast/table';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '500';
  $view->menu = TRUE;
  $view->menu_title = 'Tablo';
  $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 (
    array (
      'tablename' => 'search_index',
      'field' => 'score',
      'sortorder' => 'DESC',
      'options' => '',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'search_index',
      'field' => 'score',
      'label' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'search_index',
      'field' => 'word',
      'operator' => '=',
      'options' => '',
      'value' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
  );
  $view->exposed_filter = array (
    array (
      'tablename' => 'search_index',
      'field' => 'word',
      'label' => 'Mot(s) clé(s)',
      'optional' => '0',
      'is_default' => '0',
      'operator' => '1',
      'single' => '0',
    ),
  );
  $view->requires = array(search_index, node);
  $views[$view->name] = $view;

BTW, according to the various error reported on my site (all on the same scheme) this issue may conflict with access modules (Workflow, Nodeaccess) or module with views hooks (Flags), maybe similarly to what people experienced in the current topic.

I say in touch.

Manually added line: $info['subquery'] = str_replace($src, $dest, $info['subquery']); from patch #49

(as it seems that this line is the only difference between this new #49 patch and the previous #37 patch of the comment above).

I cleared drupal servers' and browser's cache, cleared views'cache, ran update.php.

Same VFS View, same error.

I don't think that I'd need to re-index my whole site, or shall I?

The "JOIN" error appears only if I had another filter.
But I use this patch: #218187: Views cache too large
VFS may be incompatible with it...

edit duplicate

I'm seeing the same error as doc2@drupalfr.org.

I can confirm that the JOIN error appears if there's more than one filter. In my case, I'm filtering against a CCK node type (one that includes several custom fields) and Node:Published, but it happens if I only use a Node:Published filter.

It's not just CCK node types though, as I can also generate the error by filtering based on vanilla node types (like "story") and Node: Published. Here's an example of the problem query:

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 '.1.25 IN ('1.25')) AND (1224011072.1.25 1 'god')' at line 1 query: 

SELECT count( DISTINCT(node.nid)) FROM node node INNER JOIN (SELECT n.nid, SUM(1.25 * COALESCE((i.score * nt.search_weight), 0) + 1.25 * COALESCE((i.score * t.count), 0) + 1.25 * COALESCE((POW(2, GREATEST(n.created, n.changed) - 1224011072) * 6.43e-8), 0) + 1.25 * COALESCE((2.0 - 2.0 / (1.0 + c.comment_count * 1)), 0)) AS score FROM node n LEFT JOIN search_index i ON n.nid=i.sid LEFT JOIN vfs_ranking_nodetype nt ON nt.type = n.type LEFT JOIN search_total t ON i.word=t.word LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE i.word IN ('god') GROUP BY n.nid HAVING COUNT(*)=1) temp_vfs ON node.nid = temp_vfs.nid WHERE (1.25.1.25 IN ('1.25')) AND (1224011072.1.25 1 'god')

Here's the second error:

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 '.1.25 IN ('1.25')) AND (1224011072.1.25 1 'god') LIMIT 0, 10' at line 1 query: 

SELECT DISTINCT(node.nid), temp_vfs.score, node.title AS node_title, node.changed AS node_changed FROM node node INNER JOIN (SELECT n.nid, SUM(1.25 * COALESCE((i.score * nt.search_weight), 0) + 1.25 * COALESCE((i.score * t.count), 0) + 1.25 * COALESCE((POW(2, GREATEST(n.created, n.changed) - 1224011072) * 6.43e-8), 0) + 1.25 * COALESCE((2.0 - 2.0 / (1.0 + c.comment_count * 1)), 0)) AS score FROM node n LEFT JOIN search_index i ON n.nid=i.sid LEFT JOIN vfs_ranking_nodetype nt ON nt.type = n.type LEFT JOIN search_total t ON i.word=t.word LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE i.word IN ('god') GROUP BY n.nid HAVING COUNT(*)=1) temp_vfs ON node.nid = temp_vfs.nid WHERE (1.25.1.25 IN ('1.25')) AND (1224011072.1.25 1 'god') LIMIT 0, 10

I'm running:

Apache 2.x
PHP 5.1.2
MySQL 5.0.18.
RHEL 4

On the Drupal side, I'm running Views 5.x-1.6 and 5.x-2.0 (with the patch applied).

Digging a bit deeper, I found that if I go to Administration > Site Configuration > Search Settings and choose to use the default Drupal Drupal Installation, the error goes away.

If I use the "No Duplicates" option (having created the index using "ALTER IGNORE TABLE search_index ADD UNIQUE INDEX (sid, word, type, fromsid)") and rebuilt the index, then I get the error. However, when the index rebuilds, Drupal does throw a number of errors about non-unique

user warning: Duplicate entry '988-editions-node-0' for key 1 query: INSERT INTO search_index (word, sid, type, score) VALUES ('editions', 988, 'node', 1.2084584661205)

So I'm guessing this is an issue with my index, and I'll focus my attentions there.

I'm getting the same type of query error. Here is my (formatted) query

SELECT count( DISTINCT(node.nid))
FROM node node
        LEFT JOIN term_node term_node
                ON node.nid = term_node.nid
        LEFT JOIN term_hierarchy term_hierarchy
                ON term_node.tid = term_hierarchy.tid
        LEFT JOIN content_field_short_desc_0 node_data_field_short_desc_0
                ON node.vid = node_data_field_short_desc_0.vid
        LEFT JOIN content_type_listing6 node_data_field_file_date
                ON node.vid = node_data_field_file_date.vid
        LEFT JOIN node_comment_statistics node_comment_statistics
                ON node.nid = node_comment_statistics.nid
        LEFT JOIN content_field_view_count node_data_field_view_count
                ON node.vid = node_data_field_view_count.vid
        INNER JOIN (
                    SELECT
                        n.nid,
                        SUM(5 * COALESCE((i.score * t.count), 0) + 2.5 * COALESCE((POW(2, GREATEST(n.created, n.changed) - 1247625445) * 6.43e-8), 0)) AS score 
                    FROM node n
                        LEFT JOIN search_index i
                            ON n.nid=i.sid
                        LEFT JOIN search_total t
                            ON i.word=t.word
                    WHERE i.fromsid=0
                        AND i.word IN ('monster')
                    GROUP BY n.nid HAVING COUNT(*)=1) temp_vfs
                ON node.nid = temp_vfs.nid
WHERE (5.2.5 1247625445 'monster')
    AND (node.status IN ('='))
    AND (term_node.tid = '60');

You can see clearly in the 2nd and 3rd last lines where things are going wrong.

(5.2.5 1247625445 'monster')
AND (node.status IN ('='))

The terms seem to be wrong or mixed up.

- '5.2.5' look like php version but I'm running '5.2.4'. clearly a column name would fit better there.
- 1247625445 is a timestamp in the place where an operator should be.
- '=' is unlikely to be a valid node status and was probably originally intended as an operator.
- Perhaps the 'IN' is also incorrect.

Other queries in this thread seem to have similar problems with conditions like

(1.66666666667.1.66666666667 IN ('1217423949'))
(4.5.4 angelin 'node')
(1.25.1.25 IN ('1.25')) AND (1224011072.1.25 1 'god')

Some issues reported in the thread don't have this problem and so they could be unrelated.

It looks like somewhere in the code, elements of an array are not in the places they are expected to be. Whether the error is in this views subquery patch #143888 (I'm using the one from comment 49) or in views_fastsearch 2.0 is uncertain. Whatever the case, it is unlikely to have anything to do with the index since the code that makes the query is unlikely to depend on the contents of the search_index table.

This version of Views Fast Search is not supported anymore. The issue is closed for this reason.
Please upgrade to a supported version and feel free to reopen the issue on the new version if applicable.

This issue has been automagically closed by a script.