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

drunken monkey’s picture

Which 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"?

gilsbert’s picture

I'm using the "Search: Fulltext search" filter.
The setting for "Use as" is "Search keys".

drunken monkey’s picture

OK, 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).

drunken monkey’s picture

Wow, 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!

Status: Needs review » Needs work

The last submitted patch, 4: 2127001-4--negated_fulltext_keys--combined.patch, failed testing.

gilsbert’s picture

Hi.

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

drunken monkey’s picture

Status: Needs work » Needs review
StatusFileSize
new2.43 KB
new6.07 KB

I 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 ;)).

Status: Needs review » Needs work

The last submitted patch, 7: 2127001-7--negated_fulltext_keys--combined.patch, failed testing.

gilsbert’s picture

Hi.

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

drunken monkey’s picture

Version: 7.x-1.0 » 7.x-1.x-dev
Status: Needs work » Needs review

Thanks 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.)

drunken monkey’s picture

drunken monkey’s picture

Status: Needs review » Needs work

The last submitted patch, 7: 2127001-7--negated_fulltext_keys--tests_only.patch, failed testing.

gilsbert’s picture

The "combined patch" #7 passed the test now!
Perhaps you can just cancel the other?
Cheers!

drunken monkey’s picture

Status: Needs work » Reviewed & tested by the community

No 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!

drunken monkey’s picture

Status: Reviewed & tested by the community » Fixed

Committed.
Thanks a lot for testing so thoroughly! This is really a great help for me.

gilsbert’s picture

Hi.

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

gilsbert’s picture

Status: Fixed » Needs work

By the way: using the superuser everything is working fine.

drunken monkey’s picture

Status: Needs work » Fixed

Of 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.

gilsbert’s picture

Hi.

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

gilsbert’s picture

Hunnn... 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.

gilsbert’s picture

Status: Fixed » Needs work

Drunken,
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).

drunken monkey’s picture

Status: Needs work » Fixed

It 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:

SELECT DISTINCT t.item_id AS item_id, '1' AS score
FROM 
{search_api_db_default_node_index} t
LEFT OUTER JOIN {search_api_db_default_node_index_search_api_access_node} t_2 ON t.item_id = t_2.item_id
WHERE  (t.item_id NOT IN  (SELECT t.item_id AS item_id
FROM 
{search_api_db_default_node_index_search_api_viewed} t
WHERE  (word = 'macto') )) AND(( (t.status = '1') OR (t.author = '12') )AND( (t_2.value = 'node_access_all:0') OR (t_2.value = 'node_access__all') ))

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 in service.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.

gilsbert’s picture

Hi.
Thank you for guide me on how to debug this issue.
I created a new ticket: #2142107.
Regards,
Gilsberty

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.