Hi,

I am using oracle 12.1, Catsearch query "a but not b" working with catsearch(column, 'a - b', null)>0. But I am unable to only query "not b".

Here is a test:

create table test_catsearch(name varchar2(100)); 
insert into  test_catsearch(name) values('Lisa Smith');
insert into  test_catsearch(name) values('Bart Simpson');
insert into  test_catsearch(name) values('Lisa Simpson');
create index cat_test on TEST_CATSEARCH(name) indextype is CTXSYS.CTXCAT;

"simpson not bart" returning 'Lisa Simpson' works fine:

SELECT name FROM TEST_CATSEARCH t WHERE CATSEARCH(name, 'simpson - bart', null)> 0 ;

Error DRG-50901 when using '-' for "not bart"

SELECT name FROM TEST_CATSEARCH t WHERE CATSEARCH(name, ' - bart', null)> 0 ;

Changing condition doesn't make a difference:

SELECT name FROM TEST_CATSEARCH t WHERE CATSEARCH(name, 'bart', null)<=0 ; -- changing condition on score not working, same result as first query
SELECT name, score(1) FROM TEST_CATSEARCH t WHERE CATSEARCH(name, 'bart', null)<=0 ; --ORA-29908: missing primary invocation for ancillary operator

Neither does using logical "NOT"

SELECT name FROM TEST_CATSEARCH t WHERE NOT(CATSEARCH(name, 'bart', null)>0);--logical not on condition doesn't have impact on result neither

My question is:
Is there an alternative to query only "not b" with catsearch()?
Catsearch() returns number, but seems not the same way as score in context query. Does it mean anything else the >0 at the end of condition?

SELECT name FROM TEST_CATSEARCH
minus
SELECT name FROM TEST_CATSEARCH t WHERE CATSEARCH(name, 'bart', null)> 0 ;

NAME          
Lisa Simpson  
Lisa Smith