0

Why does the Oracle Text CONTAINS query operator not return any results when a wildcard is applied on a stopword?

Example:
(Those sample values assume that an english stoplist is used - that depends on the DB language. If you use a different DB language, just replace "are" and "there" with stopwords from your language.)

SQL> CREATE TABLE SEARCHTEST (VALUE VARCHAR2(200 CHAR));

Table created.

SQL> CREATE INDEX IX_SEARCHTEST ON SEARCHTEST(VALUE)
  2    INDEXTYPE IS CTXSYS.CONTEXT
  3    PARAMETERS('SYNC (ON COMMIT)');

Index created.

SQL>
SQL> INSERT INTO SEARCHTEST VALUES ('searchtest');

1 row created.

SQL> INSERT INTO SEARCHTEST VALUES ('searchtest test');

1 row created.

SQL> INSERT INTO SEARCHTEST VALUES ('searchtest are');

1 row created.

SQL> INSERT INTO SEARCHTEST VALUES ('searchtest there');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest') > 0;

VALUE
------------------------------
searchtest
searchtest test
searchtest are
searchtest there

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest test') > 0;

VALUE
------------------------------
searchtest test

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest are') > 0;

VALUE
------------------------------
searchtest
searchtest test
searchtest are
searchtest there

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest there') > 0;

VALUE
------------------------------
searchtest
searchtest test
searchtest are
searchtest there

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest%') > 0;

VALUE
------------------------------
searchtest
searchtest test
searchtest are
searchtest there

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest test%') > 0;

VALUE
------------------------------
searchtest test

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest are%') > 0;

no rows selected

SQL> SELECT VALUE FROM SEARCHTEST WHERE CONTAINS(VALUE, 'searchtest there%') > 0;

no rows selected

As you can see, the last 2 queries do not return the expected result. Why is that, and how can it be fixed?
Note: I can't just remove the wildcard from the query, since it is automatically appended to the search text that was entered by the user to enable prefix searches.

DB Version:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

1 Answer 1

1

This is by design according to Oracle:

This is a bit of subtle one. First I would recommend reading “Understanding Stopword Transformations” in the doc (https://docs.oracle.com/database/121/CCREF/astoptrn.htm#CCREF1900)

If you look at H.2 for phrase queries, a stopword is transformed into “no_token”. So “searchtest are” is the same as just “searchtest” and you get a match on all of the documents in the example.

However, as soon as you stick a wildcard at the end: ‘searchtest are%’ then ‘are%’ is no longer a stopword. We are looking for words like ‘area’ or ‘arena’ which are not stopwords. So we do an actual phrase query, looking for the word ‘searchtest’ followed by another word starting with ‘are’. Text has no way to know that searchtest was followed by the stopword ‘are’ (because that information is not stored anywhere) so we don’t get a match.

It is arguable that we should detect that the expansion matches a stopword and effectively remove it. However this could result in undesirable behavior for other searches. Do we really want a search for ‘suc%’ to fail (no hits) just because ‘suc%’ matches the stopword ‘such’ ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.