Hi.
I use search_api_db on postgresql database.
The fulltext operator "contains none of these words" is not working.
When I send more than one word the result is a PDO exception.
When I send only one word the result is all records that contains the word (I believe it should filter as blacklist, right?).
Regards,
Gilsberty
Comments
Comment #1
drunken monkeyWhich filter are you using? One for a specific field or the "Search: Fulltext search" filter? In the latter case, what is your setting for "Use as"?
Comment #2
gilsbert commentedI'm using the "Search: Fulltext search" filter.
The setting for "Use as" is "Search keys".
Comment #3
drunken monkeyOK, I was able to reproduce the problem. Thanks a lot for reporting it!
I'll see whether I can come up with a patch to fix this (and add some tests – I'm amazed we aren't catching that right now).
Comment #4
drunken monkeyWow, this sent me on quite a journey … Turns out there were related bugs in both Search API Views (#2128001: Fix logic for "contains none of these words" fulltext operator) and Search API Solr Search! Also, finding and fixing the bug in this module wasn't that easy either (it rarely is, considering the complexity of our queries). But I think I managed it – attached are a patch with some additional tests (which should fail) and one that combines these tests with the fix I implemented.
Please see whether using this patch (and the one in the Search API issue, unless you want "contains not all of these words") fixes the problem for you, too! And again, thanks for reporting!
Comment #6
gilsbert commentedHi.
Congratulation for doing such hard work so quickly.
I need to apologize because I'm lost.
Should I use the patch at issue #2128001 or the patch linked here?
Should I use both?
All patches here were reported with failures by the system. Should I ignore that information?
Thanks a lot.
Regards,
Gilsberty
Comment #7
drunken monkeyI have no idea why the patches fail to apply. I'm just re-attaching them now, let's see whether that helps.
For testing it would be great if you could do the following: First just apply the "combined" patch in this issue and see whether the filter is now working correctly (excluding for one word, not throwing an exception for multiple words) – but bear in mind that it is at that point a "contains not all of these words" filter, so you'll also see results that only have one of two entered keywords. (Optionally, also run the "Search API Database Search" tests to see if they now/still pass.)
Then, go to the Search API issue, apply the patch from there and see whether that correctly makes the filter exclude items that have any of the words when entering multiple search terms (instead of only excluding those that have all of them).
Then report back on your findings in both issues (or just in this one, I'll read it anyways ;)).
Comment #9
gilsbert commentedHi.
Thank you very much for guiding me for the tests.
Details below.
a) 2127001-7--negated_fulltext_keys--combined.patch
patch's log
========
>> patching file search_api_db.test
>> Hunk #1 succeeded at 339 with fuzz 1 (offset -165 lines).
>> patching file service.inc
After applying this patch we got two things fixed:
1) the pdo exception when using more than one word and
2) the correct behavior for the filter when using only one word.
b) 2128001-1--negated_fulltext_keys_operator.patch
patch's log
========
>> patching file contrib/search_api_views/includes/handler_filter_fulltext.inc
After applying this patch the behavior for more than one word is fixed too.
I believe both patches are working perfectly (all tests I made got 100% of success).
What can I do to help you further to commit those patches?
Regards,
Gilsberty
Comment #10
drunken monkeyThanks a lot for testing, Gilsberty, and providing such great feedback! Good to hear the patches worked well for you.
There's really nothing more you can or need to do here – I'll just wait a few more days (as is my custom, so other people can chime in if they want) and then commit both patches.
(The testbot fails due to its idiotic behavior of using the version supplied in the issue – changing that to the dev version will hopefully fix this.)
Comment #11
drunken monkey7: 2127001-7--negated_fulltext_keys--tests_only.patch queued for re-testing.
Comment #12
drunken monkey7: 2127001-7--negated_fulltext_keys--combined.patch queued for re-testing.
Comment #14
gilsbert commentedThe "combined patch" #7 passed the test now!
Perhaps you can just cancel the other?
Cheers!
Comment #15
drunken monkeyNo need, we just ignore that one. It's a bit annoying the test bot can't figure this out by itself, but still.
As said, if no-one else objects, I'll commit this in the next days. Thanks again!
Comment #16
drunken monkeyCommitted.
Thanks a lot for testing so thoroughly! This is really a great help for me.
Comment #17
gilsbert commentedHi.
I tested using last dev version of search_api and search_api_db.
There is a "new" or "not tested before" bug when using an user different of the superuser (user "1"). I can't remember if I tested before using a different user than the superuser. I'm sorry.
The issue can be reproduced by an anonymous attempt as well.
Unfortunally I get few information and they are all pasted below.
SQLSTATE[22P02]: Invalid text representation: 7 ERRO: sintaxe de entrada é inválida para integer: "brasil" LINE 40: WHERE (word = 'brasil') ) t)) AND( (t.status = 'brasil') AN... ^
It might be related with postgresql only, but I can't be sure because I dont have mysql to test.
May we fix this please?
Regards,
Gilsberty
Comment #18
gilsbert commentedBy the way: using the superuser everything is working fine.
Comment #19
drunken monkeyOf course this should be fixed, but this seems to be an entirely different problem, so please create a new issue for it.
If you can find the complete query, that would be helpful. The problem seems to be the "Node access" data alteration (which of course doesn't fire for admins), or rather the DB backend not creating the right query for it.
It would be great if you could test this with both the 1.0 and the dev version of this module, to see whether the patch in this issue really introduced the problem.
Comment #20
gilsbert commentedHi.
The bug #17 is really a new one created by the patch #7 (2127001-7--negated_fulltext_keys--combined.patch).
I'm sure about it because:
1) I can reproduce the issue using the last dev where the patch #7 is commited
2) I can not reproduce the issue using the version 1.0
3) I can reproduce the issue using the version 1.0 after applying the patch #7
Perhaps it is more interesting/viable to keep this issue alive instead to open a new one. Of course I trust on you and I will follow what you judge is better.
I could not find the entire query but I believe the field status is a numeric one and can't be compared with string. Check this part of the query: (t.status = 'brasil'): the string 'brasil' was the term I used to test the operator.
It might be possible that you didn't see it before because you tested on mysql and I use over postgresql.
Regards,
Gilsberty
Comment #21
gilsbert commentedHunnn... looking from a diffrent point of view it is possible that the bug #17 was hidden before the patch #7... just like the bug #2128001 at search_api.
Please, let me know if you prefer a new issue or if we will continue here.
Comment #22
gilsbert commentedDrunken,
I'm sorry to bother you.
I'm changing the status once more just to know if you prefer a new ticket (or not).
A final update: the issue reported at #17 is happening in the last dev version (november 19th 2013).
Comment #23
drunken monkeyIt wouldn't have been necessary to re-open, as long as it's not closed I'll still see it. Just didn't have the time to check my issues in the last days.
Anyways, please create a new issue, and describe your exact steps. I'm pretty sure this is an old issue that was just hidden before.
Also, I could not reproduce this with MySQL, so maybe that's even a bug in the Postgres database layer. For MySQL, when searching with node access and "contains none of these words: macto", the following query is created:
As you see, the status is correctly filtered for "1", not for the fulltext keyword. It might be that the Postgres database integration misplaces an argument there. Or I'm using it wrong and the MySQL layer is just constructed in a way which hides this problem.
In any case, I have seen where the problem is, but the complete query would still be useful. But I admit it's not easy to get to that, especially if you're no developer.
If you have Devel installed, put
dpq($db_query);below the$time_processing_done = microtime(TRUE);line inservice.inc, give a non-admin role (so node access will fire) the "Access developer information" permission and then trigger the bug with a user of that role.Comment #24
gilsbert commentedHi.
Thank you for guide me on how to debug this issue.
I created a new ticket: #2142107.
Regards,
Gilsberty