Text (CONTAINS) Query When Query Term >61 Characters Returns Unexpected Results (Doc ID 1278810.1)

Last updated on JANUARY 05, 2011

Applies to:

Oracle Text - Version: 10.2.0.2 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

In 10.2.0, Text queries with long query terms (>61 characters) return unexpected results.

Given this setup in SQL*Plus:

SQL> drop user ctxtest cascade;
drop user ctxtest cascade
*
ERROR at line 1:
ORA-01918: user 'CTXTEST' does not exist


SQL> grant connect, resource, ctxapp to ctxtest identified by ctxtest;

Grant succeeded.

SQL> connect ctxtest/ctxtest
Connected.
SQL> create table test (id number primary key, text varchar2(100));

Table created.

SQL> insert into test values
  2    (1,
  3     'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklm');

1 row created.

SQL> commit;

Commit complete.

SQL> create index testindx on test(text)
  2    indextype is ctxsys.context;

Index created.

the following Text query results are mostly unexpected:

SQL> --  Query 1:  The following is expected to return 1 row but instead
SQL> --            returns 'no rows selected':
SQL> select id
  2    from test
  3    where contains (text, '%abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefgh%') > 0;

no rows selected

SQL>
SQL> --  Query 2:  The following is expected to return 1 row but instead
SQL> --            returns 'no rows selected':
SQL> select id
  2    from test
  3    where contains (text, '%abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghi%') > 0;

no rows selected

SQL>
SQL> --   Query 3:  The following is expected to return 1 row and it does:
SQL> select id
  2    from test
  3    where contains (text, 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghi%') > 0;

        ID
----------
         1

SQL>
SQL> --  Query 4:  The following is expected to 1 row and it does:
SQL> select id
  2    from test
  3    where contains (text, 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghij%') > 0;

        ID
----------
         1

SQL>
SQL> --  Query 5:  The following is expected to return 'no rows selected' but
SQL> --            instead returns 1 row:
SQL> select id
  2    from test
  3    where contains (text, 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklz%') > 0;

        ID
----------
         1

These queries return expected results in 11.1.0.7.0 and 11.2.0.1.0.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms