In Oracle Database 10g and Oracle Database Express Edition 11g I have encountered some strange behavior. I have a column that is indexed as a ctxsys.context type index. When I query the table for results with a contains function, it works except when the value I'm searching for is 'Still'. Then it returns no results. When I search for the same data with a column like 'Still' I get results normally. If I search for 'Jazz' using contains, then I get results normally.
Below is sql I used for reproducing this behavior on a newly created test table.
-- Setup the table with an index and some data
create table "STILL_TEST" (
"ID" number(22,0) primary key,
"PROF_DATA_15" varchar2(255 char),
"OTHER" varchar2(255 char),
"SHORTER" varchar2(100 char)
);
insert into "STILL_TEST" values (1, 'Still', 'Still', 'Still');
insert into "STILL_TEST" values (3, 'Jazz', 'Jazz', 'Jazz');
CREATE INDEX "STILL_TEST_PROF_DATA_15" ON "STILL_TEST" ("PROF_DATA_15")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('SYNC (ON COMMIT)');
commit;
-- Now query it a bit. See how both types of queries work if the
-- parameter is 'Jazz'
select * from "STILL_TEST" where prof_data_15 like 'Jazz';
select * from "STILL_TEST" where contains(prof_data_15, 'Jazz') > 0;
select * from "STILL_TEST" where prof_data_15 like 'Still';
-- So far so good, but why doesn't this next query return any results?
select * from "STILL_TEST" where contains(prof_data_15, 'Still') > 0;