{"action":"opened","issue":{"url":"https://api.github.com/repos/geopython/pycsw/issues/489","repository_url":"https://api.github.com/repos/geopython/pycsw","labels_url":"https://api.github.com/repos/geopython/pycsw/issues/489/labels{/name}","comments_url":"https://api.github.com/repos/geopython/pycsw/issues/489/comments","events_url":"https://api.github.com/repos/geopython/pycsw/issues/489/events","html_url":"https://github.com/geopython/pycsw/issues/489","id":194452992,"number":489,"title":"FTS on csw:AnyText is not reliable when using wildcards","user":{"login":"ricardogsilva","id":732010,"avatar_url":"https://avatars.githubusercontent.com/u/732010?v=3","gravatar_id":"","url":"https://api.github.com/users/ricardogsilva","html_url":"https://github.com/ricardogsilva","followers_url":"https://api.github.com/users/ricardogsilva/followers","following_url":"https://api.github.com/users/ricardogsilva/following{/other_user}","gists_url":"https://api.github.com/users/ricardogsilva/gists{/gist_id}","starred_url":"https://api.github.com/users/ricardogsilva/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ricardogsilva/subscriptions","organizations_url":"https://api.github.com/users/ricardogsilva/orgs","repos_url":"https://api.github.com/users/ricardogsilva/repos","events_url":"https://api.github.com/users/ricardogsilva/events{/privacy}","received_events_url":"https://api.github.com/users/ricardogsilva/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2016-12-08T21:45:58Z","updated_at":"2016-12-08T21:45:58Z","closed_at":null,"body":"# Description\r\n\r\nWhen querying a `postgresql` repository with expressions that take the form `csw:AnyText like %something%`, postgresql's Full Text Search (FTS) should not be used because it leads to erroneous results. This happens because the FTS mechanism does not know how to interpret the wildcard character `%`.\r\n\r\nHowever, not all queries lead to incorrect results. It actually depends on the content of the request. This happens because when postgresql processes an FTS query, it will apply some pre-processing to the search terms, including [stemming](https://en.wikipedia.org/wiki/Stemming) and these may significantly alter the result of the matching.\r\n\r\nExample:\r\n\r\nLets say we have the following phrase:\r\n\r\n> I am a robot learning to search text\r\n\r\nNow we want to know if the substring 'bot' is in our phrase.\r\nUsing normal SQL, we'd build a query like this:\r\n\r\n```sql\r\nSELECT 'I am a robot learning to search text' ILIKE '%bot%';\r\n```\r\n\r\nWhich yields a result like:\r\n\r\n```\r\n?column?\r\n--------------\r\nt\r\n(1 row)\r\n```\r\n\r\nThe `t` meaning `True`, as in we have found a correct match.\r\n\r\n\r\nNow if we try to use an FTS query things are very different. We'd issue a query like:\r\n\r\n```sql\r\nSELECT to_tsvector('I am a robot learning to search text') @@ to_tsquery('%bot%')\r\n```\r\n\r\nNow the result is:\r\n```\r\n?column?\r\n--------------\r\nf\r\n(1 row)\r\n```\r\n\r\nThe `f` meaning `False`, as in we did not find a match.\r\n\r\nThis happens because the FTS tools do not know how to interpret the pattern `%bot%`. No expansion is done on the token. \r\n\r\nLets see how postgresql treats our phrase:\r\n\r\n```sql\r\nSELECT to_tsvector('I am a robot learning to search text');\r\n```\r\n\r\nThe result:\r\n\r\n```\r\nto_tsvector\r\n--------------\r\n'learn':5 'robot':4 'search':7 'text':8\r\n(1 row)\r\n```\r\nThe `to_tsvector` function stripped away some common words from the phrase and also performed stemming on the word **learning**, which now became **learn**.\r\n\r\nNow lets see how postgresql handles the matching part of our query (the stuff after the `@@` operator)\r\n\r\n```sql\r\nSELECT to_tsquery('%bot%');\r\n```\r\n\r\nThe result:\r\n\r\n```\r\nto_tsquery\r\n--------------\r\n'bot'\r\n(1 row)\r\n```\r\n\r\nThe `to_tsquery` function strips away our `%` wildcards and just returns the substring. It also performs stemming, although that is not visible here because the word `bot` is already a stem.\r\n\r\nThis means that:\r\n\r\n1. An FTS query will not fulfill our hope that the database would expand `'%bot%'` into `'robot'` and find a match. I'm thinking that we should just disable FTS in `csw:AnyText` type of constraints whenever the constraint includes the wildcard character. What is your opinion?\r\n\r\n2. It also implies that some queries may also return more results than desirable, if the original expressions are stemmed into more generic strings. I haven't thought of a nice solution to this problem yet... Maybe this is not a problem at all, and perhaps the user can iteratively select more appropriate search terms. What is your opinion?\r\n\r\nIn pycsw we are using FTS when the following conditions are met:\r\n\r\n* The underlying repository is of type `postgresql`\r\n* There is a suitable index in the database to allow fast(er) FTS processing\r\n* The property requested in the constraint is `csw:AnyText`\r\n\r\n\r\n\r\n# Environment\r\n\r\n- operating system:\r\n- Python version:\r\n- pycsw version:\r\n- source/distribution\r\n - [x] git clone\r\n - [ ] DebianGIS/UbuntuGIS\r\n - [ ] PyPI\r\n - [ ] zip/tar.gz\r\n - [ ] other (please specify):\r\n- web server\r\n - [x] Apache/mod_wsgi\r\n - [ ] CGI\r\n - [ ] other (please specify): \r\n\r\n# Steps to Reproduce\r\n\r\n* Fire up a postgresql database\r\n* Load it with the test data from the `sru` suite\r\n* Perform the second test on the `requests.txt` file of the `sru` suite. The test is something like\r\n\r\n```\r\nsearch,mode=sru&version=1.1&operation=searchRetrieve&query=lor\r\n```\r\n\r\n  The test will transform the `query=lor` KVP parameter into `csw:AnyText like '%lor%'`. It will then query the database using FTS and will come up with zero results. The test will fail because it expects 5 records as a result. This test will not fail when using `sqlite` as a repository because there it will not use FTS and stick with the boring old SQL.\r\n\r\n# Additional Information\r\n\r\n* This problem was uncovered while working on [RFC8](http://pycsw.org/development/rfc/rfc-8.html)\r\n* Reproducing this problem is perhaps not so straightforward because our tests are not currently working with a postgresql repository. Running `paver test -d PostgreSQL` throws a code error.\r\n* Seeing as postgresql is a more capable RDBMS for production deployments, we should probably adapt our current CI pipeline to use it instead of sqlite. I am paving the way for this with the work from RFC8, which will allow selecting between `sqlite` and `postgresql` when running tests. After this we could explore ways to use postgresql in Travis instead of sqlite.\r\n"}}
{"actor":{"display_login":"ricardogsilva"}}