searches with "or" generate a bad query using views 1.6
| Project: | Views Fast Search |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
For example, when i search for "beer or wine", drupal throws this error:
* warning: array_fill in /var/opt/drupal-tools/release/wwwherald/modules/views_fastsearch/views_fastsearch.module on line 475.
* warning: array_merge in /var/opt/drupal-tools/release/wwwherald/modules/views_fastsearch/views_fastsearch.module on line 475.
* warning: array_merge in /var/opt/drupal-tools/release/wwwherald/modules/views_fastsearch/views_fastsearch.module on line 475.
* warning: array_fill in /var/opt/drupal-tools/release/wwwherald/modules/views_fastsearch/views_fastsearch.module on line 475.
* warning: array_merge in /var/opt/drupal-tools/release/wwwherald/modules/views_fastsearch/views_fastsearch.module on line 475.
* warning: array_merge in /var/opt/drupal-tools/release/wwwherald/modules/views_fastsearch/views_fastsearch.module on line 475.
* 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 node 'type') AND (marketplace.restaurant IN ('',''))' at line 1 query: SELECT count(DISTINCT(node.nid)) FROM node node LEFT JOIN search_index search_index ON node.nid = search_index.sid LEFT JOIN content_field_premium_restaurant node_data_field_premium_restaurant ON node.vid = node_data_field_premium_restaurant.vid LEFT JOIN location location ON node.vid = location.eid LEFT JOIN content_type_marketplace node_data_field_phone_mp ON node.vid = node_data_field_phone_mp.vid LEFT JOIN content_type_restaurant node_data_field_phone_number ON node.vid = node_data_field_phone_number.vid LEFT JOIN content_type_marketplace node_data_field_expiration_mp ON node.vid = node_data_field_expiration_mp.vid LEFT JOIN content_type_restaurant node_data_field_home_page_expiration_1 ON node.vid = node_data_field_home_page_expiration_1.vid LEFT JOIN content_type_marketplace node_data_field_home_pic_mp ON node.vid = node_data_field_home_pic_mp.vid LEFT JOIN content_type_restaurant node_data_field_bsd_main_picture ON node.vid = node_data_field_bsd_main_picture.vid LEFT JOIN content_type_restaurant node_data_field_web_address ON node.vid = node_data_field_web_address.vid LEFT JOIN content_type_marketplace node_data_field_web_address_mp ON node.vid = node_data_field_web_address_mp.vid WHERE ((search_index.word='') OR (search_index.word='node') OR (search_index.word='status')) AND (=.1 node 'type') AND (marketplace.restaurant IN ('','')) in /var/opt/drupal-tools/release/wwwherald/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 '=.1 node 'type') AND (marketplace.restaurant IN ('','')) GROUP BY node.nid, node' at line 1 query: SELECT DISTINCT(node.nid), node_data_field_premium_restaurant.field_premium_restaurant_value AS node_data_field_premium_restaurant_field_premium_restaurant_value, node.title AS node_title, node.changed AS node_changed, location.street AS location_street, location.city AS location_city, location.province AS location_province, location.country AS location_country, location.postal_code AS location_postal_code, node_data_field_phone_mp.field_phone_mp_value AS node_data_field_phone_mp_field_phone_mp_value, node_data_field_phone_number.field_phone_number_value AS node_data_field_phone_number_field_phone_number_value, node_data_field_expiration_mp.field_expiration_mp_value AS node_data_field_expiration_mp_field_expiration_mp_value, node_data_field_home_page_expiration_1.field_home_page_expiration_1_value AS node_data_field_home_page_expiration_1_field_home_page_expiration_1_value, node_data_field_home_pic_mp.field_home_pic_mp_fid AS node_data_field_home_pic_mp_field_home_pic_mp_fid, node_data_field_home_pic_mp.field_home_pic_mp_title AS node_data_field_home_pic_mp_field_home_pic_mp_title, node_data_field_home_pic_mp.field_home_pic_mp_alt AS node_data_field_home_pic_mp_field_home_pic_mp_alt, node_data_field_bsd_main_picture.field_bsd_main_picture_fid AS node_data_field_bsd_main_picture_field_bsd_main_picture_fid, node_data_field_bsd_main_picture.field_bsd_main_picture_title AS node_data_field_bsd_main_picture_field_bsd_main_picture_title, node_data_field_bsd_main_picture.field_bsd_main_picture_alt AS node_data_field_bsd_main_picture_field_bsd_main_picture_alt, node_data_field_web_address.field_web_address_value AS node_data_field_web_address_field_web_address_value, node_data_field_web_address_mp.field_web_address_mp_value AS node_data_field_web_address_mp_field_web_address_mp_value FROM node node LEFT JOIN search_index search_index ON node.nid = search_index.sid LEFT JOIN content_field_premium_restaurant node_data_field_premium_restaurant ON node.vid = node_data_field_premium_restaurant.vid LEFT JOIN location location ON node.vid = location.eid LEFT JOIN content_type_marketplace node_data_field_phone_mp ON node.vid = node_data_field_phone_mp.vid LEFT JOIN content_type_restaurant node_data_field_phone_number ON node.vid = node_data_field_phone_number.vid LEFT JOIN content_type_marketplace node_data_field_expiration_mp ON node.vid = node_data_field_expiration_mp.vid LEFT JOIN content_type_restaurant node_data_field_home_page_expiration_1 ON node.vid = node_data_field_home_page_expiration_1.vid LEFT JOIN content_type_marketplace node_data_field_home_pic_mp ON node.vid = node_data_field_home_pic_mp.vid LEFT JOIN content_type_restaurant node_data_field_bsd_main_picture ON node.vid = node_data_field_bsd_main_picture.vid LEFT JOIN content_type_restaurant node_data_field_web_address ON node.vid = node_data_field_web_address.vid LEFT JOIN content_type_marketplace node_data_field_web_address_mp ON node.vid = node_data_field_web_address_mp.vid WHERE ((search_index.word='') OR (search_index.word='node') OR (search_index.word='status')) AND (=.1 node 'type') AND (marketplace.restaurant IN ('','')) GROUP BY node.nid, node_data_field_premium_restaurant_field_premium_restaurant_value, node_title ORDER BY search_index.score DESC, node_data_field_premium_restaurant_field_premium_restaurant_value DESC, node_title ASC LIMIT 0, 15 in /var/opt/drupal-tools/release/wwwherald/includes/database.mysql.inc on line 172.
Is this a bug? or am i doing something wrong?
Thanks,
Matias
#1
I don't think that it has anything to do with the "or" in the search query. Please remove the DISTINCT clause from your view. If this works, the answer is that you just can't use DISTINCT. In general, I only add this when I know it's absolutely necessary because I know it causes problems. It causes problems because of the way Drupal rewrites queries; queries with the DISTINCT filter don't get rewritten properly. IMO this is more a bug in Drupal than in VFS.
#2
Hi Doug, dear drupalers
::1:: Could you tell me what is a "DISTINCT clause" of a view? It may just be a personal language problem (I'm French) but still, I'm not sure about what you mean as I don't see any DISTINCT feature within my views.
Indeed with the default view, and therefore with the
search : fast_indexfilter set to the AND operator, I get the following ::2:: error message: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 'OR 'brunch') ORDER BY node_changed_changed DESC LIMIT 0, 5' at line 1 query: SELECT DISTINCT(node.nid), node.changed AS node_changed_changed, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid FROM node node INNER JOIN users users ON node.uid = users.uid WHERE (node.type IN ('presse')) AND (node.status = '1') AND (.tid OR 'brunch') ORDER BY node_changed_changed DESC LIMIT 0, 5 in /Volumes/CMS_HD/Web/drupal/includes/database.mysql.inc on line 172.I'm not sure whether the default settings and the error message are related. Any idea about what's wrong?
::3:: Could there be a possibility to disable the error message? In which case/under what condition?
Anyways, thank you for your great module!
#3
"... the DISTINCT clause from your view" refers to the DISTINCT Filter that you probably added to your view. In my last comment, I thought that this might be causing problems.
But looking at it a little closer, it looks like either the substitutable arguments are getting messed up. You may have found a bug. But it's hard to tell because your view has so many extra things in it. Try changing the order of the filters. Also try simplifying the view.
I also just noticed that you've filled this bug against VFS 5.x-1.x-dev. The current version is 5.x-2.x-dev. I seriously doubt that I'll be fixing 1.x bugs.
#4
Dear Doug,
You said
I'll try the 5.x-2.x-dev version but I don't feel confident as there are no public release notes or any other kind of info about it... Not even about dev. I understand that you might be busy but I doesn't help us trying it.
I have to change my views. Therefore I'll let you know further more about them. Can you already tell us whether the VFS 5.x-2.x-dev views are compatible with the 5.x-1.x-dev ones? It could help us giving a try and feedbacks!
Remark: it might be a n00b remark but still useful: I get those error messages with Garland but not with the themes/zen one (I guess the current corresponding theme is themes/zen/classic from 5.x-1.0-beta2 version).
My views settings generating this "distinct" error:
Page view :
-> with either a table view or a search result View Type.
-> with or without empty text.
-> with menu + menu provided as tab (so that I get my fastsearch custom views as tabs on my search/* pages) without default menu tab options
No bloc provided
Fields :
-> Node type : sortable + exposed
-> Image : display image
-> Node : title
-> Node : author name : sortable
-> Node : created time
-> With or without Search : score : NB: My "VFS - Node type rankings" module was disabled during all my tests. Therefore nothing showed up about the score. Or I get it wrong
No argument
Filters:
CASE 1: clone of default VFS
-> Search: Fast Index (AND)
error is
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 'OR 'essai') ORDER BY node_changed_changed DESC LIMIT 0, 5' at line 1 query: SELECT DISTINCT(node.nid), node.changed AS node_changed_changed, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid FROM node node INNER JOIN users users ON node.uid = users.uid WHERE (node.type IN ('presse')) AND (node.status = '1') AND (.tid OR 'essai') ORDER BY node_changed_changed DESC LIMIT 0, 5 in /Volumes/CMS_HD/Web/drupal/includes/database.mysql.inc on line 172.CASE 2: VFS + per node-type Exposed filters
->...
About all those different CASES: Would you prefer... me to export my 4 views in a txt document with a clear title for each?
Or to do so when I'll have those views converted to VFS version 5.x-2.x-dev?
Thank you anyways for your explanations on the DISTINCT issue, eventhough this is not completely clear yet.
Arsène
#5
See http://drupal.org/node/166390. I'll check into what's up with the d.o packaging system.
#6
I have the same issue.
My search box is set to "OR"
when i type or in the search field, i get this error.
May i add that using the "unique" option fixed this issue for me.
#7
Solved using unique option in settings.